Loop through a query

[Eljefegeneo]

I am very new to loops, was afraid to try them but now one would come in very handy. I've copied come code that seems to work OK, but I cannot get the condition part of the report code to function correctly. What I am trying to do is loop through a query record set of ten items, print two different reports for each with the same condition.
The recordset is the query:

 SELECT tblAgents.CountryReference, tblAgents.AgentID, tblAgents.Active
  FROM tblAgents
  WHERE (((tblAgents.Active)=True));

The code (modified so that I can check that the AgentID number has been passed to the condition) is:

  On Error GoTo Error_Handler
      Dim db              As DAO.Database
      Dim rs              As DAO.Recordset
      Dim iCount      As Integer
   Dim rpt, rpt1, cond, cond1 As String
  rpt = "rptEmailReportCurrentMonth"
  rpt1 = "rptEmailReportCurrentMonthReceived"
  cond = "[AgentID] = AgentID"
      Set db = CurrentDb()
      Set rs = db.OpenRecordset("qryAgentIdList")
  cond = "[AgentID] = rs.AgentID"
      With rs
          'If .RecordCount <> 0 Then
              
              rs.MoveFirst
              iCount = rs.RecordCount
              
              Do While Not .EOF
                  MsgBox rpt & " " & "For Agent#:" & " " & cond
          MsgBox rpt1 & " " & "For Agent#:" & " " & cond
                  .MoveNext
              Loop
          'End If
      End With
   
      rs.Close
   
  Error_Handler_Exit:
      On Error Resume Next
    
      Set rs = Nothing
      Set db = Nothing
      Exit Sub
   
  Error_Handler:
      MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
      Err.Number & vbCrLf & "Error Source: LoopRecExample" & vbCrLf & "Error Description: " & _
      Err.Description, vbCritical, "An Error has Occured!"
      Resume Error_Handler_Exit

When I run the code the AgentID is not passed to the "cond" which is

 cond = "[AgentID] = AgentID"

When I run the code I just get a message box saying:

"rptEmailReportCurrentMonth For Agent#: [AgentId]"
"rptEmailReportCurrentMonthReceived For Agent#: [AgentID]"

How do I pass the condition for each item in the loop? Thanks.

 

[pbaldy]

Inside the loop:

cond = "[AgentID] = " & rs!AgentID

 

[TJPoorman]

Your problem is you're not assigning your variable, you're assigning a literal string.

cond = [COLOR="Red"]"[AgentID] = AgentID"[/COLOR]
      Set db = CurrentDb()
      Set rs = db.OpenRecordset("qryAgentIdList")
  cond = [COLOR="red"]"[AgentID] = rs.AgentID"[/COLOR]

Instead use:

cond = "[AgentID] = " & rs!AgentID

 

[TJPoorman]

Darn, Paul beat me to it. lol

 

[pbaldy]

Fastest fingers in the west!

 

[Uncle Gizmo]

Try:-

Option Compare Database
Option Explicit

Private Sub Command1_Click()
On Error GoTo Error_Handler
 

Dim iCount As Integer
Dim rpt As String
Dim rpt1 As String
Dim cond As String

'Dim cond1 As String    'Not Used

rpt = "rptEmailReportCurrentMonth"
rpt1 = "rptEmailReportCurrentMonthReceived"
cond = "[AgentID] = AgentID"
   
Dim db As DAO.Database
Dim rs As DAO.Recordset

'"qryAgentIdList"
'SELECT tblAgents.CountryReference, tblAgents.AgentID, tblAgents.Active
'FROM tblAgents
'WHERE (((tblAgents.Active)=True));

Set db = CurrentDb()
Set rs = db.OpenRecordset("qryAgentIdList")

      With rs
              rs.MoveFirst
              
              Do While Not .EOF
                    cond = "[AgentID] = " & rs!AgentID
                    MsgBox rpt & " " & "For Agent#:" & " " & cond
                    .MoveNext
              Loop
      End With
   
      rs.Close
   
Error_Handler_Exit:
      On Error Resume Next
    
      Set rs = Nothing
      Set db = Nothing
      Exit Sub
   
