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

nEBU is an individual number only used to identify the person so is never used in calculations.

Hi David,

You can skip that variable, as you can use the field-value directly:

    DoCmd.OpenReport "Monthly_Invoice", acViewPreview, , "EBU_Number=" & rsInvoices!EBU_number, acHidden

The advantage of constructing dynamical sql-strings is that the report is independant of any context. Just the (complete) sql-string is transferred to the report.

Constructing becomes very easy with a few standard functions, e.g.

active_sql = "SELECT * FROM rsInvoices" _

           & " WHERE EBU_Number = " & rsInvoices!EBU_Number _

           & " AND StartDate >= " & As_date(Your_Startdate) _

           & " AND EndDate < " & As_date(Your_Enddate + 1) _

           & ... any further conditions ...

The standard function As_date converts a date to an unambigious ISO date, and surrounds the #'s.

An other standard function is As_text. This surrounds the text-value with the necessary single quotes, but also doubles the internal single qoutes.

Also, As_real: the converts the decimal comma to the necessary decimal point.

Let me know if you are interested in this way of working.

In my applications I do not use QueryDefs any more.

Imb.

Answer 5

Hi lmb

I am interested in using sql and think it might work better to export a query to an excell file.

The sql generated from the query designer is

SELECT Players.Surname, Players.[First Name], CCur([Weekly Attendances]![Attendances]*[Session Cost]![Price]) AS Total

FROM [Session Cost], Players INNER JOIN [Weekly Attendances] ON Players.EBU_Number = [Weekly Attendances].EBU_Number

WHERE ((([Weekly Attendances].Month)=[Forms]![frmInvoicesSent]![cboMonth]+[Forms]![frmInvoicesSent]![cboYear]))

ORDER BY Players.Surname;

I send a spreadsheet each month to the Treasurer with a list of the invoices. The parameter is from two combo boxes to produce the criteria in the form Oct24.

Thanks

David