Multi queries (Access 97)

Topic

New Reply

I am just far enough along to be scary – no one here can help anymore. I hope this isn’t trivial (or perhaps I hope it IS), but here’s the problem: I have a database that downloads project info from three separate software programs via ODBC. Eureka, it actually works! I have a main query/report that includes boilerplate data about the project, a subreport with approval/coordination info, and a second subreport with current progress. The queries each use the same specific project number as a filtering criteria. To run this beast, I need to enter the same project number 3 times. Is there any way to enter the project number ONCE, in a form or through VBA, so that the query criteria can use it for all 3 queries and reports? I have tried linking queries, but then I get multiple “enter parameter” messages, which is worse. I can supply more info, but probably cannot send a sample because of the complexity.

Thanks in advance for ideas.

— Mike in Tennessee

Reply | Quote

Replies

Hi Mike,
You can store the criteria in a table. I often use a table with only one field (and only one record) for storing criteria that the user has entered. You can then use DLookup() to get the value from the table. I usually use an unbound form and use DAO (or sometimes ADO) to update the value in the table.

This has worked very well for me – I must give credit to Lounger David Rasley for the tip.

HTH

Reply | Quote

Thanks for the response! I figured some type of table or form would be the ticket, but didn’t know how to go about it. I’m reading the manual now and trying to figure out the DLookup and DAO – I will let you know how it goes!

Reply | Quote

Let me know if you need an example of my suggestion.

Reply | Quote

Yep, I’m beyond fear of embarrassment!!! If you have an example, it would save me HOURS of exciting reading!! I will be forever grateful.

Reply | Quote

Here ya go! The criteria is stored in the first row of the table “tblStoredCriteria”. The query (“qryData”) uses the criteria entered in the table (“tblStoredCriteria”) for the ProjectCategory field.

The form (“Example”) initially uses Dlooukup to get the value from the table (“tblStoredCriteria”) but then uses DAO code to change the stored value once it’s modified.

HTH

Reply | Quote

Eureka! I’ve got it working! THANKS!!

Reply | Quote

the way i do it is to have an opening form where the project number is keyed in then set the query criteria to this form

Reply | Quote

Thanks for the response to my question – I will give this a try!

Reply | Quote