469,964 Members | 1,717 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,964 developers. It's quick & easy.

Recordset "Object variable or With Block variable not set"

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

Expand|Select|Wrap|Line Numbers
  2. Private Sub EMAIL_Click()
  3. On Error GoTo Err_EMAIL_Click
  5.     DoCmd.OpenForm "Email", acNormal
  7.     Dim rst As DAO.Recordset
  8.     Dim stDocName As String
  9.     Dim strSendTo As String
  10.     Dim strSubject As String
  11.     Dim strMessageText As String
  13.     Set rst = Me.Recordset
  15.     Do While rst.EOF = False
  16.         stDocName = "Request for Updated PO Info EMAIL"
  17.         strSendTo = [Report_Request for Updated PO Info EMAIL].Supplier_Email
  18.         strSubject = "Wesco Distribution Shipping Update Report"
  19.         strMessageText = "To:  " & [Report_Request for Updated PO Info EMAIL].Supplier_Contact_Name & vbCrLf _
  20.             & "" & vbCrLf _
  21.             & "Attached is a Shipping Update Report for certain PO numbers." & vbCrLf _
  22.             & "" & vbCrLf _
  23.             & "Please review the attached report and reply back to this email with the requested information." & vbCrLf _
  24.             & "" & vbCrLf _
  25.             & "Thank you," & vbCrLf _
  26.             & "" & vbCrLf _
  27.             & "Wesco Distribution Expediting Department "
  29.         DoCmd.SendObject acSendReport, stDocName, acFormatRTF, strSendTo, , , strSubject, strMessageText
  31.     rst.MoveNext
  33.     Loop
  35.     rst.Close
  37.     Set rst = Nothing
  39.     DoCmd.Close acForm, "Email", acSaveYes
  41. Exit_EMAIL_Click:
  42.     Exit Sub
  44. Err_EMAIL_Click:
  45.     MsgBox Err.Description
  46.     Resume Exit_EMAIL_Click
  48. 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.

Mar 27 '08 #1
10 7000
Stewart Ross
2,545 Expert Mod 2GB
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.Recordsetclone

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

Mar 27 '08 #2
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.

Mar 28 '08 #3
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


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

isn't it. Need change in other statements also.
Mar 28 '08 #4
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.

Mar 28 '08 #5
Also, the str= is pulling from the report not from the recordsource....should i have it pull from the recorsource instead?
Mar 28 '08 #6
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
Expand|Select|Wrap|Line Numbers
  1. Dim bkMark as string
  3. Do while rst.EOF = false
  4.     bkMark = rst.BookMark
  5.     Me.BookMark = bkMark ' this will Synchronize the record
  6. .
  7. .
  8. .
  9. rest of your program
  12. rst.moveNext
  13. Loop
Mar 28 '08 #7
Stewart Ross
2,545 Expert Mod 2GB
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 misunderstandings 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.


Mar 28 '08 #8
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.

Mar 31 '08 #9
32,233 Expert Mod 16PB
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 ;)
Apr 6 '08 #10
Absolutely here is the link

SendObject Loop does not loop through all records!!

Thank you
Apr 7 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

24 posts views Thread by Hung Jung Lu | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.