Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Limit Selection in a combo box

Posted on 1998-08-25
5
Medium Priority
?
387 Views
Last Modified: 2011-10-03
In my form, I wish to limit the selection in a combo box based on a value in another another combo box. The source of the data is a table which has two fields, SalesProv and SalesCity. What I want to happen is, when the user selects an entry in the SalesProv combo box, the selection of cities in the SalesCity combo box is limited to the cities belonging to that province.

Any ideas?

Thanks!
0
Comment
Question by:axxess
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 

Accepted Solution

by:
JCreson earned 120 total points
ID: 1960299
I would try this:

    Since the first combo box contains the province, you want to use the AfterUpdate event to load the second combo box on the fly.  Use code to set the Row Source of the second combo box to a  query or SQL Statement, whichever way you feel more comfortable with, ("SELECT SalesCity from <tablename> WHERE SalesProv = '" & Combo1.Text & "') using the Text property of the first box as a parameter. Then requery the second box to load it up with the city data.  I think you'll get the idea...

0
 
LVL 1

Expert Comment

by:LostSoul
ID: 1960300
I always use the "On click" event rather that the "After Update" as it implies the action is triggered only after the data is saved to the table.  Anyways...

While JCreson's solution is acceptable, you must prepare for all contingencies.  For example, the user might want to put the city in before the province.  This should be expected as it is the "usual" order of address entry.  To allow for this, as users hate being told how to do things by a computer, is to allow the list of cities to contain ALL cities, then have it filtered once a province is selected so, for example, if the user picks "Toronto" then picks "Newfoundland" the "Toronto" selection is changed to a null.


0
 
LVL 5

Expert Comment

by:tuvi
ID: 1960301
Also, prepare for users who enter new province and new city that are not in the list.
0
 

Expert Comment

by:JCreson
ID: 1960302
Sorry folks...was only looking at the basic problem at hand, not application wide. You both are correct and I thank you for commenting so quickly.
0
 

Author Comment

by:axxess
ID: 1960303
JC: Thanks, but I'm rather new to VBA. Appreciate details of your solution re "using code to set the RowSource...requery to load City data..". Thanks as well for the helpful comments(Tuvi,LostSoul).
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question