Looping a RecordSet through query criteria

[MCTampa]

I have a table with approx. 90 resort ID's.
I want to set each resort ID as the criteria in my Access Query 1.
After Query 1 is run, there are 15 additional queries which basically format the information for a report.

I want to take the first record, set it as my criteria in Query 1, run queries 1-15, produce and email my report as a PDF and then (essentially) rst.movenext to the next record and repeat.

I'm not sure how to get started with writing the VBA for this.

 

[ranman256]

make a form, with a list box showing the resort data: ResortID, ResortName

the report will use a query with this listbox as the criteria ID:
select * from table where [resortID]=forms!fMyForm!lstBox

select the resort int the list , then open the report and only get that resorts data.

to cycle through ALL the resorts:

sub btnPrintAll_click()
Dim i As Integer
dim vItm, vFile, vID, vResort

For i = 0 To lstBox.ListCount - 1
   vItm= lstBox.ItemData(i)  'get next item in list data
   lstBox = vItm     'set listbox to the item
 
     'now get values from field columns
   vID = lstBox.column(0)     'in vb, columns start with zero
   vName = lstBox.column(1)  'get Name from listbox ,col.2

      'do stuff with it here
    vFile = "c:\temp\Report_" & vName & ".pdf"

     'create pdf
docmd.OutputTo acOutputReport  "rMyReport", acReport ,acFormatPDF,vFile  


Next
end sub

 

[MCTampa]

So I don't have resort name, just resort ID.
I created my listbox and called it DLIST. It only has one column, which is the resort ID.

I tried to edit your VBA, but keep getting Object Required for the first line.

Thank you by the way!

Sub btnPrintAll_click()
Dim i As Integer
Dim vItm, vFile, vID, vResort

For i = 0 To lstBox.ListCount - 1
   vItm = lstBox.ItemData(i) 'get next item in list data
   lstBox = DLIST     'set listbox to the item
 
     'now get values from field columns
   vID = lstBox.Column(0)     'in vb, columns start with zero
   'vResort = lstBox.Column(1)  'get Name from listbox ,col.2

      'do stuff with it here
   DoCmd.OpenQuery "Copy of Query1", acViewNormal, acEdit

   
Next
End Sub

 

[MCTampa]

Also, not sure if this is correct, but I've set my criteria for Resort ID in my query to [Forms]![Form1]![DLIST]

 

[MCTampa]

Coming back to this, it keeps telling me that all of the elements (vItm, VID) are empty. Not sure what I'm doing wrong.

 

[Micron]

lstBox is a variable. I don't see where you Set any listbox to that variable so you'd get such an error.
Your 90 property id's are in rows of a table and not as 90 columns I hope. I don't see the point of a listbox for this if you're not using it to select items and do stuff with just the selected item(s). You could just build a recordset (if the 90 id's are in rows, not columns), loop through that rs and feed the id field values one by one to the 15 queries that you then run. Then move to the next id and rinse/repeat.

 

[MCTampa]

lstBox is a variable. I don't see where you Set any listbox to that variable so you'd get such an error.
Your 90 property id's are in rows of a table and not as 90 columns I hope. I don't see the point of a listbox for this if you're not using it to select items and do stuff with just the selected item(s). You could just build a recordset (if the 90 id's are in rows, not columns), loop through that rs and feed the id field values one by one to the 15 queries that you then run. Then move to the next id and rinse/repeat.

It's 90 rows, not columns.
I agree, I intended to run it right off of a table, not a listbox, but thought I was wrong.
Not sure how to do that though.

 

[MCTampa]

I guess a cleaner explanation would be that I want to take each record from a singular column of approx. 90 rows and place that value as the criteria in my Query, run the query and the succeeding set of queries, then loop back to the recordset and move to the next record.

 

[Micron]

One way:
- declare and create a recordset. Use a sql statement that returns the ID's as records (and whatever else you'd need, but I'm only seeing a need for id's).
- loop over that rs and either refer directly to it to pass rs.Fields("ID") as your criteria or assign rs.Fields("ID") to a variable.
- copy the sql from each query into code and replace what you have there for criteria with the rs reference (or variable)
- run the sql. Research the .Execute method of the CurrentDb and the DoCmd.RunSql method to see which you'd prefer. Your queries seem to alter records in preparation for your report (thus they would be actin queries) so that is the recommendation.

Here's a very simple example of running a sql statement.
Notes: the debug line would be commented out once the sql that is output to the immediate window looks correct. I'd suggest reviewing the output before attempting to run it. You cannot Execute a SELECT statement but that should not be an issue for you as I've noted.

Dim strSql As String

strSql = "UPDATE " & Me.cmbLanguage.Column(1) & " SET Flag = False WHERE Flag = true"
Debug.Print strSql
CurrentDb.Execute strSql, dbFailOnError

So you'd put your 15 edited sql statements in the order you require and run each using the concatenated sql string that works in your criteria.
Then you'd MoveNext on your rs and rinse/repeat until you've gone through all of the rs records.
HTH
Post your code attempt if you get stuck.

 

[MCTampa]

Why can't I declare the recordset in VBA as such:

Function RESORTID()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("select RCODE from DirectorList")
Do Until EOF = True


End Function

From here I was trying to put it in the SQL of my query as:
WHERE (((OPSR.RESX)=rs.Fields("RCODE")

But I get "undefined function 'rs.fields in expression"

I guess I need to declare my recordset in SQL, not VBA?