Multiple Query Help (1 Viewer)

[msk7777]

Currently in an access database I have a query that runs off of a parameter form. The parameter form collects the [Start Date], [End Date] & [Office Name]. Once a user enters the [Start Date] & [End Date], the [Office Name] combo box auto populates with list of Office Names that were entered into the database within the dates given in the [Start Date] & [End Date] fields . The user then selects the Office Name they wish to run a batch query on. After selecting “OK” the query is then ran and exported (via VBA) to an excel workbook.
The problem is that the list populated in the [Office Name] field has grown over time as business has grown. Now the combo box can have anywhere between 50-100 office names listed. For each office that is listed a separate batch query has to be run. Currently the users are selecting the first name in the list and running that query, then open then second name and running that query, repeating until all the office names have had a query ran.
My boss has requested a new process that will basically allow the user to put the date parameters in and then run individual queries for each office name during those date parameters and export each individual query to excel workbooks.
Can anyone steer me in the direction I need to take to achieve this? Please let me know if I need to explain any additional details. Thanks in advance!

Msk7777

 

[pbaldy]

Open a recordset that gets all desired office names that meet the criteria. Within a loop of that recordset, put the office name into a textbox that the query uses as a criteria, and run the query. Rinse and repeat until done.

 

[msk7777]

Thank you so much Paul for responding so quickly. I'm sure you are extremely busy so I hate to ask if you could go into a bit more detail. I'm a self-taught Access user so sometimes the technical terms evade me. It sounds like your process is simple but I'm a little lost at how to begin.

 

[pbaldy]

Here's my recordset loop template code:

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT ..."
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  Do While Not rs.EOF

    rs.MoveNext
  Loop

  set rs = nothing
  set db = nothing