Do While Loops

[davesmith202]

Will the following code loop until I hit the end of the recordset? Or does it hit the end of a recordset and then does the Do Loop code one last time?

Do
'Blah blah
rs.MoveNext
Loop While Not rs.EOF

Thanks,

Dave

 

[boblarson]

It won't loop past the point at which the While meets the condition. I would suggest using this method instead:

Do Until rs.EOF
  'Blah blah
   rs.MoveNext
Loop

 

[davesmith202]

Something is not quite right with my code. I get a No Current Record error.

Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT * FROM tblTextAdsTmp WHERE DefaultID=" & [Forms]![frmDefaults]![DefaultID] & " ORDER BY TextAdsID")

    strYahooPreviousAdgroup = ""
    rs.MoveFirst
    strYahooCSV = ""


Do Until rs.EOF
'if not first record and adgroup is different to previous adgroup, then add adgroup header
If rs!Adgroup <> strYahooPreviousAdgroup Then
strYahooCSV = strYahooCSV & rs!Campaign & "," & "" & "," & "Campaign,On,Active,,,,,,," & rs!YahooSearchStatus & "," & rs!YahooMatchType & ",,,," & rs!YahooContentStatus & ",,,,,,,Off" & Chr(13) & Chr(10)
End If
        
'if not first record and adgroup is different to previous adgroup then add advert
YahooCounter = DCount("Adgroup", "tblTextAdsTmp", "Adgroup='" & rs!Adgroup & "' AND DefaultID=" & rs!DefaultID)
        
For i = 1 To YahooCounter
        strYahooCSV = strYahooCSV & rs!Campaign & "," & rs!Adgroup & "," & "Ad,On,Active,,,,,,,,,,,,," & Int(100000 * Rnd(100000)) & "," & rs!Title & "," & rsLine1 & " " & rs!Line2 & ",," & rs!DisplayURL & rs!ActualURLYahoo & Chr(13) & Chr(10)
        rs.MoveNext
Next i
        
strYahooCSV = strYahooCSV & "," & "," & rs!Campaign & "," & rs!Adgroup & "," & "Keyword,On,Active," & rs!Keyword & ",,,Default,,,," & rs!YahooMatchType & ",,,,,,,,,,,Off" & Chr(13) & Chr(10)
        
strYahooPreviousAdgroup = rs!Adgroup
rs.MoveNext
Loop

It seems to store the right data into the string but just goes too far. Something obvious I'm missing?

The following line gets highlighted when the error appears:

strYahooCSV = strYahooCSV & rs!Campaign & "," & rs!Adgroup & "," & "Ad,On,Active,,,,,,,,,,,,," & Int(100000 * Rnd(100000)) & "," & rs!Title & "," & rsLine1 & " " & rs!Line2 & ",," & rs!DisplayURL & rs!ActualURLYahoo & Chr(13) & Chr(10)

 

[Rabbie]

Which line is it erroring on?

 

[Brianwarnock]

You are looping with movenext and no eof check.

For i = 1 To YahooCounter
        strYahooCSV = strYahooCSV & rs!Campaign & "," & rs!Adgroup & "," & "Ad,On,Active,,,,,,,,,,,,," & Int(100000 * Rnd(100000)) & "," & rs!Title & "," & rsLine1 & " " & rs!Line2 & ",," & rs!DisplayURL & rs!ActualURLYahoo & Chr(13) & Chr(10)
        rs.MoveNext
Next i

Brian

 

[davesmith202]

Gotcha! So I put something like this:

For i = 1 To YahooCounter
if rs.eof then exit do
'Blah
        rs.MoveNext
Next i

Is that right?

 

[Brianwarnock]

Well I would code it like this

For i = 1 To YahooCounter
'Blah
        rs.MoveNext
if rs.eof then exit do
Next i

Brian

 

[davesmith202]

As a side note, when I escape the do loop, do I leave the i variable hanging and need to close it off with a next i? I don't think I need to but I've always been unsure on this.

 

[Banana]

Set db = CurrentDb()
    Set rs = db.OpenRecordset("SELECT * FROM tblTextAdsTmp WHERE DefaultID=" & [Forms]![frmDefaults]![DefaultID] & " ORDER BY TextAdsID")

    strYahooPreviousAdgroup = ""
    rs.MoveFirst
    strYahooCSV = ""


Do Until rs.EOF
'if not first record and adgroup is different to previous adgroup, then add adgroup header
If rs!Adgroup <> strYahooPreviousAdgroup Then
strYahooCSV = strYahooCSV & rs!Campaign & "," & "" & "," & "Campaign,On,Active,,,,,,," & rs!YahooSearchStatus & "," & rs!YahooMatchType & ",,,," & rs!YahooContentStatus & ",,,,,,,Off" & Chr(13) & Chr(10)
End If
        
'if not first record and adgroup is different to previous adgroup then add advert
YahooCounter = DCount("Adgroup", "tblTextAdsTmp", "Adgroup='" & rs!Adgroup & "' AND DefaultID=" & rs!DefaultID)
        
For i = 1 To YahooCounter
        strYahooCSV = strYahooCSV & rs!Campaign & "," & rs!Adgroup & "," & "Ad,On,Active,,,,,,,,,,,,," & Int(100000 * Rnd(100000)) & "," & rs!Title & "," & rsLine1 & " " & rs!Line2 & ",," & rs!DisplayURL & rs!ActualURLYahoo & Chr(13) & Chr(10)
        [COLOR="Red"]rs.MoveNext[/COLOR]
Next i
        
strYahooCSV = strYahooCSV & "," & "," & rs!Campaign & "," & rs!Adgroup & "," & "Keyword,On,Active," & rs!Keyword & ",,,Default,,,," & rs!YahooMatchType & ",,,,,,,,,,,Off" & Chr(13) & Chr(10)
        
strYahooPreviousAdgroup = rs!Adgroup
[COLOR="red"]rs.MoveNext[/COLOR]
Loop

You basically have two different logics going on, and two .MoveNext for the same recordset. I'm not quite what you want to do, but wanted to point out that two .MoveNext may not be what you want to do.

Normally, if I am dealing with a subset of recordset, I would clone that into a separate variable and nest a separate loop to run through the cloned & filtered recordset inside a loop for the original unfiltered recordset or something similar to that. Or maybe it should actually be single loop using For...Next instead.

 

[davesmith202]

I needed two movenexts due to the data structure. I added a moveprevious and I got the result I wanted. All fixed now.