Passing listbox controls to a report.

[AccessUser]

I have a form with a listbox control named A.

I want to open a report based on the value selected in the drop down A( a query like SELECT fields FROM Table when table.field= value from A)

How do I pass a list value to a query in the report( is there a way like there is for passing textbox values - like [forms]![formname]![fieldname])

[jaxjagfan]

I have a form with a listbox control named A.

I want to open a report based on the value selected in the drop down A( a query like SELECT fields FROM Table when table.field= value from A)

How do I pass a list value to a query in the report( is there a way like there is for passing textbox values - like [forms]![formname]![fieldname])

Combo box is a droplist - only one choice can be made
Listbox - Is a list of one or more items any number of which can be selected depending on settings.

"A" is not a good choice for a control name - cboA would be better and cboCustomerID would be best if the combo was tied to the CustomerID.

If you are selecting values in a combobox (drop-down list) and clicking a button then something like this (Assuming your report would normally print all records but you are just wanting to filter it based on the selection):

This code filters a string value - ie customer name.

Private Sub MyButton_Click()
Dim strFilter as String
strFilter = "MyReportField = " & "'" & Me.cboA & "'"
DoCmd.OpenReport "MyReport", acViewPreview, , strFilter
End Sub

Change (strFilter = "MyReportFi eld = " & "'" & Me.cboA & "'") to
(strFilter = "MyReportFi eld = " & Me.cboA) if it is a numeric value.

[AccessUser]

Combo box is a droplist - only one choice can be made
Listbox - Is a list of one or more items any number of which can be selected depending on settings.

"A" is not a good choice for a control name - cboA would be better and cboCustomerID would be best if the combo was tied to the CustomerID.

If you are selecting values in a combobox (drop-down list) and clicking a button then something like this (Assuming your report would normally print all records but you are just wanting to filter it based on the selection):

This code filters a string value - ie customer name.

Private Sub MyButton_Click()
Dim strFilter as String
strFilter = "MyReportField = " & "'" & Me.cboA & "'"
DoCmd.OpenReport "MyReport", acViewPreview, , strFilter
End Sub

Change (strFilter = "MyReportFi eld = " & "'" & Me.cboA & "'") to
(strFilter = "MyReportFi eld = " & Me.cboA) if it is a numeric value.

----
Thanks for the suggestion. What you said does make sense:
Here is what happens-
I want to display a couple of fields from a record in the report, so the CustomerID as in your example would be used to select which record,
So the query I write is
'Select customername from customer WHERE CustomerID = Me.[OpenArgs]"

What happens is when I click the OpenReport button on the form, a pop up comes asking me for the value that would go in Me.[OpenArgs].
I am sure I am making some mistake in accepting the value from the form to the query to generate report data.
Can you figure out what is going wrong?
Thanks for your help

[NeoPa]

I'm not sure where you got Me.OpenArgs from. What Jax was saying was simply to use the name of the ComboBox control in the WHERE clause (or filter string when passing to a report). cboA is the name in Jax's code. He allows for it being a string by enclosing the value returned in single-quotes (').
Does that make more sense now?

[jaxjagfan]

----
Thanks for the suggestion. What you said does make sense:
Here is what happens-
I want to display a couple of fields from a record in the report, so the CustomerID as in your example would be used to select which record,
So the query I write is
'Select customername from customer WHERE CustomerID = Me.[OpenArgs]"

What happens is when I click the OpenReport button on the form, a pop up comes asking me for the value that would go in Me.[OpenArgs].
I am sure I am making some mistake in accepting the value from the form to the query to generate report data.
Can you figure out what is going wrong?
Thanks for your help

1. Change your report so that is opens to show all records by default. We will filter it based on selections from a combobox - no need to pass a SQL statement if you don't have to. I will call it - rptCustomerAll.
2. You wanted to select (per my example) a CustomerID so I am going to make sure my combobox name reflects the field its tied to - cboCustomerID.
3. Now I create a button - cmdPreviewSelec t - since I want to preview the report before actually printing.

Private Sub cmdPreviewSelect_Click()
Dim strFilter as String
If IsNull(Me.cboCustomerID) Then
Msgbox "You Must Select A Customer"
Me.cboCustomerID.SetFocus
Exit Sub
End If
strFilter = "CustomerID = " & Me.cboCustomerID
DoCmd.OpenReport "rptCustomerAll", acViewPreview, , strFilter
End Sub

In the code I want to verifiy a CustomerID was selected in the combo - if not, tell the user, set focus on the combo and exit the print preview.