code to move to next record

[Neekos]

ok i have the following snippet of code. All i want it to do is move to the next record, but i keep getting an error of: You cannot go to the specified record.

amount = DCount("*", "[Sheet1]")
counter = 0
Debug.Print amount
 
Do
emailgroup = Me![email]
 DoCmd.GoToRecord , , acNext
emailgroup = emailgroup & ";" & Me![email]
counter = counter + 1
Loop Until counter = amount
Debug.Print emailgroup

how do i tell my loop to go the next record?

[cyberdwarf]

Maybe you are reading past the last record???

DoCmd.GotoRecord will move through the records by displaying them on screen. You might want to think about looping through an ADO recordset and processing each record that way.

HTH

Steve

[Neekos]

thanks steve - i went with the recordset and here's where im at:

Set recordset = New ADODB.recordset
recordset.Open "sheet1", CurrentProject.Connection
recordset.MoveFirst
 
amount = DCount("*", "[Sheet1]")
counter = 0
Debug.Print amount
 
Do
emailgroup = Me![email]
recordset.MoveNext
emailgroup = emailgroup & ";" & Me![email]
counter = counter + 1
Loop Until counter = amount
Debug.Print emailgroup

its returning the first email twice and stopping. i know my loop isnt right. can you help?

[cyberdwarf]

The basic routine is:-

Dim RsDetails As New RecordsetDim SQL As String
    SQL = "Select * from MyTable"
    RsDetails.Open SQL, CurrentProject.Connection
    Do Until RsDetails.EOF
        Debug.Print RsDetails("Field1")
        Debug.Print RsDetails("Field2")
        RsDetails.MoveNext
    Loop
    RsDetails.Close
    Set RsDetails = Nothing

HTH

Steve

[Neekos]

im getting an error "either BOF or EOF is true, or the current record has been deleted. Requested operation requires a current record."


Here's my code:

Dim emailgroup as String
Dim recordset As ADODB.recordset
Dim SQL As String
 
Set recordset = New ADODB.recordset
SQL = "SELECT * FROM qryAARP"
recordset.Open SQL, CurrentProject.Connection
 
'recordset.MoveFirst
 
Do Until recordset.EOF
emailgroup = recordset("email")
recordset.MoveNext
emailgroup = emailgroup & ";" & recordset("email")
Loop
Debug.Print emailgroup

[Neekos]

ok i fixed that error by adding the bold line

Dim emailgroup as String
Dim recordset As ADODB.recordset
Dim SQL As String
 
Set recordset = New ADODB.recordset
SQL = "SELECT * FROM qryAARP"
recordset.Open SQL, CurrentProject.Connection
 
'recordset.MoveFirst
 
Do Until recordset.EOF
emailgroup = recordset("email")
recordset.MoveNext
emailgroup = emailgroup & ";" & recordset("email")
 recordset.MoveNext
Loop
Debug.Print emailgroup

But i know my logic isnt right. I want the loop to continually concatenate each email. Right now its continually returning 2 emails. Can i get some help with this?

[Neekos]

ok i got it to work with some change of code. I'll post the code in case anyone wants to know how to concatenate a string of multiple records. In this case its concatenating a list of emails.

Dim emailgroup As String
Dim recordset As ADODB.recordset
Dim SQL As String
 
emailgroup = ""
Set recordset = New ADODB.recordset
SQL = "SELECT * FROM qryTVLY"
recordset.Open SQL, CurrentProject.Connection
 
Do While Not recordset.EOF
  If emailgroup = "" Then
    emailgroup = recordset!Email
  Else
    emailgroup = emailgroup & "; " & recordset!Email
  End If
  recordset.MoveNext
Loop
Debug.Print emailgroup
 
recordset.Close
Set recordset = Nothing