Use a multi-select list box to set a Form search argument

[sumdumgai]

Hello again. Need some help with a search form that I've created. The form has several controls like name, state, city, etc. which the user fills in to search a table.
As it is, only one name, state, and city, etc. can be searched at a time. Is it possible to bring up a listbox, on the state control's down click event, that contains all of the state codes so that the user can then select one or more states to search? If it can be done, could some sample code be given? Thanks.

 

[theDBguy]

Hi. Have a look at some of the samples here. Hopefully, you'll find something useful.

 

[Deleted member 147267]

on the state control's down click event

This is a combo? Why have a combo of states and a listbox of states when you can just have a listbox?? If user only chooses one, what then? How to decide which control's value(s) to use? You must be meaning the click event of a textbox, not combo.

 

[sumdumgai]

Sorry for the naming errors. I'm new to forms. On my form, I have several fields that the user uses to enter search criteria. In the Form's Name Control, they enter a name. In the State Control, they enter a tow character state id. Then the Search button is clicked and the query that's built produces a list of records that match the criteria. Instead of the user entering a two character state id in the State control, I'd like to have a list box pop up when the focus is on the State control. The user could then select multiple states as criteria. Thanks.

 

[Deleted member 147267]

Say you have a button to get the results when all the controls are filled in. Which control value will you use in that query when the button is clicked - the combo or the listbox? What if a listbox selection duplicates a combo selection? How will you know the user settled for the combo value chosen, or made selections from the listbox? It can be done, but why complicate this? Is there a reason why you wouldn't just use the listbox?

 

[sumdumgai]

I want to give the user the option to either type in the two character state id into the Forms 'State' control field, or click on a down arrow on the control to select one or more states from a pop up dialogue.

 

[June7]

A combobox cannot be multi-select for search criteria. Has to be a listbox and requires VBA code that loops through the selected items and builds an array list. Example http://allenbrowne.com/ser-50.html

So no, a combobox cannot have a 'popup' multi-select list.

 

[Deleted member 147267]

As noted, combos don't have multi select option (unless their rowsource is a multi value field and you don't want to go there). OK, how about this...

You use a textbox for single values and have a command button. Nearby label provides clues. Click button and any chosen value in that textbox is cleared and disabled and an invisible listbox appears for multi select. Label caption changes accordingly. Your search button code picks either listbox selected items if textbox is null/disabled, or the textbox value if the list is invisible. If you click the listbox button again, list is cleared & disappears and textbox is re-enabled. You can cycle the listbox or textbox on button click as easily as
Me.List1.Visible = Not Me.List1.Visible
or ...
you can show listbox on separate form after clearing the textbox and pass the selected items back. Again, you have to eliminate values from one or the other.

or you can forget the textbox and either pick one value from a listbox or multiple - problem solved. Aside from not dropping down, I fail to see the issue of only having the choice to pick one item or several items from a standard listbox.

 

[essaytee]

What about in the AfterUpdate event of the ComboBox you assign the value (State Code) to a TextBox. Enter as many as you like. Then with a bit of string manipulation, you will be able to compile an SQL IN operator.

Make it fancier still, using the same ComboBox, if entering the same State Code twice, the assumption is that it's a deletion of the State Code from the TextBox.

Food for thought.

 

[Deleted member 147267]

Without getting too fancy (only 11 principal lines of code)

textbox disabled - don't use

textbox enabled, don't use list

With Me
  .List1.Visible = Not Me.List1.Visible
  .Text4.Enabled = Not .Text4.Enabled
End With
If Me.List1.Visible Then
  Me.Command3.Caption = "Click For Item Only"
Else
  Me.Command3.Caption = "Click For List Only"
End If

 

[sumdumgai]

Thanks to everyone. I found a Youtube video that seems to address what I want. For those interested, here's url:

https://www.youtube.com/watch?v=aFFApOpes50