asked on

Access Open Report with SQL Parameter

I'd like to be able to write a function which will print an invoice (a report) for each customer who had placed an order in particular month/year. In order to do this, I created two queries:

a SQL DISTINCT query that returns the CustomerID for a particular month and (requires month and year)
a regular query for the report (requires month, year, customer id)

The only issue I have is that when the report opens I am asked for the month, year and customer id and I do not know how pass these query parameters from the function. As you can see, my intention is to put the open report command into the loop, that's why I need to know how to pass the parameters.

To test the query, try the following parameters: month=2, year=2017 (returns two customers), - customerID = 51

'retrieves all deliveries of a particular month and year
'and creates an invoice
Sub PrintAllInvoices(OrderMonth As Integer, OrderYear As Integer, Optional CustomerID As Integer)
    
    Dim DB                                  As Database
    Set DB = CurrentDb

    Dim dbs                                 As DAO.Database
    Dim qdf                                 As DAO.QueryDef
    Dim prm                                 As DAO.Parameter
    Dim rst                                 As DAO.Recordset
    
    'array variable - contains all customer IDs
    Dim arrResults()                        As Variant
    Dim i                                   As Integer
    Dim intCount                            As Integer
    
    
    'get customers for this month and year - using SQL Distinct CustomerID
    Set qdf = CurrentDb.QueryDefs("qyrOrdersByMonth_UniqueCustomers")
    qdf.Parameters("@ordermonth").Value = OrderMonth
    qdf.Parameters("@orderyear").Value = OrderYear
    Set rst = qdf.OpenRecordset
    intCount = rst.RecordCount
    
    DoCmd.OpenReport "Invoice", acViewPreview, , , acDialog
    
    With rst
        If Not .EOF Then
        Do Until .EOF
           Debug.Print .Fields("CustomerID")
           'DoCmd.OpenReport "Invoice", acViewPreview, , , acDialog
           'DoCmd.PrintOut acPrintAll
           'DoCmd.Close acReport, "Invoice"
            .MoveNext
        Loop
        End If
        .Close
    End With
    
    
    'close this connection
    'rst.Close
    Set rst = Nothing
    DB.Close
    Set DB = Nothing
End Sub

Open in new window

Test_DB.accdb

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Massimo Scola

ASKER

The report will run - but I am asked for the month, year and customer ID; the parameters of the query.

As I have a custom ribbon, I am going to add a button which open a form asking the user for the month and year he wants to print the invoices for.

Your approach sounds interesting but I'm not sure how to implement it.

Are you suggesting that I put my function into the report (instead a module)?
The query still requires three parameters and, please correct me if I'm wrong, the report's filter can only accept 1 parameter? How would the filter work?

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Massimo Scola

ASKER

So I put everything into the form's onLoad event?
I hope my intention was clear: to batch print all invoices for a particular month/year.

How does the form know which variables to 'start' with?
Do I need to pass two arguments so it known which month it should start?
Then start the function which populates the textboxes?

Private Sub Report_Load()
Call GetData(?,?)
End Sub

'retrieves all deliveries of a particular month and year
'and creates an invoice
Sub GetData(OrderMonth As Integer, OrderYear As Integer, Optional CustomerID As Integer)
    
    Dim DB                                  As Database
    Set DB = CurrentDb

    Dim dbs                                 As DAO.Database
    Dim qdf                                 As DAO.QueryDef
    Dim prm                                 As DAO.Parameter
    Dim rst                                 As DAO.Recordset
    
    'array variable - contains all customer IDs
    Dim arrResults()                        As Variant
    Dim i                                   As Integer
    Dim intCount                            As Integer
    
    
    'get customers for this month and year - using SQL Distinct CustomerID
    Set qdf = CurrentDb.QueryDefs("qyrOrdersByMonth_UniqueCustomers")
    qdf.Parameters("@ordermonth").Value = OrderMonth
    qdf.Parameters("@orderyear").Value = OrderYear
    Set rst = qdf.OpenRecordset
    intCount = rst.RecordCount
    
    DoCmd.OpenReport "Invoice", acViewPreview, , acDialog
    
    With rst
        If Not .EOF Then
        Do Until .EOF
           Debug.Print .Fields("CustomerID")
           
            Dim strFIlter
            strFIlter = "[OrderMonth]=" & Me.txtMonth.Value & " and [OrderYear]= " & Me.txtYear.Value & " and CustomerID=" & Me.txtCustomerID.Value
            DoCmd.OpenReport "Invoice", acViewPreview, , strFIlter

           
           'DoCmd.OpenReport "Invoice", acViewPreview, , , acDialog
           'DoCmd.PrintOut acPrintAll
           'DoCmd.Close acReport, "Invoice"
            .MoveNext
        Loop
        End If
        .Close
    End With
    
    
    'close this connection
    'rst.Close
    Set rst = Nothing
    DB.Close
    Set DB = Nothing
End Sub

Open in new window

Rey Obrero (Capricorn1)

no. do not use the Load event of the form for this purpose

use a command button click event to call the codes

SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Massimo Scola

ASKER

OK, I have created a form with the two comboboxes and I put them into tempvar variables.

The reason you get prompted multiple times is because Access reruns the query again if you first open in preview mode and then print.

The query requires three parameters. I assumed the [OrderMonth] bit from Rey's code was for the query parameters, but it isn't.
I am still being prompted for the parameters. How do I put the tempvars into the parameter box?

I have added the following code:

Private Sub Command5_Click()
TempVars("InvoiceMonth").Value = Me.Combo1.Value
TempVars("InvoiceYear").Value = Me.Combo3.Value

Call PrintAllInvoices(Me.Combo1.Value, Me.Combo3.Value)
End Sub

'retrieves all deliveries of a particular month and year
'and creates an invoice
Sub PrintAllInvoices(OrderMonth As Integer, OrderYear As Integer, Optional CustomerID As Integer)
    
    Dim DB                                  As Database
    Set DB = CurrentDb

    Dim dbs                                 As DAO.Database
    Dim qdf                                 As DAO.QueryDef
    Dim prm                                 As DAO.Parameter
    Dim rst                                 As DAO.Recordset
    
    'array variable - contains all customer IDs
    Dim arrResults()                        As Variant
    Dim i                                   As Integer
    Dim intCount                            As Integer
    
    
    'get customers for this month and year - using SQL Distinct CustomerID
    Set qdf = CurrentDb.QueryDefs("qyrOrdersByMonth_UniqueCustomers")
    qdf.Parameters("@ordermonth").Value = OrderMonth
    qdf.Parameters("@orderyear").Value = OrderYear
    Set rst = qdf.OpenRecordset
    intCount = rst.RecordCount
    
    
    
    With rst
        If Not .EOF Then
        Do Until .EOF
            CustomerID = .Fields("CustomerID")
           
            Dim strFIlter
            strFIlter = "[OrderMonth]=" & TempVars("InvoiceMonth") & " and [OrderYear]= " & TempVars("InvoiceYear") & " and CustomerID=" & CustomerID

           DoCmd.OpenReport "Invoice", acViewPreview, , strFIlter
            .MoveNext
        Loop
        End If
        .Close
    End With
    
    
    'close this connection
    'rst.Close
    Set rst = Nothing
    DB.Close
    Set DB = Nothing
End Sub

Open in new window

Rey Obrero (Capricorn1)

open the report in design view and remove the values in the criteria of the record source of the report

also, what is the row source of the combo box for month, the bound column should be numeric 1 for jan, 2 for feb etc

Massimo Scola

ASKER

ok - it seems to work.

Thanks for your help and thanks Pat for suggesting that I use tempvar.
I always avoided these because I wrongly assumed that, like global variables, they should be used sparingly.

Dale Fye

I never really used global variables, preferring to have a hidden form where i stored all my 'globsl variables' in text boxes.

The problem with global variables, or local ones for that matter, is that they will lose their values when an unhandled error is encountered, whereas form controls and tempvars do not.

Since the advent of tempvars, i use them almost exclusively for values that need to persist outside of a single form.

Massimo Scola

ASKER

Fully agree with the tempvars - I've read a bit about them online.

Thanks for your help and patience. Not only where your suggestions very helpful, I have also learned some new techniques.
I hope my boss doesn't want me to make big changes to this project (which I have almost finished.)

Have a nice weekend.