Change Combo to List Box (1 Viewer)

jeninOK

New member
Local time
Today, 02:07
Joined
Jul 17, 2008
Messages
2
Hi everyone,

1. I have a table with addresses (tblAddresses)
<street>
<City>
<State>
<Zip>
etc.

2. A lookup table with all the states (lkupStates)

3. On a search form I have:

-A combo box (cboStates) - this does a SELECT DISTINCT on [States] in tblAddresses.

-A multi-select list box (lstCities) - this displays SELECT DISTINCT on all cities in tblAddresses that = value selected in cboStats

-A button (cmdFilter) that opens a query filtered by the cities selected in lstCities

Question: Can any help me figure out how can I change cboStates to a multi-select list box so the user can select more than one State and see the matching cities in lstCities?

This is going to be a huge table, so effeciency is crucial.

Thank you all very much,
Jenny
 

namliam

The Mailman - AWF VIP
Local time
Today, 09:07
Joined
Aug 11, 2003
Messages
11,553
Well if efficiency is crucial you need to not be doing a Select Distinct on your table twice, becuase that performance is going to bite!!!

Now first simply change your Combo box to a listbox and enable multi select and use below code (from the access help) to fetch all the selected Cities. Then, stick that in the query...
Done!

Code:
Sub AllSelectedData()
    Dim frm As Form, ctl As Control
    Dim varItm As Variant, intI As Integer

    Set frm = Forms!Contacts
    Set ctl = frm!Names
    For Each varItm In ctl.ItemsSelected
        For intI = 0 To ctl.ColumnCount - 1
            Debug.Print ctl.Column(intI, varItm)
        Next intI
        Debug.Print
    Next varItm
End Sub
Good luck!
 

jeninOK

New member
Local time
Today, 02:07
Joined
Jul 17, 2008
Messages
2
You rock -- thanks!!!!!
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom