Results 1 to 7 of 7

What is the best way to display relevant query results in a form?

  1. #1
    BDibz is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2018
    Posts
    62

    Question What is the best way to display relevant query results in a form?

    So I've got this form used for updating and displaying information from the main table in a contextualized way. Great.



    I used to have the source table have a few calculated fields that would be displayed on this form. Not so great.

    I removed all the calculated fields and created queries that do the same aggregation and give the same results as the original calculated fields.

    Now, I need to find the best way to display that information on the form when a record is loaded. Right now I'm using a DLookup to populate a textbox with the query result matched by the Job Number (primary key). This technically works, but it just seems sloppy, and it returns the data slowly.

    I have considered, alternatively, adding an invisible subquery into the form that contains the calculated information, and using that as the control source for the text box. Is this a better or worse way to do it? Is there a good, standard way to do this?

    Click image for larger version. 

Name:	Form.PNG 
Views:	28 
Size:	18.7 KB 
ID:	34051

  2. #2
    BDibz is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2018
    Posts
    62
    I'm a dummy; this subquery method is far superior. However, it raises a better question.

    If my query depends on values entered into the data table using this form, then how do I get the query to update when form data is changed?

    Do I need to chain Requery commands through the forms->subforms? IE, do an OnChange event for the main form that says "update the subform", and an OnChange event on the subform that says "Requery this subform"?

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,176
    On Change fires each time you change control data and that means with every keypress so you don't want that. You can update a form via several methods: leave the record, close the form, code for several events (after update, exit, lost focus to name a few). Really depends on the situation but Me.Dirty = False will save changes provided there are no rule violations.
    Last edited by Micron; 05-16-2018 at 09:30 AM. Reason: Stoopud auto korekt!
    - "doesn't work" is no help. What's happening? Error messages? Where??
    - Use code tags for code/sql. Implement changes in copies of your database.
    Irregardless, ain't no such word as "reoccur".

  4. #4
    Join Date
    Apr 2017
    Posts
    657
    From picture you have in your post I can't see how your queries are matching with table records.

    When you new queries are returning a single record for every table record (or either single record or no records for every table record), then create an updatable query which combines your table and your queries, and use this updatable query as source for your form. Then you can have those additional values as part of form source, and you can have on form controls linked with those query fields. You have to disable editing for such controls, of-course.

    When your current queries are returning several records for every/some table row, then you have to use continous subform(s) in your current (main) form, linked with main form using common field(s)/ontrol(s) in both main form and subform. Whenever you activate another record in main form, records linked with main form are displayed in subform. And because values in queries subform(s) are based on are calculated, you have to disable editing of subforms.

  5. #5
    BDibz is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2018
    Posts
    62
    Quote Originally Posted by ArviLaanemets View Post
    From picture you have in your post I can't see how your queries are matching with table records.

    When you new queries are returning a single record for every table record (or either single record or no records for every table record), then create an updatable query which combines your table and your queries, and use this updatable query as source for your form. Then you can have those additional values as part of form source, and you can have on form controls linked with those query fields. You have to disable editing for such controls, of-course.

    When your current queries are returning several records for every/some table row, then you have to use continous subform(s) in your current (main) form, linked with main form using common field(s)/ontrol(s) in both main form and subform. Whenever you activate another record in main form, records linked with main form are displayed in subform. And because values in queries subform(s) are based on are calculated, you have to disable editing of subforms.
    So its possible to just make a query taking fields from that table and running calculations to output additional fields, and use this as the form source; and then updating records on that query will update the underlying records on the source table? That does sound like the most straightforward way, since it eliminates the use of various different sources. I will probably still have to use embedded queries for the monthly aggregates though.

    This pic does a better job showing the relations; I have those subforms hidden in the bottom left, they are the source for some of those control boxes (you can see the =[sbfrm...)

    Click image for larger version. 

Name:	form2.PNG 
Views:	17 
Size:	63.0 KB 
ID:	34071

  6. #6
    Join Date
    Apr 2017
    Posts
    657
    The trick is to get the query updateable.

    Read those 2 links.
    Which queries aren't updateable: http://allenbrowne.com/ser-61.html
    What to do, when the query is not updateable: http://www.fmsinc.com/microsoftacces...ble/index.html

  7. #7
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    5,803
    there is another way, but does depend on the query and form so it may not work.

    In the form properties is a property - Recordset Type. Change it to Dynaset inconsistent updates. You will also need to ensure your relationships are properly defined with referential integrity set

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 10-08-2014, 11:25 PM
  2. Replies: 3
    Last Post: 03-05-2014, 10:20 AM
  3. Display QUERY results into SUB FORM
    By taimysho0 in forum Programming
    Replies: 9
    Last Post: 11-23-2011, 11:26 AM
  4. Display Query Results on a Form
    By P5C768 in forum Queries
    Replies: 5
    Last Post: 05-04-2010, 10:04 AM
  5. display query results in a form
    By P5C768 in forum Queries
    Replies: 3
    Last Post: 08-14-2009, 02:02 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums