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

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;

Hi David,

If I look at your sql-string, then the only variable part is the Where-clause, and with that sql-string you want to export the data to an Excel-file.

You could write the sql-string as:

active_sql = "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} ORDER BY Players.Surname"

Now, when you replace the {WHERE} placeholder with the actual value, you have your dynamical sql-string.

On the form you can construct active_where (Me is the actual form):

active_where = " AND [Weekly Attendances].Month = " & As_text(Me!cboMonth + Me!cboYear)

I assume Month and Year are text-fields, but perhaps you have to change this to your real situation.

I start with " AND", because you can have many AND's concatenated, and only the first must be changed to " WHERE".

So: active_where = Replace (active_where, " AND", " WHERE", 1, 1)

And finally: active_sql = Replace (active_sql, "{WHERE}", active_where)

This dynamical string can be used to report to the Treasurer.

My real situation is a little more complex, because all parts of the sql-string are dynamical. This means that also forms (and reports) are dynamical.

Imb.

Answer 5

[Forms]![frmInvoicesSent]![cboMonth]+[Forms]![frmInvoicesSent]![cboYear]

When concatenating values you should normally use the & concatenation operator rather than the + arithmetical operator. The latter should only be used where one of the parameters might be Null, and you want the expression to evaluate to Null. In arithmetical operations Null propagates, i.e the result will always be Null, regardless of the other values in the expression. An example would be:

FirstName & " " & (MiddleName + " " ) & LastName

to suppress the superfluous space if MiddleName is Null.

To export a query's result table to Excel you can wrap the code in a little function like this:

Public Function ExportToExcel(strQuery As String, strPath As String)

On Error GoTo Err\_Handler  

DoCmd.TransferSpreadsheet \_  
TransferType:=acExport, \_  
TableName:=strQuery, \_  
FileName:=strPath  

Exit_Here:
Exit Function

Err_Handler:
MsgBox Err.Description
Resume Exit_Here

End Function

The name of the query and the path to the Excel file would be passed into the function as string expressions.