By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,526 Members | 2,189 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,526 IT Pros & Developers. It's quick & easy.

Recordset "Object variable or With Block variable not set"

P: 78
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
  1.  
  2. Private Sub EMAIL_Click()
  3. On Error GoTo Err_EMAIL_Click
  4.  
  5.     DoCmd.OpenForm "Email", acNormal
  6.  
  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
  12.  
  13.     Set rst = Me.Recordset
  14.  
  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 "
  28.  
  29.         DoCmd.SendObject acSendReport, stDocName, acFormatRTF, strSendTo, , , strSubject, strMessageText
  30.  
  31.     rst.MoveNext
  32.  
  33.     Loop
  34.  
  35.     rst.Close
  36.  
  37.     Set rst = Nothing
  38.  
  39.     DoCmd.Close acForm, "Email", acSaveYes
  40.  
  41. Exit_EMAIL_Click:
  42.     Exit Sub
  43.  
  44. Err_EMAIL_Click:
  45.     MsgBox Err.Description
  46.     Resume Exit_EMAIL_Click
  47.  
  48. End Sub
  49.  
  50.  
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
Mar 27 '08 #1
Share this Question
Share on Google+
10 Replies


Expert Mod 2.5K+
P: 2,545
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.

-Stewart
Mar 27 '08 #2

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

P: 23
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.
Mar 28 '08 #4

P: 78
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
Mar 28 '08 #5

P: 78
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

P: 23
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
  2.  
  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
  10.  
  11.  
  12. rst.moveNext
  13. Loop
Mar 28 '08 #7

Expert Mod 2.5K+
P: 2,545
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.

Regards

Stewart
Mar 28 '08 #8

P: 78
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
Mar 31 '08 #9

NeoPa
Expert Mod 15k+
P: 31,399
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

P: 78
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.