EOF to stop program loop not working (XP)

**JudyJones** · 2004-12-21 13:56

I have the following code attached to a button on a form to email a report to one location at a time until the end of the file is reached. The program is not paying attention to the line containing the "Do Until rst.EOF" note and was giving me a 2105 error when it reached that point. I was able to make it work by using the ErrHandler to stop execution when the error is 2105 but I would like to know what was wrong that my EOF code so that it would not stop the loop process.

Private Sub Email2Sch_Click()
On Error GoTo ErrHandler
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("qrySchContacts")
Dim emailto As String
emailto = Forms!frmEmail!ContactEMail
Do Until rst.EOF

DoCmd.SendObject acReport, "rptSchoolErrors", "RichTextFormat(*.rtf)", emailto, "", "", "SASI Errors", "Please See the Attached Report. Right click on it, choose open and MS Word will convert it from Rich Text Format", False, ""
DoCmd.GoToRecord , , acNext
Loop
Exit Sub
ErrHandler:
Select Case Err
Case 2105 'End of File
Exit Sub
DoCmd.Close "frmEmail"
DoCmd.Quit

Case Else
MsgBox Err.Description, vbExclamation
Resume
End Select
End Sub

**HansV** · 2004-12-21 14:12

You mive to the next record in the form, but you don't move to the next record in the recordset, so you never reach EOF. As far as I can see, you don't use the recordset at all in your code. You might as well remove it and use

Do
...
Loop

still using the error handler to stop if you reach the last record in the form.

BTW, shouldn't you have a line

DoCmd.GoToRecord , , acFirst

before the loop?

**JudyJones** · 2004-12-22 15:34

Thanks for your suggestions which I have implemented. I do not understand enough about recordsets so I don't understand the difference between moving to the next record in the form and moving to the next record in the recordset. The modified code now works great when I click the command button to which I attached the code to the on click event. However, when I tried using the code from the on open event of the form so it would run automatically I get the error that I can not get to the GoToRecord Action or Method in Design View. I tried using several other events on the form but got the same error message.

Private Sub Form_Open(Cancel As Integer)
On Error GoTo ErrHandler
Dim emailto As String
emailto = Forms!frmEmail!ContactEMail
DoCmd.GoToRecord , , acFirst
Do
DoCmd.SendObject acReport, "rptSchoolErrors", "RichTextFormat(*.rtf)", emailto, "", "", "SASI Errors", "Please See the Attached Report. Right click on it, choose open and MS Word will convert it from Rich Text Format", False, ""
DoCmd.GoToRecord , , acNext
Loop
Exit Sub
ErrHandler:
Select Case Err
Case 2105 'End of File
DoCmd.Quit
Exit Sub

Case Else
MsgBox Err.Description, vbExclamation
Resume
End Select

End Sub

**HansV** · 2004-12-22 15:45

When you open a form, it is automatically at the first record, so you don't need the line

DoCmd.GoToRecord , , acFirst

You can remove it from the On Open event. It would only be needed if the code was called from a command button, and if the user might already have moved to another than the first record. If you still have problems, try the On Load event instead of the On Open event.

If your only goal is to send the report to all recipients in the record source of the form, it would be more efficient to use a recordset and not open the form at all. But if you can get it to work as it is, never mind.

**JudyJones** · 2004-12-22 16:17

If it would be more efficient to use a recordset, what needs to be modified in my original code to make it use a recordset and move to each record in the recordset?

**HansV** · 2004-12-22 16:25

Does the report refer to the form? If so, how?

And in your code, the e-mail address is fixed, it is not updated within the loop. Is that what you intended?

**JudyJones** · 2004-12-22 16:46

The form displays information about one school which changes to the next school as it moves to a new record. The report displays errors that each school needs to fix and is specific for the school record currently displayed on the form. The email address is specific for each school and it also changes as the code moves to each record displayed on the form.

**HansV** · 2004-12-22 17:48

Thank you for the information. How does the report know which record is displayed in the form? Does the record source of the report have criteria that look like [Forms]![frmEmail]![SchoolID] or something like that? Please be specific.

**JudyJones** · 2004-12-22 18:03

The record source for the report is displaying info for one school based on [Forms]![frmEmail]![schoolnum]. When I move to the next record on the form the schoolnum changes and the report for the next school is generated.

**HansV** · 2004-12-22 19:31

Here is a way to do it without moving from record to record in a form.

1. In a standard module, put this code

Public glngSchoolNum As Long

Public Function Email2Schools()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

On Error GoTo ErrHandler

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qrySchContacts", dbOpenDynaset)

Do While rst.EOF = False
glngSchoolNum = rst!SchoolNum
DoCmd.SendObject acSendReport, "rptSchoolErrors", acFormatRTF, _
rst!ContactEMail, , , "SASI Errors", "Please See the Attached Report. " & _
"Right click on it, choose open and MS Word will convert it " & _
"from Rich Text Format", False
rst.MoveNext
Loop

ExitHandler:
On Error Resume Next
glngSchoolNum = 0
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Function

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Function

(I have assumed that SchoolNum is a number variable)

2. Remove the criteria [Forms]![frmEmail]![schoolnum] from the query qrySchContacts.

3. Put the following code in the On Open event procedure of the report:

Private Sub Report_Open(Cancel As Integer)
If lngSchoolNum > 0 Then
Me.RecordSource = _
"SELECT * FROM qrySchContacts WHERE SchoolNum = " & lngSchoolNum
End If
End Sub

The Email2Schools function can be called from a command button on an unbound form, or from a toolbar button, or from a macro.

**JudyJones** · 2004-12-22 21:08

Thanks. My Schoolnum is a text field. Would I simply change the line of code "Public glngSchoolNum As Long" to "Public strSchoolNum As String"? I will try this as soon as I return to work after Christmas and let you know how well it works.

**HansV** · 2004-12-22 21:23

You should also change the instruction

Me.RecordSource = _
"SELECT * FROM qrySchContacts WHERE SchoolNum = " & lngSchoolNum

to

Me.RecordSource = _
"SELECT * FROM qrySchContacts WHERE SchoolNum = " & Chr(34) & strSchoolNum & Chr(34)

The Chr(34)'s put quotes around the text value.

**JudyJones** · 2004-12-28 15:32

I must have something wrong so I have attached a stripped down copy of my database. I am trying to email each school's error report to the contact person for that school. I am using the module code I got from your previous reply, which is activated using a macro. It is not separating the report to send one school's errors to each person. While it is being tested, my email address is used for each record but I did supply a different contact name for each school. I am really anxious to make this work because I hope it will help me understand about using recordsets.

**HansV** · 2004-12-28 15:42

In the On Open event of the report, change the line

<code>If strSchoolNum = Null Then</code>

to

<code>If Not (strSchoolNum = "") Then</code>

The first condition is never true. You want to test that strSchoolNum is not an empty string.

**JudyJones** · 2004-12-28 16:14

That small change did the trick. Thanks for your help.

Thread: EOF to stop program loop not working (XP)

Thread Tools