Form recordsetclone navigation failure!!!

[nspader]

Hello All,

I have developed a program to automatically generate and send reports based on certain criteria.

I have realized that there is a problem with its navigation. I will post the code below but basically the form opens and begins to send emails...like it is supposed to.

What I have noticed is that when it is supposed to navigate to rst.movenext it appears to be skipping records. By skipping I can visually see it going through each record on the form, but only does sendobject on random records.

Any advise on what you can see would be great. I will post code and attach DB to better assist.

Thank you in advance for your help

Nick

Private Sub Form_Load()
On Error GoTo Err_Form_Load
 
Dim rst As Object
Dim bkmk As String
Dim stDocName As String
Dim strSendTo As String
Dim strSubject As String
Dim strMessageText As String
Dim WAIT As Double
 
Set rst = Me.RecordsetClone
 
Me.SetFocus
 
If rst.RecordCount <> 0 Then
    DoCmd.OpenForm "Email_Process", acNormal
    Do Until rst.EOF = True
        bkmk = rst.Bookmark
        Me.Bookmark = bkmk
 
        stDocName = "Request for Updated PO Info EMAIL"
        strSendTo = "[e-mail]" 'rst!EmailAddress
        strSubject = "Wesco Distribution Status Update Report for Branch " & rst!BR
        strMessageText = "To:  " & rst!SupplierName & vbCrLf _
                        & "" & vbCrLf _
                        & "C/O:  " & rst!ContactName & vbCrLf _
                        & "" & vbCrLf _
                        & "Attached is a status update report for specific PO line items." & vbCrLf _
                        & "" & vbCrLf _
                        & "Please review the attached report." & vbCrLf _
                        & "" & vbCrLf _
                        & "PLEASE MAKE CHANGES ON THE REPORT and reply back to this email with the corrected report ATTACHED and any other required information." & vbCrLf _
                        & "" & vbCrLf _
                        & "If you have any questions or concerns, contact information is located on the attached report." & vbCrLf _
                        & "" & vbCrLf _
                        & "Thank you," & vbCrLf _
                        & "" & vbCrLf _
                        & "Wesco Distribution Purchasing Department "
 
        DoCmd.SendObject acSendReport, stDocName, acFormatRTF, strSendTo, , , strSubject, strMessageText, 0
        WAIT = Timer
        While Timer < WAIT + 1
            DoEvents  'do nothing
        Wend
        rst.MoveNext
    Loop
 
    rst.Close
    Set rst = Nothing
    bkMark = ""
    DoCmd.Close acForm, "Email", acSaveYes
    DoCmd.Close acForm, "Email_Process", acSaveYes
    MsgBox "All emails have been sent to Suppliers", 0, "Email Complete"
Else
    MsgBox "There is no Email Address for Supplier's in this Branch Report File", vbExclamation, "No e-mail addresses"
    rst.Close
    Set rst = Nothing
    DoCmd.Close acForm, "Email", acSaveYes
    Exit Sub
End If
 
Exit_Form_Click:
    Exit Sub
Err_Form_Load:
    MsgBox Err.Description
    Resume Exit_Form_Click
End Sub
 

[FishVal]

Hello, Nick.

I would not expect this code to work at all because of the following fragment.

....
        WAIT = Timer
        While Timer < WAIT + 1
            DoEvents  'do nothing
        Wend
        rst.MoveNext
    Loop
 
....
 

Regards,
Fish

[mshmyob]

I would also properly populate your recorset like so before I tried anything

Dim rst As Recordset
' create a recordset and populate
Set rst = Me.RecordsetClone
    rst.MoveLast
    rst.MoveFirst
' then the rest of your code
 

cheers,

[nspader]

Hello, Nick.

I would not expect this code to work at all because of the following fragment.



Regards,
Fish

Fish,

Why would the Timer make the code not work? The point of that was a previous attempt to slow the code down and allow sendobject to run before attempting to run it again.

I did try both taking out the timer portion...need the rst.Movenext to move through the recordset. And I added movelast, movefirst. I previously attempted that with no avail. Also, placing it there...wouldn't it cause an error if there are no records?

Thanx for the help, any other advice is appreciated.

Nick

[nspader]

Also, In the attached example...for branch 7848 for example it runs record 1, then goes to record 5, then goes to record 11, then goes to record 12, then says "complete". Just thought id give you exacts.

