By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,218 Members | 1,326 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,218 IT Pros & Developers. It's quick & easy.

Passing listbox controls to a report.

P: 2
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])
Feb 7 '08 #1
Share this Question
Share on Google+
4 Replies


jaxjagfan
Expert 100+
P: 254
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub MyButton_Click()
  2. Dim strFilter as String
  3. strFilter = "MyReportField = " & "'" & Me.cboA & "'"
  4. DoCmd.OpenReport "MyReport", acViewPreview, , strFilter
  5. End Sub
  6.  
Change (strFilter = "MyReportField = " & "'" & Me.cboA & "'") to
(strFilter = "MyReportField = " & Me.cboA) if it is a numeric value.
Feb 7 '08 #2

P: 2
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub MyButton_Click()
  2. Dim strFilter as String
  3. strFilter = "MyReportField = " & "'" & Me.cboA & "'"
  4. DoCmd.OpenReport "MyReport", acViewPreview, , strFilter
  5. End Sub
Change (strFilter = "MyReportField = " & "'" & Me.cboA & "'") to
(strFilter = "MyReportField = " & 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
Feb 7 '08 #3

NeoPa
Expert Mod 15k+
P: 31,707
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?
Feb 7 '08 #4

jaxjagfan
Expert 100+
P: 254
----
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 - cmdPreviewSelect - since I want to preview the report before actually printing.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdPreviewSelect_Click()
  2. Dim strFilter as String
  3. If IsNull(Me.cboCustomerID) Then
  4. Msgbox "You Must Select A Customer"
  5. Me.cboCustomerID.SetFocus
  6. Exit Sub
  7. End If
  8. strFilter = "CustomerID = " & Me.cboCustomerID
  9. DoCmd.OpenReport "rptCustomerAll", acViewPreview, , strFilter
  10. End Sub
  11.  
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.
Feb 8 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.