Results 1 to 9 of 9
  1. #1
    mike-tenn
    Guest

    Multi queries (Access 97)

    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

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi queries (Access 97)

    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. <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

    HTH

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi queries (Access 97)

    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

  4. #4
    mike-tenn
    Guest

    Re: Multi queries (Access 97)

    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!

  5. #5
    mike-tenn
    Guest

    Re: Multi queries (Access 97)

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

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi queries (Access 97)

    Let me know if you need an example of my suggestion. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  7. #7
    mike-tenn
    Guest

    Re: Multi queries (Access 97)

    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. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi queries (Access 97)

    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 <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Attached Files Attached Files

  9. #9
    mike-tenn
    Guest

    Re: Multi queries (Access 97)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •