Open report and filter on form data

[sparky961]

Me again...

I have a form with a combobox at the top. This shows the customer name and has a hidden customer ID field. There is also a date picker control. Both of these affect a listbox control that shows records associated with sales order line details. The two comboboxes filter the results in the list box via the OnChange (or similarly named) events.

What I'm trying to do now is have a button that opens a report with only the filtered records in the list box. I already have a report that shows the unfiltered record set, and my current code seems to apply the date filter correctly.

The problem is that I'm prompted for the Customer ID (SalesOrder.Customer_ListID) before the report is opened. If I leave it blank, I get no records. If I enter a known customer ID, I get records that are filtered only by date.

Here's the button event:

Private Sub btnPrintPreview_Click()
On Error GoTo Err_btnPrintPreview_Click

    Dim stDocName As String

    stDocName = "Open Sales Orders by Customer"
    strWhereCondition = "SalesOrder.Customer_ListID = '" & Me.cboCustomer.Value & "'" & " AND SalesOrder.DueDate <= DateValue('" & Me.toDateFilter.Value & "')"
    strOpenArgs = ""
    DoCmd.OpenReport stDocName, acPreview, , strWhereCondition, , strOpenArgs

Exit_btnPrintPreview_Click:
    Exit Sub

Err_btnPrintPreview_Click:
    MsgBox Err.Description
    Resume Exit_btnPrintPreview_Click
    
End Sub

As a sidenote, I've seen that forms and such should be named using more common conventions like frmFormName, rptReportName, etc. Can someone tell me if changes to these names will propagate through the code, queries, and forms - or will I have to change these names in multiple places?

Much appreciation...
-Sparky

 

[rainman89]

is list ID a #? im assuming so

you would want

"SalesOrder.Customer_ListID = " & Me.cboCustomer.column(n) &

Where n is your bound column in your combo box

 

[sparky961]

Customer.ListID is a unique string. Example: "2EC0000-1137506644"

SalesOrder.Customer_ListID relates the sales orders back to a customer.

Changing from using the Value property to the Column property doesn't seem to make a difference in whether there's a dialog box displayed or not.

-Sparky

 

[rainman89]

Well if it is prompting you for the value then it seems like it cant find the combo box value... the way you had it should be working.. The combobox is not on a subform or anything is it?

 

[sparky961]

Nope, not on a subform.

Are there some debugging hints someone can give? I've checked and tried a lot of things for this to no avail.

-Sparky

 

[rainman89]

Can you post up a sample of what you are talking about. It might help see what your problem is!

 

[sparky961]

Wow, don't I feel stupid. Thanks for your help, but the problem is my own mistake.

Turns out the WhereCondition for the OpenReport contained an incorrectly-typed field name.

It should read as follows...

strWhereCondition = "SalesOrder.CustomerRef_ListID = '" & Me.cboCustomer.Value & "'" & " AND SalesOrder.DueDate <= DateValue('" & Me.toDateFilter.Value & "')"

I had "SalesOrder.Customer_ListID". It's taken me like all day to find this one.

Again, thanks for trying to help!

-Sparky

 

[rainman89]

Ha glad you got it figured out!