Jul 28 2020 07:51 AM
Jul 28 2020 07:51 AM
I have built a form off of multiple tables linked together in query builder.
How can I run a query with unique criteria each time that pulls up the results in form view (so that I can navigate between the resulting records)?
Jul 28 2020 11:38 AM
First, we ought to try to get on the same page with regard to terminology so you have a better idea of suggestions that might be offered.
We use the term "join", rather than "link" to refer to the process of establishing a relationship between two tables. In its most basic implementation, that is the Primary Key field in a table joined to the Foreign Key field in a related table. We do that to ensure referential integrity between the records.
If your query includes multiple tables, it is likely that the joins will be on the various Primary and Foreign Key fields in the related tables.
So, the next step.
If you DO include multiple tables in a query, it is much LESS likely that the resulting recordset will be updateable. If your intention is only to REVIEW and not to update records, this multi-table query will be okay. However, if you need to modify selected records, you should NOT bind a form to a multi-table query. Rather you should bind a main form to a single table or a query based on that table. Then use subforms for the related records in the related tables.
Finally, the design I would use is illustrated in a sample relational database application on my website,
or in this one. Rather than go into detail here, I suggest you study those sample applications and see how they illustrate some methods you can use.
Jul 28 2020 01:43 PM
@George Hepworth Thanks for those clarifications! Ok, so I created a query that pulls in all of the relevant details from the tables. Now I built the Form based on off that single query (so that it is updateable).
Now using this query, how can I run it with specific criteria each time so that the results pull up as Forms that I can navigate through as unique records? When I run the query now it just pulls up the datasheet.
Jul 28 2020 02:21 PM
@michellebevan Did you check out either of the sample relational database applications to which I provided links? They both illustrate different ways to accomplish what you describe you want to do.