Problem passing a parameter

Question

I produce monthly invoices for a club. I use a query to produce the invoices. I run a procedure from a form, a dataset is opened and the reports are posted a pdfs. At present I write the month criteria in the query and this works OK. I am now trying to use a combo box in the usual way on the form to pass the parameter to the query. When I run the query from the form but when I run the procedure I get the following Error Message

This is the code

Private Sub cmdPrintNew_Click()

Dim rs As DAO.Recordset

Dim strSql As String

Dim cMth As String

Dim cName As String

Dim nEbu As Single

Dim filename As String

Dim filepath As String

Set rsInvoices = CurrentDb.OpenRecordset("Invoice Query")

rsInvoices.MoveFirst 

Do While Not rsInvoices.EOF 

    nEbu = rsInvoices!EBU\_number 

    cName = rsInvoices!Surname & rsInvoices!Initial 

    filepath = "C:\Documents\Invoices\" & cName & ".pdf" 

    Debug.Print filepath 

    DoCmd.OpenReport "Monthly\_Invoice", acViewPreview, , "EBU\_Number=" & nEbu, acHidden 

    DoCmd.OutputTo acOutputReport, "Monthly\_Invoice", acFormatPDF, filepath 

    DoCmd.Close acReport, "Monthly\_Invoice" 

rsInvoices.MoveNext 

Loop 

MsgBox "All invoices Printed" 

End Sub

Answer 1

The following is an example of code which establishes a recordset on the basis of a query which references parameters:

    Dim dbs As DAO.Database

    Dim rst As DAO.Recordset

    Dim qdf As DAO.QueryDef

    Dim prm As DAO.Parameter

    Set dbs = CurrentDb

    Set qdf = dbs.QueryDefs(strQuery)

    For Each prm In qdf.Parameters

        prm = Eval(prm.Name)

    Next prm

    Set rst = qdf.OpenRecordset

In the above strQuery is one of the function's arguments, as which the name of the query is passed into the function.

Answer 2

It's neither. You don't need to change anything in the code. As the code loops through the Parameters collection of the querydef object the Name property of each Parameter object is the name of the parameter. Say a query references two parameters Forms!frmDateRange!txtDateFrom and Forms!frmDateRange!txtDateTo which define a range, those are the names of the parameters. At the first iteration of the loop it might evaluate to 2024-09-10, and at the second iteration to 2024-09-20, which are the dates entered into those two controls in the form. When the query's OpenRecordset method is then called it knows those two dates and returns those rows where the values in a column of DateTime data type fall within the date range.

Answer 3

Dim nEbu As Single

...

Set rsInvoices = CurrentDb.OpenRecordset("Invoice Query")

Hi David,

I wonder, isn't it simpler to use a variable sql-string to construct the rsInvoices recordset, instead of modify the QueryDef and use the modified QueryDef to construct the recordset?

I also wonder why you use a Single datatype for nEbu. The Single type has an inherent rounding inaccuracy, but they are suited for (less precise) calculations. I assume you will never calculate with a nEbu.

Imb.

Answer 4

When using parameter queries in VBA you have to explicitly tell Access to evaluate the parameters or else you get errors.

Look over https://www.devhut.net/ms-access-vba-run-parameter-query-in-vba/ as it shows you how to resolve this issue.

Answer 5

Is the prm.name the Forms etc or the field name?