Populating a combo box's values based on another field's value (data entry)

[Jim]

I'm experimenting with a simple database comprised of three tables:

Table #1 contains a set of buildings. A building's properties consist of an
ID code and a text description of the building.

Table 1: "Building"
ID: Text, Indexed (No duplicates)
Description: Text

Table 1: Data
"A","Building A"
"B","Building B"


Table #2 contains a set of rooms in which "things" reside. A room's
properties consist of the building in which it resides and its room number
designation.

Table 2: "Room"
Building_ID: Text
Number: Text

In Datasheet view, 'Room.Building_ID' is a combo box field whose values are
limited to the set of values obtained via,

SELECT DISTINCT Building.ID FROM Building ORDER BY Building.ID;

Table 2: Data
"A","1"
"A","2"
"B","11"
"B","22"


Table #3 contains the set of "things" that reside in the various rooms. A
thing's properties include its location (building ID and room number) and a
text description of what the thing is.

Table 3: "Thing"
Room_Building_ID: Text
Room_Number: Text
Description: Text

In Datasheet view, 'Thing.Room_Building_ID' is a combo box whose values are
obtained via,

SELECT DISTINCT Room.Building_ID FROM Room ORDER BY Room.Building_ID;

and 'Thing.Room_Number' is a combo box whose values are obtained via,

SELECT DISTINCT Room.Number FROM Room ORDER BY Room.Number;


In Datasheet view, as the user populates the "Thing" table with data, it
would be nice if the SELECT statement for the Thing.Room_Number field--that
last SELECT statement--would take into account the user's choice for the
Thing.Room_Building_ID value/field as it populates the 'Room Number' combo
box with values. For example, if the user enters the value "A" into the
Thing.Room_Building_ID field, the last SELECT statement would populate the
Thing.Room_Number combo box with the values {"1","2"} and not
{"1","2","11","22"} (noting that rooms "11" and "22" are not in building A).
If this is possible, how is it done?

Jim

 

[John Spencer MVP]

Are you using the query datasheet view or a form's datasheet view? If you are
using a query's datasheet view, you are "out of luck" as I don't believe there
is a way to do this (cascading comboboxes) in a query.

If you are using a continuous form, then you can change the row source of the
combobox using code in the form's current event, but you will see the control
in the rows of data that are not the current row blank out. The data is still
there, but it cannot be shown since the combobox does not have the value
available to display.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County