Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Runtime 3701 on .MoveNext

Discussion in 'Information Technology' started by TJS, Jul 28, 2006.

  1. TJS

    TJS
    Expand Collapse
    Guest

    I'm trying to create a Bcc list of 400 people instead of sending 400
    individual emails. If I comment out the "DoCmd.SendObject...." I will
    get a runtime error 3701 on the ".MoveNext" command. Makes NO sense to
    me! Help?



    Public Function SendNewsletter()
    Dim MyDb As DAO.Database
    Dim MySet As DAO.Recordset
    Dim email As String

    Set MyDb = CurrentDb()
    Set MySet = MyDb.OpenRecordset("qryNewsletterEmails", dbOpenDynaset)

    With MySet
    Do Until .EOF
    email = email & MySet! & ";"
    ' remove the next line causes a runtime error on .MoveNext
    DoCmd.SendObject .... etc ' <editted for length>
    .MoveNext
    Loop
    End With

    ' want to move the DoCmd here

    MySet.Close
    MyDb.Close
    End Function
     
  2. datAdrenaline

    datAdrenaline
    Expand Collapse
    Guest

    This is MAY happening because VBA may think you are modifying the field
    'email' in your recordset instead of setting the variable email. Also, one
    you use the With, you no longer need to reference the recordset object inside
    of the with block as you do in the line that sets the email (strEmail)
    variable ... (note: I also changed the recordset type to dbOpenSnapshot ..
    which is a readonly type and will probably be a little faster)

    Try this:
    Public Function SendNewsletter()
    Dim MyDb As DAO.Database
    Dim MySet As DAO.Recordset
    Dim strEmail As String

    Set MyDb = CurrentDb()
    Set MySet = MyDb.OpenRecordset("qryNewsletterEmails", dbOpenSnapshot)

    With MySet
    Do Until .EOF
    strEmail = strEmail & ! & ";"
    ' remove the next line causes a runtime error on .MoveNext
    .MoveNext
    Loop
    End With

    DoCmd.SendObject .... etc ' <editted for length>

    End Sub

    Hopefully these slight changes will get the code back on track!!!

    --
    Regards,
    Brent Spaulding
    datAdrenaline


    "TJS" wrote:
    [color=blue]
    > I'm trying to create a Bcc list of 400 people instead of sending 400
    > individual emails. If I comment out the "DoCmd.SendObject...." I will
    > get a runtime error 3701 on the ".MoveNext" command. Makes NO sense to
    > me! Help?
    >
    >
    >
    > Public Function SendNewsletter()
    > Dim MyDb As DAO.Database
    > Dim MySet As DAO.Recordset
    > Dim email As String
    >
    > Set MyDb = CurrentDb()
    > Set MySet = MyDb.OpenRecordset("qryNewsletterEmails", dbOpenDynaset)
    >
    > With MySet
    > Do Until .EOF
    > email = email & MySet![email] & ";"
    > ' remove the next line causes a runtime error on .MoveNext
    > DoCmd.SendObject .... etc ' <editted for length>
    > .MoveNext
    > Loop
    > End With
    >
    > ' want to move the DoCmd here
    >
    > MySet.Close
    > MyDb.Close
    > End Function
    >
    >[/color]
     
  3. TJS

    TJS
    Expand Collapse
    Guest

    This turned out to be bad data in the query that was causing the
    runtime error.
     

Share This Page