Loop Help

[vbaInet]

Why are you moving the recordset Last? The code I gave you simply does a Do While loop until it get's to the End Of File (i.e. EOF).

You are looping through a recordset what's the point of saving it into an array again? What if the max number of records for a particular type is more than 10, how do you cater for that dynamically?

Follow the bare structure I gave you in my first post, for each value in the recordset, concatenate it to a string variable.

 

[rnutts]

I am getting a message stating that I have a Do Loop Running which is not terminated. I have looked at vba help and cannot see why the second loop (below) keeps giving this error

   Set rst = CurrentDb.OpenRecordset(strsql2)
            intUpBound = rst.RecordCount
            rst.MoveFirst
            For intcounter = 1 To intUpBound
            Do While Not rst.EOF
                If Len(Me.JobNumber & vbNullString) > 0 Then
                MsgBox rst!RemRoom, vbOKOnly
                Exit Do
            Loop
            rst.MoveFirst
                line1 = arrcount(1) & vbCrLf & arrcount(2) & vbCrLf & arrcount(3) & vbCrLf & arrcount(4) & vbCrLf & arrcount(5) & vbCrLf & arrcount(6) & vbCrLf & arrcount(7) & vbCrLf & arrcount(8) & vbCrLf & arrcount(9) & vbCrLf & arrcount(10)
            .body = line1
                rst.Close
                Set rst = Nothing
            End If[\code]

 

[vbaInet]

I have looked at vba help and cannot see why the second loop (below) keeps giving this error

We keep talking about the same thing. Have you gotten the second loop working on its own before integrating it with the first loop?

Debug > Compile

[\code]

It's the back slash (/), not the forward slash (\). Please use that going forward.

 

[rnutts]

I am still no further on than yesterday.
I have the second loop working on another cmd button (code below)
but I have no idea how to collect the variable into a string and then use that string outside of the loop

I have searched these and other forums for posts I can relate to my own, but cannot see any similar.
Could you either give me some more detail on how to do it or point to to some posts which are similar

Thanks

Private Sub Command102_Click()
Dim rst As dao.Recordset
Dim strsql2 As String
Dim intupbound As Integer
Dim line1 As String
Dim subjob As String
strsql2 = "SELECT tblAssistEnvSubJobs.[RemJobNo], tblassistenvsubjobs.[remsubjobno], tblAssistEnvSubJobs.[RemSeperator], tblAssistEnvSubJobs.[RemRoom], tblAssistEnvSubJobs.[RemItemLoc], tblAssistEnvSubJobs.[RemItem]" & _
          "FROM tblAssistEnvSubJobs " & _
          "WHERE (((tblAssistEnvSubJobs.[RemJobNo])= " & [Forms]![frmnapswork]![JobNumber] & "));"

Set rst = CurrentDb.OpenRecordset(strsql2)
For intupbound = 1 To rst.RecordCount
Do While Not rst.EOF
    If Len(Me.JobNumber & vbNullString) > 0 Then
        MsgBox rst!RemRoom & rst!RemItemLoc, vbOKOnly
    End If
   rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Sub

 

[vbaInet]

For intupbound = 1 To rst.RecordCount

Just wondering what you're using intupbound for?

Let's take this part of your code then.

[COLOR=Blue]First Loop[/COLOR]

    Do While Not rst.EOF
        If Len(Me.JobNumber & vbNullString) > 0 Then
            [COLOR=Red]strInner[/COLOR] = [COLOR=Red]strInner[/COLOR] & rst!RemRoom & vbTab & rst!RemItemLoc & vbNewLine
        End If
        rst.MoveNext
    Loop

[COLOR=Blue]Use [COLOR=Red]strInner [/COLOR]here with values from your first loop
End of First Loop[/COLOR]

strInner is just a String variable. There are other ways of doing what you're trying to do here, but this is easier.