For While - Loop problem

[gauja]

Hi all
Can anyone help me with this code. I am having a form using a query to list my coustomer to be sent email who should be on time for apointment.
In Access 2000 I used While-Wend and it worked but in access 2007 it didn´t work so I change to Do While - Loop
Here is the code in Access 2000:
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.MoveFirst
DoCmd.GoToRecord , , acFirst
While (Not (rst.EOF))

If IsNull(Me.FRTE) And Not IsNull(Me.txtEmail) Then
Dim strStDocName As String

strStDocName = "FBolusetning"
DoCmd.SendObject acReport, strStDocName, acFormatRTF, txtEmail, , , "Minnum á bólusetningu, sjá viðhengi", "Skilaboð frá " & " " & DLookup("Stofa", "Notandi"), False
End If

rst.MoveNext
If Not rst.EOF Then
DoCmd.GoToRecord , , acNext

End If
Wend

So I tryed to change it in Access 2007 to:
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb

Set rst = Me.RecordsetClone
rst.MoveFirst
DoCmd.GoToRecord , , acFirst

Do While Not rst.EOF

If IsNull(Me.FRTE) And Not IsNull(Me.txtEmail) Then
Dim strStDocName As String

strStDocName = "FBolusetning"
DoCmd.SendObject acReport, strStDocName, acFormatRTF, txtEmail, , , "Minnum á bólusetningu, sjá viðhengi", "Skilaboð frá " & " " & DLookup("Stofa", "Notandi"), False
End If

rst.MoveNext
If (Not (rst.EOF)) Then
DoCmd.GoToRecord , , acNext
End If

Loop
If I didn´t use DoCmd.GoToRecord , , acNext then it didn´t go to next Record but now it give the error on the last record "Can´t go to the specific record"
So how can I get it to go through all my records to send without giving a notice and stoop after the last record? What am I doing wrong?

Gauja

 

[namliam]

That should never have worked...
DoCmd.GoToRecord , , acNext
Moves the form, but doesnt move the recordset, the recordset is moved by the previous line
rst.MoveNext
Which is probably throwing your error, I expect you need to move that into your IF.

FYI, if your posting code please use the code tags [ code ] and [ /code ] around it (without the spaces) or use the # button on top of the post menu.
Not using the code tags collapses any formatting of the code and makes it increadably hard to read

 

[gauja]

That should never have worked...
DoCmd.GoToRecord , , acNext
Moves the form, but doesnt move the recordset, the recordset is moved by the previous line
rst.MoveNext
Which is probably throwing your error, I expect you need to move that into your IF.

FYI, if your posting code please use the code tags [ code ] and [ /code ] around it (without the spaces) or use the # button on top of the post menu.
Not using the code tags collapses any formatting of the code and makes it increadably hard to read

Thanks, but if I didn´t use acNext it only sent message many time to the same person, it never went down the row of records

 

[jal]

Thanks, but if I didn´t use acNext it only sent message many time to the same person, it never went down the row of records

Well yes - you have to decide where you want to pull the data from. Do you want to pull it from the form? Then yes, you would probably want to use AcNext to move the form to the next record.

Do you want to pull it from the recordset? You use MoveNext as though yuo want to pull it from the recordset - but then you never actually use the data in the recordset (that I can see) so it seems that you are just wasting the recordset.

Make up your mind as to where you want to pull the data from.

 

[namliam]

Thanks, but if I didn´t use acNext it only sent message many time to the same person, it never went down the row of records

Because you are getting the data of the form:

If IsNull([B][U]Me.FRTE[/U][/B]) And Not IsNull([B][U]Me.txtEmail[/U][/B]) Then
...
DoCmd.SendObject acReport, strStDocName, acFormatRTF, [B][U]txtEmail[/U][/B], , , "Minnum á bólusetningu, sjá viðhengi", "Skilaboð frá " & " " & DLookup("Stofa", "Notandi"), False
...

Not from the recordset, which would ie. be rst.txtEmail

Basicaly your problem comes from the fact your doing 2 things at the same time, working with the form and working with a recordset.
Eliminate the form or eliminate the recordset and solve your problem.