Error_Handler:
      MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
      Err.Number & vbCrLf & "Error Source: LoopRecExample" & vbCrLf & "Error Description: " & _
      Err.Description, vbCritical, "An Error has Occured!"
      Resume Error_Handler_Exit
End Sub

 

[Uncle Gizmo]

I would also like to say this is a very well presented question that has everything necessary to make it a pleasure to answer.

 

[pbaldy]

Try:-

Your code has the same flaw noted by both TJ and I.

 

[Uncle Gizmo]

Your code has the same flaw noted by both TJ and I.

Ah! Missed that!.... Corrected

 

[jdraw]

In addition to the advice/suggestions offered by others, I would like to clarify part of what Uncle Gizmo did in his response.

Yes he kept the variable inside the quote-- wrong(I'm sure it was fast typing).
Paul and TJ showed and corrected the issue.

But my point is that this code does not do what you think

Dim rpt, rpt1, cond, cond1 As String

In Access vba you have to explicitly Dim your variables
eg

Dim rpt As String, rpt1 As String, cond As String, cond1 As String

OR
Dim rpt As String
Dim rpt1 As String
Dim cond As String
Dim cond1 As String

Your code
Dim rpt, rpt1, cond, cond1 As String
will result in
rpt,rpt1 and cond being Variants
cond1 will be a string.

 

[Galaxiom]

Dim iCount As Integer
Dim rpt As String
Dim rpt1 As String
Dim cond As String

rpt = "rptEmailReportCurrentMonth"
rpt1 = "rptEmailReportCurrentMonthReceived"
cond = "[AgentID] = AgentID"

No point declaring variables then assigning their value statically. Might as well use Constants instead. They are inserted directly into the code at compile time. Code is clearer that way.

Const rpt = "rptEmailReportCurrentMonth"
Const rpt1 = "rptEmailReportCurrentMonthReceived"
Const cond = "[AgentID] = AgentID"

      With rs
              rs.MoveFirst
              
              Do While Not .EOF
                    cond = "[AgentID] = " & rs!AgentID
                    MsgBox rpt & " " & "For Agent#:" & " " & cond
                    .MoveNext
              Loop
      End With

Best test the recordset has records before using MoveFirst. It isn't really necessary because the Do While condition will skip if there are no records and the recordset will be at the first record when opened.

If you are going to have a With block then best use it consistently by avoiding referring to it again inside the block.

If .RecordCount <> 0 Then

Note that a DAO recordset requires a MoveLast before the RecordCount becomes valid.

 

[Eljefegeneo]

First of all, thank you all for answering. I think I am beginning to understand the loop and setting the conditions. The first time I tried to modify the code and put the

  cond = "[AgentID] = " & rs!AgentID

Outside the Do loop it didn't go through all the records, getting stuck on the first one. The first AgentID appeared 22 times instead of twice, there being 11 records.

      Set db = CurrentDb()
      Set rs = db.OpenRecordset("qryAgentIdList")
  cond = "[AgentID] = " & rs!AgentID
      With rs
              
              rs.MoveFirst
              iCount = rs.RecordCount
                          Do While Not .EOF
                  MsgBox rpt & " " & "For Agent#:" & " " & cond
          MsgBox rpt1 & " " & "For Agent#:" & " " & cond
                  .MoveNext
      End With

Then when I put it into the lookup as Uncle Gizmo suggested, it worked as required.

rs.MoveFirst  
 Do While Recordset.EOF = False     
                  MsgBox rpt & " " & "For Agent#:" & " " & cond
          MsgBox rpt1 & " " & "For Agent#:" & " " & cond
       Recordset.MoveNext   Loop

I assume there is a difference?

And I thank JDraw for the explanation on variants. There was no cond1, but it is good to know anyway.

And, UncleGizmo, yes, I did try to explain what I was doing and what I wanted as the outcome. So I appreciate your comments.

 

[pbaldy]

Did you miss:

Inside the loop:

If you do it before, it will only return the first record. The reference to the recordset value gets the value at that point; it does not update as the recordset advances.