QueryDefs.OpenRecordset problems

[Ast2TheRgnlMgr]

Hi all,



First-time poster here! Having trouble with this piece of code (mostly because I don't understand it). The problem is I have defined my parameters in frmAccountingDatabaseInput, but when I run the macro, it asks me for parameters for approveparam, dateparam, and typeparam. Shouldn't it already have been defined in frmAccountingDatabaseInput??? Below is the VBA and SQL code I'm using:

VBA

Sub Save_Invoices_Meet_Criteria()


Dim FileName As String
Dim FilePath As String
Dim myStmt As String
Dim Db As DAO.Database
Dim myrs As DAO.Recordset


Set Db = CurrentDb()


Dim qdf As DAO.QueryDef
Set qdf = Db.QueryDefs("qryCreateInvoicesApproved")


qdf!Approveparam = [Forms]![frmAccountingDatabaseInput]![Invoice_approved]
qdf!Dateparam = [Forms]![frmAccountingDatabaseInput]![Combo272]
qdf!Typeparam = [Forms]![frmAccountingDatabaseInput]![Combo274]


Set myrs = qdf.OpenRecordset(dbOpenDynaset)




Do Until myrs.EOF


FileName = Me.reference
foldername = Format(Now(), "YYYY-MM-DD")
FilePath = "C:\Users\company\Desktop\Invoicing Database\Save_Test\" & foldername & "\" & FileName & ".pdf"






DoCmd.OpenReport "RPTInvoice", acViewPreview, FilePath




DoCmd.Close
myrs.MoveNext


Loop


myrs.Close
Set myrs = Nothing


End Sub

SQL

PARAMETERS [Approveparam] Bit, [Dateparam] DateTime, [Typeparam] Text ( 255 );
SELECT DISTINCT tblAccountingDatabase.[reference]
FROM tblAccountingDatabase
WHERE (((tblAccountingDatabase.Invoice_approved)=[Approveparam]) AND ((tblAccountingDatabase.invoice_date)=[Dateparam]) AND ((tblAccountingDatabase.Invoice_Type)=[Typeparam]));

Thanks in advance for any and all help!
- Dwight K. Schrute

[ranman256]

You don't need all this code when you can open it with a simple
docmd.openQuery "qsMyQuery"

[Ast2TheRgnlMgr]

That's basically what I started with. It leads to an error - Too few parameters. Expected 2.

This is what the code looks like with your adjustment. I just need to define myrs as the query result but I can't figure out how to do that:

Sub Save_Invoices_Meet_Criteria()


Dim FileName As String
Dim FilePath As String
Dim myStmt As String
Dim Db As DAO.Database
Dim myrs As DAO.Recordset


Set Db = CurrentDb()




DoCmd.OpenQuery ("qryCreateInvoicesApproved")




Do Until myrs.EOF


FileName = Me.reference
foldername = Format(Now(), "YYYY-MM-DD")
FilePath = "C:\Users\company\Desktop\Invoicing Database\Save_Test\" & foldername & "\" & FileName & ".pdf"




DoCmd.OpenReport "RPTInvoice", acPreviewView, FilePath


DoCmd.Close
myrs.MoveNext


Loop


myrs.Close
Set myrs = Nothing


End Sub