Form query best practice

[Tskutnik]

Hello all,
Could use some best practice advice.
Is it better to write a query and then create a form that reads/executes the query (as different objects) or is is better to develop the query from with the form itself.
I see the advantage of a seperate query if it will be used by different functions, but if it is a specific form-related query it seems to be more organized to embed it with the form.
Same question goes for report/query relationships as it does for queries.
I'm not sure if I'm missing an obvious advantage to one method vs the other.
Thanks for any help.

 

[plog]

I believe a form that directly interacts with data (add/edit/delete) should be based on the table it is acting on. It just makes things clearer and simpler. No getting confused about which tables are being edited, no relationship violations no unexpected results from the limits you have placed on the data by being in a query.

 

[Uncle Gizmo]

Regarding reports I seldom use the filtering options available in reports. I create the SQL for the results I want in a form. The reports open event captures the SQL from the form it was generated in and sets the reports record source to it. See this video for more info:-

MS Access - Generating Multiple Reports (PDF Invoices)

and more resources HERE:-

https://sites.google.com/site/msacc...pies-of-report-with-different-criteria/videos

 

[Uncle Gizmo]

With regard to the record source of a form. I sometimes use an SQL statement in the form record source. I find this particularly useful if the table the form is based on contains an awful lot records. By creating an SQL statement that returns record number 0, then the form is opened completely empty, because there is never a record number 0! I'm not particularly happy in using queries as I always feel these are subject to being changed by the user.

 

[sneuberg]

It's sure handy to have the record source or row source of objects as named query object when you have problems you need to track down, but other than that I think they just create a lot of clutter.