Result of one combo box, affecting results of another...

[Goobz]

Just out of curiousity...

Is there a way to have what the user selects in one combo box on a
form, affect what is available for the user on another box!?

For instance... Box A has 8 different addresses.. Within those
addresses there are 12 different building ID locations... Instead of
having all 12 building locations for each address always appear in the
combo box, If the user picks building 4, is there a way to just have
the building ID options for building 4 showing in combo box B?

Charles!

 

[Jeff Boyce]

Use your favorite search engine.

Use "Cascading Combo boxes" for search terms.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Roger Converse]

Charles,

You can probably find what you are looking for here:

http://forums.techguy.org/business-applications/667999-solved-access-query-listbox-question.html

HTH
Thanks,
Roger

 

[Ken Sheridan]

Charles:

You might also like to take a look at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


which demonstrates various ways of using correlated combo boxes with
hierarchical data in both single and continuous form view while maintaining
normalized table structures. The units in the demo are the local
administrative areas of County, District and Parish in my area, but the
principles are the same for any similar hierarchy.

Ken Sheridan
Stafford, England

 

[Goobz]

I've tried all of the items shown above. However, the problem I am
having with all of them, is trying to integrate them into an already
existing database, using and storing existing fields...

I have tried the bound, un-bound, all of them, and just can't seem to
figure out how to get it to work in conjunction with my database. I
obviously am doing something wrong!?

 

[Goobz]

My problem is this...


I can get 99.9% of these examples all installed, and the whole nine
yards. Where my problem comes in at, is when I have to "bind" one of
the combo boxes, that's where my problem comes in at...

The information I am trying to bind is on a table "DN.911ID". When
I try and use the examples that I have found and downloaded, it won't
let me "bind" it to the example colum that is being used, so I have no
way of getting any of the information imported over.

I have a table "Building" which is being linked to "911 Zones", and
it's within those tables that the infromation I need to use on 99% of
these examples (ie: 911 Zones.BuildingID", which has a relationship
with "Building.NameID".

I wouldn't mind saving the information under the "DN" table, but I
guess if I can get it to Data Source to any correct table, I can deal
with it from there...

Thanx, Charles!

 

[Ken Sheridan]

Charles:

The basic principle behind my demo is that only the lowest level in the
hierarchy is stored in the referencing table. The table is thus normalized
as it does not contain columns for district or parish, which are known from
the parish, so it would be redundant to have columns for these in Locations.
In my case its ParishID which is stored in the Locations table, and is thus
the ControlSource of the bound combo box cboParish on frmLocations_Single.
The district and county combo boxes are unbound.

I'm not sure I've correctly interpreted your last post, but I assume that in
your case the zone is the higher level of the hierarchy and the building the
lower level, i.e. for each zone there are a number of buildings. I take it
that the referencing table (DN?) in each row of which you have a foreign key
column (911ID?) is the one on which your form is based, so this would have an
unbound combo box for the zone and a bound combo box (with 911ID as its
ControlSource property) for the building.

Ken Sheridan
Stafford, England

 

[Goobz]

Yes and no... Here's what I have going on... I'm working on massivly
cleaning up a database that the person before me had put together,
which looks basically like an excel spreadsheet on steroids. I've
already split the database, which users used to work in directly, and
have to get out of it so the next could get in it, and have everyone
working off the front end mde file... Here's the layout for my little
scenario I am working on right now...

Table.DN - This is the table that the values are stored in for
specific people.
Form.DN Update - This is the form that is being used, pulling and
storing values from Table.DN.
Form.DN Update Subform - This is a poorly built subform, which pulls
and stores values from Table.TN

Here's what I am trying to do... I will give you the breakdown...

Form.DN - Contains the Combo Boxes.
cboBuilding - This box has locations which are pulled from
Table.Building.911ID, and stores Table.Building.911Zone
cbo911Zone - This will use the value from cboBuilding to bring up
values based on the result.
cbo911Zone is the value that needs to be stored in Table.DN under that
specific persons information.


Value.Building.911ID =Building 1, Building 2, Building 4, Building 21,
Mill Creek, etc.
Value.Building.911Zone=This has approx. 5 values for each building,
which is the selection that will be returned depending on the result
from the building.
Value.DN.911ID=This is the field that I have the results stored at for
each specific individual user.

Does this help at all to understand what it is I am trying to do!?

 

[Ken Sheridan]

In that case cbo911Zone is a bound control and is correlated with the unbound
control cboBuilding by referencing it as a parameter in the usual way. In
the form's Current event procedure, if the form's NewRecord property is False
look up the building for the current 911 zone and assign the value to
cboBuilding, else assign a Null to cboBuilding.

Ken Sheridan
Stafford, England