Solved Best practice: filter on query or on form? (1 Viewer)

Charlie8585

New member
Local time
Today, 06:10
Joined
Apr 9, 2022
Messages
8
Simple question. I have a query that calculates nutritional values of smoothies based on their ingredients. Lots of calculated queries using (sadly unavoidable) queries on queries.
This query is only used in a single form (as form's record source). Is it best practice to:
A - leave query itself as is and use form to filter record you want. (So if you fire up query on its own you'd see all records like this: )
WinSht_12-05-2022_at_10-56-06_Access_-_JustBlitzData41__Database-_FGoogle_Dri-MSACCESS_no130.png

or
B - put make query form-specific, i.e. put [Forms]![frmSmoothies]![txtSmoothieID] as a criteria in ID field in query Design View? (So that loading it (with form loaded in background) gives you this:
WinSht_12-05-2022_at_10-57-43_Access_-_JustBlitzData41__Database-_FGoogle_Dri-MSACCESS_no131.png

Reason for question: I've simplified examples for clarity. In reality dataset will get bigger with many more calculated fields so I was wondering if B would make database/form loading a little faster? Or does it make no difference?
 

Ranman256

Well-known member
Local time
Today, 01:10
Joined
Apr 9, 2015
Messages
4,121
Make a form that shows all records,
user can filter ,
add button to open that selected record via the key to view the 1 record...

docmd.openform “fDetail”,,”[id]=“. & me.txtID
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:10
Joined
Feb 19, 2002
Messages
36,908
The answer really depends on if your BE is SQL Server or if it contains hundreds of thousands/millions of rows. If the BE is SQL Server or just large, you never want to bind a form to a table or to a query without criteria. You want a form to return the minimum number of rows possible.

In your case, I'm guessing this will stay as Jet/ACE and the row count will be hundreds or perhaps thousands. Given that, I would use all the built-in features Access provides for us. So, I would use a subform in DataSheet view and let the user filter using the filters exposed by DS view. Unless your users can't be taught how to use the filters, I would not build anything for searching. I would just let Access provide the tools.
 

Charlie8585

New member
Local time
Today, 06:10
Joined
Apr 9, 2022
Messages
8
The answer really depends on if your BE is SQL Server or if it contains hundreds of thousands/millions of rows. If the BE is SQL Server or just large, you never want to bind a form to a table or to a query without criteria. You want a form to return the minimum number of rows possible.

In your case, I'm guessing this will stay as Jet/ACE and the row count will be hundreds or perhaps thousands. Given that, I would use all the built-in features Access provides for us. So, I would use a subform in DataSheet view and let the user filter using the filters exposed by DS view. Unless your users can't be taught how to use the filters, I would not build anything for searching. I would just let Access provide the tools.
Noted. Indeed it's latter case. Will follow your advice.
 

Users who are viewing this thread

Top Bottom