Loop not giving expected output

[branston]

Hi,

Ive just created a loop in my VBA code (1st one ive ever done) and im having some trouble getting it to output what i expected it to.

I have some code before it which looks in a big table (TblA) and copies certain records into a smaller table (TblB)
I want the loop to go through every record in TblB and store the EmailAdd in a variable (so i end up with 1 long list of EmailAdds, seperated by a
this is what i ahve so far:

Set rst = CurrentDb.OpenRecordset("TblB")

If rst.RecordCount > 0 Then
rst.MoveFirst
Do While Not rst.EOF
strTo = strTo & ";" & [EmailAdd]
rst.MoveNext
Loop
End If
I ask it to output the strTo in a msgbox and I get the correct NUMBER of records, but they are all the EmailAdd of the 1st person in TblA.

Am i missing a vital bit of code which tells it to look in TblB?

Thank you!

 

[DCrake]

Set rst = CurrentDb.OpenRecordset("TblB")

If rst.RecordCount > 0 Then
rst.MoveFirst
Do While Not rst.EOF
strTo = strTo & ";" & [EmailAdd]
rst.MoveNext
Loop
End If

Correct code is:

Set rst = CurrentDb.OpenRecordset("Select * From TblB Where EmailAdd Is Not Null")

If Not rst.EOF And Not rst.BOF Then

   Do Until rst.EOF
      StrTo = StrTo & ";" & rst("EmailAdd")
      rst.MoveNext
   Loop
   rst.Close
End If

strTo = Mid(strTo,2)   'Drop the first semi-colon


set rst = Nothing

David

 

[branston]

Hi David,

Thanks for your help so far... looks like i was way off the mark!!
But now im getting an error on the line:

StrTo = StrTo & ";" & rst("EmailAdd")

The error is "Item not found in this collection"

Any ideas?

 

[branston]

Oops, my mistake - i missed out the "s on that line!
Works great now, thank you so much!