Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    EOF to stop program loop not working (XP)

    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
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

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

    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?

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

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

    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.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

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

    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?

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

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

    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.

  9. #9
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

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

    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.

  11. #11
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

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

    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.

  13. #13
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

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

    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.

  15. #15
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    That small change did the trick. Thanks for your help.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •