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.

 

[Micron]

You forgot rs - surprised if it didn't barf there - Do Until rs.EOF

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

No. Post your entire code - or at least enough of it to show your sql concatenation and how you handle the execution of at least one query. Concatenation is likely where the current problem lies. Did you debug.print and review the sql as you were advised to?

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.

If you did, then perhaps you don't quite understand concatenation. What you wrote is being literally interpreted as a function call by the name of rs.Fields("RCODE"), which of course does not exist.

This part
WHERE (((OPSR.RESX)=rs.Fields("RCODE")

would need to be more like
" ... WHERE (((OPSR.RESX)=" & rs.Fields("RCODE")

If the value is a string, you need string delimiters. Single quote would work well, as in
" ... WHERE (((OPSR.RESX)='" & rs.Fields("RCODE") & "'"

A good primer on using recordsets.

Microsoft Access tips: VBA Traps: Working with Recordsets

Common mistakes developers make when working with Recordsets in a Microsoft Access database

allenbrowne.com

 

[MCTampa]

You're exactly correct, I don't understand what the concatenation does or where to put it.
I'm a revenue management associate, not a developer, so I apologize for my ignorance.

I've been tinkering with it, but this is the extent of my code:

Function RESORTID()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
Set db = CurrentDb()
strSql = "Select RCODE from DirectorList"
Set rs = CurrentDb.OpenRecordset(strSql)

End Function

I'm not sure what to do with the below:

Dim strSql As String

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

 

[MCTampa]

Also, what is the point of concatenating here? All I want to do is take the value in the field RCODE and use it as the criteria for the query.
I'm not sure what the following is suposed to do:

strSql = "DeveloperList" & cmbLanguage.Column(0) & " SET Flag = False WHERE Flag = true"

 

[Micron]

No apologies necessary. When someone posts code and doesn't proclaim their newbie-ness I assume they have some vba experience. Sorry if I missed where you said that.

If you don't concatenate, the variables and references are taken literally. If I substitute values (e.g. 1 for the combo column(0) reference) then
strSql = "DeveloperList" & cmbLanguage.Column(0) & " SET Flag = False WHERE Flag = true"

could be interpreted as
strSql = "DeveloperList1 SET Flag = False WHERE Flag = true"
If that is conducive to your design then it's fine. Where is fails is when you don't do concatenation and references are taken literally, such as the examples I posted in post 11.

Will have to park this for a bit, but rest assured, I'm intent on getting a solution for you.

 

[welshgasman]

You need to concatenate rcode to your sql string.

 

[MCTampa]

Ok, so in this statement

strSql = "DeveloperList" & cmbLanguage.Column(0) & " SET Flag = False WHERE Flag = true"

My string is a concatenation of my reference table (DeveloperList), but I don't understand what cmbLanguage.Column(0) means. I get the column(0) reference in that it's the only column in my table, but what does cmbLanguage mean?

As for the last element, what does " SET Flag = False WHERE Flag = true" mean in plain English? What is this trying to do?

In one of your earlier replies you asked "Did you debug.print and review the sql as you were advised to?" What am I reviewing? Do I see the results of something?

 

[Micron]

Here's a debug statement in a snippet of code.

Sub UseLike()
Dim strSql As String
strSql = "SELECT EmployeeID FROM [Attendance Log] WHERE [Last Name] LIKE """ & "*"
strSql = strSql & "Pete" & "*"""
Debug.Print strSql

End Sub

The printout is in the immediate window of the vb editor. Result looks good to me.

If your immediate window is not visible I suggest you open it and leave it there.

We can only assume what's in your code and expressions when these things haven't been addressed or we can't see your project. I assumed cmbLanguage was a combo box you were using somewhere. If you don't know what it is then it suggests you have copied this code from somewhere and don't know how to adapt it to your project. Column(0) is a reference to the first column of the combo, regardless of how many it has.

" SET Flag = False WHERE Flag = true" mean in plain English? What is this trying to do?

This is part of a sql statement that edits data. It will set the values in the "FLAG" field to False wherever they are True. Does this mean you don't have a field named FLAG either?

 

[MCTampa]

Now I'm going (staggering...) in the right direction.

This

Sub UseLike()
Dim strSql As String
strSql = "SELECT RCODE FROM [DirectorList]" & """*"
strSql = strSql & "RCODE" & "*"""
Debug.Print strSql

End Sub

Yielded:
SELECT RCODE FROM [DirectorList]"*RCODE*"

And I'm not using any ComboBoxes, ListBoxes, or anything, I just want to take the RCODE value off of the DirectorList table and use it as my query criteria.

 

[Micron]

This will give you all the records (assuming there is more than one record in the field).
"SELECT RCODE FROM [DirectorList]"
Not sure why you tacked on the asterisks and field names.

If you can't build or troubleshoot sql because you haven't learned yet, then build a query in design that gives you what you want. Then switch to sql view, copy the sql and use it in code. Don't forget to concatenate as required though, because sql is not usable in vba if you have references to forms/controls and such.

 

[MCTampa]

My query was built in design mode and it works perfectly if I'm referencing a single resort code, even if that resort code is in a text field.
My form was originally designed for the user to select a single resort code from a drop down and run the report.

Now I want to be able to load a list (DirectorList) and run each code in a batch, producing the PDF report one by one and emailing them out.

Selecting all of the records is fine, I guess, if I can then apply them one by one to my query.

Below is the SQL of my query.
You said above "Then switch to sql view, copy the sql and use it in code." I don't know what that means.
I'm trying to place it in my Where statement, but it's not working and I'm getting a syntax error.

SELECT Trim([OPSR]![Resort ID]) AS [Resort ID], IIf([OPSR]![Super Type]="AVAIL",1,IIf([opsr]![Super Type]="BLOCKED",2,IIf([opsr]![Super Type]="COVID",3,IIf([opsr]![Super Type]="RETN",4,IIf([opsr]![Super Type]="STPSLE",5,IIf([opsr]![Super Type]="UTIL",6,"")))))) AS Sort, OPSR.[Super Type], [OPSR]![UCDX] & " " & "BR" AS UCDX, OPSR.[Check In Year], OPSR.Payment, Sum(IIf([OPSR]![WKX]=1,[OPSR]![CountOfSPACE#],0)) AS w1, Sum(IIf([OPSR]![WKX]=2,[OPSR]![CountOfSPACE#],0)) AS w2, Sum(IIf([OPSR]![WKX]=3,[OPSR]![CountOfSPACE#],0)) AS w3, Sum(IIf([OPSR]![WKX]=4,[OPSR]![CountOfSPACE#],0)) AS w4, Sum(IIf([OPSR]![WKX]=5,[OPSR]![CountOfSPACE#],0)) AS w5, Sum(IIf([OPSR]![WKX]=6,[OPSR]![CountOfSPACE#],0)) AS w6, Sum(IIf([OPSR]![WKX]=7,[OPSR]![CountOfSPACE#],0)) AS w7, Sum(IIf([OPSR]![WKX]=8,[OPSR]![CountOfSPACE#],0)) AS w8, Sum(IIf([OPSR]![WKX]=9,[OPSR]![CountOfSPACE#],0)) AS w9, Sum(IIf([OPSR]![WKX]=10,[OPSR]![CountOfSPACE#],0)) AS w10, Sum(IIf([OPSR]![WKX]=11,[OPSR]![CountOfSPACE#],0)) AS w11, Sum(IIf([OPSR]![WKX]=12,[OPSR]![CountOfSPACE#],0)) AS w12, Sum(IIf([OPSR]![WKX]=13,[OPSR]![CountOfSPACE#],0)) AS w13, Sum(IIf([OPSR]![WKX]=14,[OPSR]![CountOfSPACE#],0)) AS w14, Sum(IIf([OPSR]![WKX]=15,[OPSR]![CountOfSPACE#],0)) AS w15, Sum(IIf([OPSR]![WKX]=16,[OPSR]![CountOfSPACE#],0)) AS w16, Sum(IIf([OPSR]![WKX]=17,[OPSR]![CountOfSPACE#],0)) AS w17, Sum(IIf([OPSR]![WKX]=18,[OPSR]![CountOfSPACE#],0)) AS w18, Sum(IIf([OPSR]![WKX]=19,[OPSR]![CountOfSPACE#],0)) AS w19, Sum(IIf([OPSR]![WKX]=20,[OPSR]![CountOfSPACE#],0)) AS w20, Sum(IIf([OPSR]![WKX]=21,[OPSR]![CountOfSPACE#],0)) AS w21, Sum(IIf([OPSR]![WKX]=22,[OPSR]![CountOfSPACE#],0)) AS w22, Sum(IIf([OPSR]![WKX]=23,[OPSR]![CountOfSPACE#],0)) AS w23, Sum(IIf([OPSR]![WKX]=24,[OPSR]![CountOfSPACE#],0)) AS w24, Sum(IIf([OPSR]![WKX]=25,[OPSR]![CountOfSPACE#],0)) AS w25, Sum(IIf([OPSR]![WKX]=26,[OPSR]![CountOfSPACE#],0)) AS w26, Sum(IIf([OPSR]![WKX]=27,[OPSR]![CountOfSPACE#],0)) AS w27, Sum(IIf([OPSR]![WKX]=28,[OPSR]![CountOfSPACE#],0)) AS w28, Sum(IIf([OPSR]![WKX]=29,[OPSR]![CountOfSPACE#],0)) AS w29, Sum(IIf([OPSR]![WKX]=30,[OPSR]![CountOfSPACE#],0)) AS w30, Sum(IIf([OPSR]![WKX]=31,[OPSR]![CountOfSPACE#],0)) AS w31, Sum(IIf([OPSR]![WKX]=32,[OPSR]![CountOfSPACE#],0)) AS w32, Sum(IIf([OPSR]![WKX]=33,[OPSR]![CountOfSPACE#],0)) AS w33, Sum(IIf([OPSR]![WKX]=34,[OPSR]![CountOfSPACE#],0)) AS w34, Sum(IIf([OPSR]![WKX]=35,[OPSR]![CountOfSPACE#],0)) AS w35, Sum(IIf([OPSR]![WKX]=36,[OPSR]![CountOfSPACE#],0)) AS w36, Sum(IIf([OPSR]![WKX]=37,[OPSR]![CountOfSPACE#],0)) AS w37, Sum(IIf([OPSR]![WKX]=38,[OPSR]![CountOfSPACE#],0)) AS w38, Sum(IIf([OPSR]![WKX]=39,[OPSR]![CountOfSPACE#],0)) AS w39, Sum(IIf([OPSR]![WKX]=40,[OPSR]![CountOfSPACE#],0)) AS w40, Sum(IIf([OPSR]![WKX]=41,[OPSR]![CountOfSPACE#],0)) AS w41, Sum(IIf([OPSR]![WKX]=42,[OPSR]![CountOfSPACE#],0)) AS w42, Sum(IIf([OPSR]![WKX]=43,[OPSR]![CountOfSPACE#],0)) AS w43, Sum(IIf([OPSR]![WKX]=44,[OPSR]![CountOfSPACE#],0)) AS w44, Sum(IIf([OPSR]![WKX]=45,[OPSR]![CountOfSPACE#],0)) AS w45, Sum(IIf([OPSR]![WKX]=46,[OPSR]![CountOfSPACE#],0)) AS w46, Sum(IIf([OPSR]![WKX]=47,[OPSR]![CountOfSPACE#],0)) AS w47, Sum(IIf([OPSR]![WKX]=48,[OPSR]![CountOfSPACE#],0)) AS w48, Sum(IIf([OPSR]![WKX]=49,[OPSR]![CountOfSPACE#],0)) AS w49, Sum(IIf([OPSR]![WKX]=50,[OPSR]![CountOfSPACE#],0)) AS w50, Sum(IIf([OPSR]![WKX]=51,[OPSR]![CountOfSPACE#],0)) AS w51, Sum(IIf([OPSR]![WKX]=52,[OPSR]![CountOfSPACE#],0)) AS w52, Sum(IIf([OPSR]![WKX]=53,[OPSR]![CountOfSPACE#],0)) AS w53, Sum([OPSR]![CountOfSPACE#]) AS TOT INTO [Output]
FROM OPSR
WHERE (((OPSR.RESX)='"& rs.Fields("RCODE")&"'"))
GROUP BY Trim([OPSR]![Resort ID]), IIf([OPSR]![Super Type]="AVAIL",1,IIf([opsr]![Super Type]="BLOCKED",2,IIf([opsr]![Super Type]="COVID",3,IIf([opsr]![Super Type]="RETN",4,IIf([opsr]![Super Type]="STPSLE",5,IIf([opsr]![Super Type]="UTIL",6,"")))))), OPSR.[Super Type], [OPSR]![UCDX] & " " & "BR", OPSR.[Check In Year], OPSR.Payment
HAVING (((OPSR.[Check In Year])=[Forms]![Form1]![Text124] Or (OPSR.[Check In Year])=[Forms]![Form1]![Text126] Or (OPSR.[Check In Year])=[Forms]![Form1]![Text128] Or (OPSR.[Check In Year])=[Forms]![Form1]![Text132] Or (OPSR.[Check In Year])=[Forms]![Form1]![Text130] Or (OPSR.[Check In Year])=[Forms]![Form1]![Text134] Or (OPSR.[Check In Year])=[Forms]![Form1]![Text138] Or (OPSR.[Check In Year])=[Forms]![Form1]![Text142] Or (OPSR.[Check In Year])=[Forms]![Form1]![Text140] Or (OPSR.[Check In Year])=[Forms]![Form1]![Text136] Or (OPSR.[Check In Year])=[Forms]![Form1]![Text144] Or (OPSR.[Check In Year])=[Forms]![Form1]![Text146]));