Run Time Error '91' Problem - Access 2010 VBA

[mick5003]

Hi I have a problem with some code I have hung to a click button on a form. The intention of the code is to save a pdf version of the form, run serval update queries and then email a specific set of people (a vendor and user).

When the code runs i get a Run Time Error '91' - Object Variable or With Block Variable Not set around the following code:

Set qry = db.QueryDefs("qry_pur_new_vend")
qry.Parameters(0) = [Forms]![frm_purchasing_approval]![supplier_name]
Set rst = qry.OpenRecordset

I have defined the 'db' and 'qry' and I do not undertsand why this error is being thrown up especially when I have used this code elsewhere in my database fine. I have included the full code below. Apologies that it is messy but I am no VBA expert and rely on what I can find on the net.

Private Sub Command72_Click()
Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim rst As Recordset
Dim FileNamePDF As String
Dim SetDirectoryPDF As String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Dim OutlookAttach As Outlook.Attachment
DoCmd.SetWarnings False
If Dir("C:\dbtemp", vbDirectory) = "" Then
MkDir ("C:\dbtemp")
Else
'do nothing for the "C:\dbtemp" directory already exists
'MsgBox "C:\dbtemp\ directory already exists"
End If
'Set Directory to save to
SetDirectoryPDF = "C:\dbtemp\"
'Set the filename and save location
FileNamePDF = SetDirectoryPDF & "Quote_Rejection.pdf"
'print to pdf document
DoCmd.OutputTo acOutputForm, "frm_purchasing_approval", acFormatPDF, FileNamePDF, False
Set qry = db.QueryDefs("qry_pur_new_vend")
qry.Parameters(0) = [Forms]![frm_purchasing_approval]![supplier_name]
Set rst = qry.OpenRecordset
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
.BodyFormat = olFormatRichText
.To = rst.Fields("USER_FLD_2").Value
.CC = "x@x.com"
.Subject = "Request for Quotation PN:" & Forms!frm_frm_purchasing_approval!part_no & " Rejected"
.HTMLBody = "Please be aware.  Your provided quotation for the above part number has been rejected.  Please see the attached form and enclosed comments.  Please provide your response to your normal contact"
Dim fso As Object, SourceFolder As Object, SourceFile As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set SourceFolder = fso.GetFolder("C:\dbtemp\")
For Each SourceFile In SourceFolder.Files
.Attachments.Add SourceFolder.Path & "\" & SourceFile.Name
Next
'Send email
'.DeleteAfterSubmit = True 'This would let Outlook send the note without storing it in your sent bin
.Display
Kill "C:\dbtemp\*.*" ' delete all files in the folder
RmDir "C:\dbtemp\" ' delete folder
End With
'MsgBox MailOutLook.Body
DoCmd.OpenQuery "qry_supp_rej", acViewNormal, acAdd
DoCmd.Close
End Sub

[Rawb]

It's most likely one of two things:

• The Query "qry_pur_new_vend" doesn't have any parameters, or
• The Form "frm_purchasing_approval" isn't open when you run the code.

If neither of those are the case, let me know and I'll see what else I can come up with.

[mick5003]

Thanks for the advice.

I have looked at both and neither was the problem, I have however, now resolved the issue. Its a bit embarassing but it turns out, my variable 'db' hadnt been declared. Oops.

Thanks for the reply and assistance.

Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim rst As Recordset
Dim FileNamePDF As String
Dim SetDirectoryPDF As String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Dim OutlookAttach As Outlook.Attachment

[mick5003]

Sorry,

db had been declared but not set

set db = CurrentDb