[FishVal]

...
Why would the Timer make the code not work? The point of that was a previous attempt to slow the code down and allow sendobject to run before attempting to run it again.
....

Sorry, didn't realize that Timer() is function.

[n8kindt]

i just downloaded your file. if you are referring to the Create Expedite Reports > Email Report it seemed to work fine for me? i stopped the operation midway thru and checked the number of records (since the email_process form was open to view) for each of your combo boxes. 7844 had 4 records--it looped thru the code 4 times. 7848 had 14 records--it looped thru 14 times. 7863 also had 14 records--it looped thru 14 times as well. all loops also ran the sendobject method as well. is this the part u were referring to? u said it was randomly skipping the sendobject method right?

i can also vouch for the email working too lol.

[nspader]

i just downloaded your file. if you are referring to the Create Expedite Reports > Email Report it seemed to work fine for me? i stopped the operation midway thru and checked the number of records (since the email_process form was open to view) for each of your combo boxes. 7844 had 4 records--it looped thru the code 4 times. 7848 had 14 records--it looped thru 14 times. 7863 also had 14 records--it looped thru 14 times as well. all loops also ran the sendobject method as well. is this the part u were referring to? u said it was randomly skipping the sendobject method right?

i can also vouch for the email working too lol.

Yes that is exactly the part I was refering to. If I can ask. What version of access and emailing program are you using. I wonder if that is part of it.

Thanks. I am using access 2000 and outlook 2000, with a dll reference of Outlook 9 object library.

Nick

[n8kindt]

Yes that is exactly the part I was refering to. If I can ask. What version of access and emailing program are you using. I wonder if that is part of it.

Thanks. I am using access 2000 and outlook 2000, with a dll reference of Outlook 9 object library.

Nick

i have Access 2007 and Outlook 2007 with the Microsoft Outlook 12.0 Object Library. i will try it one more time tonight to make sure it worked right so you're not barking up the wrong tree.

however, if i were u, i might consider taking a different approach than using microsoft outlook to send emails. i have a similar program set up and i used .cbo code from this website: http://www.paulsadowski.com/WSH/cdo.htm. if you would like, i could send you the function i created using this. this works very slick for me b/c i use microsoft access runtime 2007 and can set it up as a free independent program without having to install Microsoft Office on every computer to send emails. the only downside i have found about this is that you really have no way to go back and look at the actual email you sent unless u bcc all the emails to yourself.

[nspader]

i have Access 2007 and Outlook 2007 with the Microsoft Outlook 12.0 Object Library. i will try it one more time tonight to make sure it worked right so you're not barking up the wrong tree.

however, if i were u, i might consider taking a different approach than using microsoft outlook to send emails. i have a similar program set up and i used .cbo code from this website: http://www.paulsadowski.com/WSH/cdo.htm. if you would like, i could send you the function i created using this. this works very slick for me b/c i use microsoft access runtime 2007 and can set it up as a free independent program without having to install Microsoft Office on every computer to send emails. the only downside i have found about this is that you really have no way to go back and look at the actual email you sent unless u bcc all the emails to yourself.

Thank you for your help. I ended up doing something similar but still using Outlook. In our company we all use Outlook so it is appropriate to use it. However I realized that Sendobject was the easy way out at first, and we all know about that. ERROR ERROR ERROR. I went back and rewrote the code to call the outlook object and send it that way, with the export of the report just prior to the email message and attachment. It is working perfectly. Clearly it had something to do with the Sendobject (why or what I have no idea). I really appreciate your time on the matter. I will be sending this Program out company wide and want it to be perfect(as can be) before sending it out.

Nick

[n8kindt]

Thank you for your help. I ended up doing something similar but still using Outlook. In our company we all use Outlook so it is appropriate to use it. However I realized that Sendobject was the easy way out at first, and we all know about that. ERROR ERROR ERROR. I went back and rewrote the code to call the outlook object and send it that way, with the export of the report just prior to the email message and attachment. It is working perfectly. Clearly it had something to do with the Sendobject (why or what I have no idea). I really appreciate your time on the matter. I will be sending this Program out company wide and want it to be perfect(as can be) before sending it out.

Nick

Great news! glad i could help. good luck