Recordset "Object variable or With Block variable not set"

[nspader]

I want to start out saying I am a novice code writer. I am trying to send a report via email based on each supplier. The code below is what I am using. I need to base recordset on Form, report generated is based on the supplier number on that form so as to only create a single report.

This is Access 2000, with Windows 2000

Private Sub EMAIL_Click()
On Error GoTo Err_EMAIL_Click

    DoCmd.OpenForm "Email", acNormal
    
    Dim rst As DAO.Recordset
    Dim stDocName As String
    Dim strSendTo As String
    Dim strSubject As String
    Dim strMessageText As String
    
    Set rst = Me.Recordset
    
    Do While rst.EOF = False
        stDocName = "Request for Updated PO Info EMAIL"
        strSendTo = [Report_Request for Updated PO Info EMAIL].Supplier_Email
        strSubject = "Wesco Distribution Shipping Update Report"
        strMessageText = "To:  " & [Report_Request for Updated PO Info EMAIL].Supplier_Contact_Name & vbCrLf _
            & "" & vbCrLf _
            & "Attached is a Shipping Update Report for certain PO numbers." & vbCrLf _
            & "" & vbCrLf _
            & "Please review the attached report and reply back to this email with the requested information." & vbCrLf _
            & "" & vbCrLf _
            & "Thank you," & vbCrLf _
            & "" & vbCrLf _
            & "Wesco Distribution Expediting Department "
    
        DoCmd.SendObject acSendReport, stDocName, acFormatRTF, strSendTo, , , strSubject, strMessageText
    
    rst.MoveNext
    
    Loop
    
    rst.Close
    
    Set rst = Nothing
    
    DoCmd.Close acForm, "Email", acSaveYes
    
Exit_EMAIL_Click:
    Exit Sub

Err_EMAIL_Click:
    MsgBox Err.Description
    Resume Exit_EMAIL_Click
    
End Sub

It is throwing error "Object variable or With Block variable not set"

The error happens on "Do While rst.EOF = False"

Any Help here is greatly appreaciated.

NICK

[Stewart Ross]

Hi Nick. I think your problem is at line 12, which I guess should be referring to the RecordsetClone property to copy the underlying recordset from your form:

Set rst = Me.Recordsetclo ne

The object error is arising because there is no valid current recordset in object variable rst.

-Stewart

[nspader]

Thank you.

I made that change and now it is throwing error stating "You entered and expression that has an invalid reference to the RecordsetClone Property"

**Note: The form I am using has a subform located in it. I dont know if this is part of the problem.

Any help is greatly appreciated.

Nick

[aprpillai]

Pardon me if I am wrong, I don't see anywhere in your code you are referencing the rst object for picking the field values.

For example :

strSendTo = [Report_Request for Updated PO Info EMAIL].Supplier_Email

must be written as

strSendTo = rst![Report_Request for Updated PO Info EMAIL].Supplier_Email

or

With rst
strSendTo = .[Report_Request for Updated PO Info EMAIL].Supplier_Email
.
.
End with

isn't it. Need change in other statements also.

[nspader]

I am not sure if that is true.

However, it stops before it gets to that point. The error occurs at rst = ...

I believe that code works in order, please correct if I am wrong.

Nick

[nspader]

Also, the str= is pulling from the report not from the recordsource... .should i have it pull from the recorsource instead?

[aprpillai]

If you are trying to move the record on the Form one by one with the use of the Recordsetclone and use the Cureent record field on the Form as criteria for the Report to send the Mail then you have to Synchronize the BookMark of the Recordsetclone with the Form's Bookmark. Try the following

Dim bkMark as string

Do while rst.EOF = false
    bkMark = rst.BookMark
    Me.BookMark = bkMark ' this will Synchronize the record
.
.
.
rest of your program


rst.moveNext
Loop

[Stewart Ross]

Hi again. The more I look at your code the more I [font=Verdana][size=2]think that you should reconsider what it is you are trying to achieve and start again. Aprpillai is right that you are not actually referring to the rst elements (although as you have said you are getting an error message before any of these lines is reached). I think it is actually report elements you may be referring to in the parts that Aprpillai has identified. There are so many difficulties and apparent misunderstandin gs I think it is not practicable to resolve them this way.[/size][/font]

You are opening a form called e-mail at the top of your code. In what form is your current code located? I suspect you are getting the recordset clone error because the 'me' property is referring to whatever form the subroutine is in, not to your e-mail form at all. Is your current form some form of switchboard that is unbound? This would at least explain why recordsetclone is causing an error.

Rather than go line by line into the many errors that are now apparent, please tell us in the broad what it is you are doing, which queries are involved in doing so, what report you run, how you propose to filter it for each person you are e-mailing, what format you propose to e-mail the report in (snapshot, rtf, Excel etc), and how you get the e-mail addresses for each person. Maybe then we can give better advice that will actually help you to achieve your goal.

Regards

Stewart

[nspader]

Thank you for that Infomation.

I did infact have it posted off the wrong form. I have changed it to perform on form load of the correct form and it works.

Now, I have a different problem. Now it only loops through 3 records, not all records in the recordset.

I am going to post under new heading. Thank you for your help. Please look for new heading if you know anything about sendobject and loops.

Nick

[NeoPa]

Rather than them looking for it Nick, you could post a link to it in here and that way get a higher likelihood of it being seen by any already interested parties ;)

[nspader]

Absolutely here is the link

SendObject Loop does not loop through all records!!

Thank you