23
I've built a series of queries that bring together and process data from several different tables and queries and at the end a single query returns a single record based on a selection made in a form.
I've built myself into a bit of a bind, however, in that the queries begin by referencing the value in a control in the form, and the form itself is based on the query. This circular setup is not working. Obviously.
In order to better accomplish my goals, I'm thinking it may be better to use the queries to populate a table with the appropriate data and then build the form from that table. This would mean less processing as I use the form, quicker db response, and much easier form construction.
The problem is that I'm not sure where to start in modifying my queries and/or what VBA it may be necessary to employ to make this work.
Currently the initial query, qryPrintCostAssets, returns a record from tblAssets based on the selection in forms!formPrinterCost!listPrinters. The final query, qryPrintCostMaster, returns all the data I want correctly and with all necessary processing completed simply runs based on qryPrintCostAssets.AssetID pulling from several other data processing queries. Running this query triggers all the other queries to process in turn.
I'd like to modify the entire string of queries to run for each record returned by qryPrinter, which is simply a list of the printers pulled from tblAssets.
I assume the only queries that may need modification would be the last, qryPrintCostMaster, and possibly the first, qryPrintCostAssets. And the modifications would probably be based on the WHERE and FROM statements.
Can a query be built that will do all this updating itself or should I employ VBA and use a FOR loop to run the queries upon command, updating the table.
Using the various Event triggers, I assume the best time to run all this is when I open formPrinterCost.
Do you have any tips or information you think would benefit me in this endeavor?
