By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,047 Members | 606 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,047 IT Pros & Developers. It's quick & easy.

Use query to populate a table

P: 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?
Sep 1 '10 #1
Share this Question
Share on Google+
1 Reply

P: 23
I resolved this issue by generalizing the SELECT statement and removing the WHERE clause.

Now qryPrintCostMaster returns all records correctly modified in a neat table.
Sep 1 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.