Here is a sample DB using the code I provided. Take a look at the properties for the list box. The only thing I can think of that would cause the error you describe would be the Multi Select property.
The form I created was off a query... Idk how much of a difference that makes.
So its like a form that has the record source of query.... so you open the form and run it runs that query... Basically the listbox is kind of a parameter... As if only open the records that have the selected values. It works great with single selection.. but not multi
I opened your database. When I make selections and clikc the command button, Nothing happens
The results are displayed in the immediate window. The keyboard shortcut to the immediate window is Ctrl+G.
Since you state, "It works great with single selection.. but not multi", and you do not see anything happening in the DB that I uploaded, I will guess there is more code in your click event than what you posted.
Sorry that's not what I meant. It works great single selection without the VBA CODING.The results are displayed in the immediate window. The keyboard shortcut to the immediate window is Ctrl+G.
Since you state, "It works great with single selection.. but not multi", and you do not see anything happening in the DB that I uploaded, I will guess there is more code in your click event than what you posted.
I was hoping to get the resutls in a query.
So basically using the form as a filter.
The code I posted is tested. There is a working example of the code here. If you are still having trouble, you can upload your DB here for analysis. Remove personal data, compact and repair, and zip the file.
I understand you need more than having the results print to the immediate window. You need to accomplish the first step before you can cross the finish line.
MultiSelectTest.zip
I created a new sample DB.
Its a much simpler database than I was working with but the idea is the same.
I want to be able to select multiple states and click the button to run a filtered query
THANKS SO MUCH FOR YOUR HELP! I REALLY DO APPRICIATE IT!
I tried the code on this new sample database and got the same error.
The following code was highlighted:
lngID = Me.List0.Column(0, varSelection)
*NOTE* List box is named List0
I don't know what lngID is... am I suppose to replace it with somehting else?
I did a couple of things. The control button on your form had an embedded macro. I removed the macro by remove the text in the Control Source property of the Control Button.
I created a click event handler in VBA and pasted the code from post #8 in the click event.
I changed the line of code
Dim lngID As Long 'Assumes the value in the first column is a number
and
lngID = Me.List0.Column(0, varSelection)
'Insert code here to do something
Debug.Print lngID
to
Dim strState As String 'Assumes the value in the first column is text
and
strState = Me.List0.Column(0, varSelection)
'Insert code here to do something
Debug.Print strState
respectively. I then changed the property, "Multi Value", for the list box to "Extended".
Now you can view the results in the immediate window by using the keyboard shortcut Ctrl+G.
I noticed that you have two tables in the DB you uploaded. One uses an autonumber as a PK and the other uses text as a PK. So, table1 stores the literal text from your States table. It is OK to do this but you may want to consider using an autonumber field for your States table and using the Autonumber field as you PK. If you do not use the Autonumber field, consider limiting the characters allowed in your text field to two characters or maybe four.
I took the liberty of adding a table to the DB I uploaded here. This table is a table I often use in my DB's. It has three columns: PK, postal abbreviation, and full name. It makes it easier for a User to see the full state name when they are making the selection. After the selection is made, you can display the abbreviation and store the PK in the relative tables.
Actually one of the table didn't have a Primary Key, The only reason I had it created was to allow only selected value as input for a form I did not creat in this sample DB.
I tested the DB you posted and it works. I don't quite understand the results though. If I select NJ & PA in the immediate window I see NJ & Pa. (It opens the VBA coding window and at bottom there is the Immediate winow and I just see NJ & PA)
I want to be able to see the enitre data. For example in the Sample DB I would like for all the customers to show up if they are from NJ & PA if that's my selection in the form.
Well, you need to be able to walk before you can run. A developer will typically retrieve the PK values from a listbox or combobox and then apply the results to a query or VBA procedure or both. This is where the "'Insert code here to do something" will come into play. Now that we have managed the first hurdles, the next steps can be considered.
Post #15 would be one approach.
You need to first decide what the PK is for your states table. Then you need to decide what you want to do with the user input (the data retrieved from the list box).
What is your goal? Are you trying to open another form, open a report, create an email? You can add the results from the listbox to a WHERE clause in a query for instance or add the results to a Where Criteria of a form's filter property.
My PK value is fine. Think of it as a job # ... autonumber is fine.Well, you need to be able to walk before you can run. A developer will typically retrieve the PK values from a listbox or combobox and then apply the results to a query or VBA procedure or both. This is where the "'Insert code here to do something" will come into play. Now that we have managed the first hurdles, the next steps can be considered.
Post #15 would be one approach.
You need to first decide what the PK is for your states table. Then you need to decide what you want to do with the user input (the data retrieved from the list box).
What is your goal? Are you trying to open another form, open a report, create an email? You can add the results from the listbox to a WHERE clause in a query for instance or add the results to a Where Criteria of a form's filter property.
I would like to be able to open a new query I guess (or filtered an existing one). So as you saw in the database there was a query. I would like to be able to pick the values from the form (example the states), and all the customers that belong to those selected states show up (basically there data from the main data table show up).
I have a solution for you. I will post it soon.