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]
[B]DoCmd.GoToRecord , , acNext[/B]
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.GotoRecor d 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:

[CODE=vb]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.Move First

Do Until recordset.EOF
emailgroup = recordset("emai l")
recordset.MoveN ext
emailgroup = emailgroup & ";" & recordset("emai l")
Loop
Debug.Print emailgroup[/CODE]

[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")
[B]recordset.MoveNext[/B]
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.

[CODE=vb]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.MoveN ext
Loop
Debug.Print emailgroup

recordset.Close
Set recordset = Nothing[/CODE]