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

I have included the Select in my code as follows:-

Private Sub cmdExcel_Click()

Dim dbs As DAO.Database 

Dim active\_sql As String 

Set dbs = CurrentDb 

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 AND [Weekly Attendances].Month =  & As\_text(Me!cboMonth + Me!cboYear) ORDER BY Players.Surname" 

DoCmd.OutputTo acOutputQuery, active\_sql, acFormatXLSX, "C:\Documents\Invoices\Invoices Sent.xlsx" 

MsgBox "Excel File sent" 

End Sub

When I run this I get an error message see picture. What have I done wrong.

Thanks

David

Answer 5

Hi David,

The error you get is because you use the active_sql as the name of a query. Alas, there is no such a query.

But there are more remarks: the sql-string does not contain the actual (variable) values but the verbatim control names.

Lets us go back to the beginning.

Of the form frmInvoicesSent you have the comboboxes to select Month and Year (because it is the active form you can use Me).

On that form you have also a command button to trigger the export.

In the Click event of that button you have the code that must be executed.

First you (or I) need to know what datatype is [Weekly Attendances].Month? This must exactly match Me!cboMonth + Me!cboYear. If you need "102024" or "1024" for the current month, the datatype is a String, and thus must be enclosed in single quotes.

To add these quotes, you can use the function As_text (see later).

Suppose the command button is called "Treasurer".

A very simple code could look like:

Sub Treasurer_Click()

    Dim active_sql As String

    Dim active_where As String

    active_sql = "SELECT (Players ... ... ON Players.EBU_number = [Weekly Attendances].EBU_number) {WHERE} ORDER BY Players.Surname"

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

    'now construct the final active_sql

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

    active_sql = Replace(active_sql, "{WHERE}", active_where)

    'For further processing can use the function  ExportToExcel  that Ken supplied.

    ExportToExcel active_sql, "C:\Documents\Invoices\Invoices Sent.xlsx"

End Sub

This is the basis, perhaps there is a typo somewhere.

If you understand how it works, you can nuanciate at will.

Finally the function As_text:

Function As_text(cur_text As String) As String 

    As_text = "'" & Replace(cur_text, "'", "''") & "'" 

End Function 

Place both functions As_text and ExportToExcel in a general module.

Success!

Imb.