Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-16-2008, 09:14 PM   #1
jeninOK
Registered User
 
Join Date: Jul 2008
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
jeninOK is on a distinguished road
Change Combo to List Box

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

jeninOK is offline   Reply With Quote
Old 07-16-2008, 10:58 PM   #2
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,407
Thanks: 0
Thanked 801 Times in 788 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
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!
__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Old 07-17-2008, 01:23 PM   #3
jeninOK
Registered User
 
Join Date: Jul 2008
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
jeninOK is on a distinguished road
You rock -- thanks!!!!!

jeninOK is offline   Reply With Quote
Reply

Tags
cascade , combo , filter , list box

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Check Box and Combo Box Blank ErikRP Forms 9 03-25-2008 11:09 AM
[SOLVED] Combo Box to Sort Already Sorted List Box trenton.bachelo General 2 06-30-2005 03:46 PM
[SOLVED] How do I issue items from a multi-level inventory? ltl Forms 5 01-27-2004 12:32 PM
Problem closing a form with a combo box linked to a list box. Essendon Forms 1 04-10-2002 02:37 AM
Combo box:info in drop down list appears different in box once selected. Why? Fix? SomeGuy Forms 2 09-27-2000 08:03 AM




All times are GMT -8. The time now is 02:17 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World