List Boxes in Tandem

[Willburr]

Hi,

I have a database where I need the output from one list box (listbox1) to
determine the values displayed in a second list box (listbox2). listbox1
contains the fields MailCodeID and MailCode. Each MailCode is associated
with one or more FloorCodes, and listbox2 contains the fields FloorCodeID and
FloorCode. When a MailCode is chosen in listbox1, I want the listbox2 to
display only the FloorCodes/FloorCodeIDs associated with the MailCode chosen
in listbox1. I created a query called qryEditFloor which associates all
MailCodeIDs/MailCodes with their associated FloorCodeIDs/FloorCodes.

I need listbox1 to be unbound, so I have set it up the row source property to:

SELECT DISTINCT qryEditFloor.MailCodeID, qryEditFloor.MailCode FROM
qryEditFloor;

Ideally, when listbox2 is populated with the proper FloorCodeIDs/FloorCodes,
the associated data in qryEditFloor will be displayed on my form for
edification.

I cannot find any SQL commands or VBA syntax which will return only the
FloorCodeIDs and FloorCodes in listbox2 associated with the
MailCodeID/MailCode inputted in listbox1. Has anyone ever encountered this
problem and if so, did you find a solution? I really appreciate anyone's
help with this problem!

 

[KARL DEWEY]

Search this newsgroup for Cascading Combos.

 

[Jim Bunton]

for the second list box:
Rowsource = "SELECT whatever FROM aTable WHERE aTable.ColumnName =
[Forms]![FormName].[Listbox1name]"
{if the first list box has more than one column you can add column(n) where
n starts at 0}

for the first list box:
OnClick
me.listbox2.requery

 

[Willburr]

Jim,

It's really starting to work now! Thank you for your input.

I am getting a text box prompting me to "Input Parameter Value",
"Forms!frmEditFloor.me!List30". (List 30 is list box 1 and List 47 is list
box 2). When I input the proper mail code, the correct floors show up in my
list box 2. However, is there a way it will simply read what was clicked in
list box 1 to show the corresponding floor codes in list box 2?

Again, many thanks!

Willburr

for the second list box:
Rowsource = "SELECT whatever FROM aTable WHERE aTable.ColumnName =
[Forms]![FormName].[Listbox1name]"
{if the first list box has more than one column you can add column(n) where
n starts at 0}

for the first list box:
OnClick
me.listbox2.requery

 

[Willburr]

Thanks, Karl!

Willburr

Search this newsgroup for Cascading Combos.

 

[Jim Bunton]

If you're being prompted for a parameter value then there's something wrong
with the relevant sql - probably the 'rowsource' for a combo.

To test it copy it and paste it into the query builder. you generally need
to use square brackets to enclose references to forms contraols. eg
Forms!myform.mycontrol needs to be [Forms]![myform].[mycontrol]

If the rowsource is using a value from a form then the form has to be open
when you test the sal inthe query builder.

It is oftern useful to have two columns in the combo rowsource - eg
SomethingsId, SomethingsName - tou can make the first invisible to the
user - make the combos column count = 2 and the column widths eg 0; 2 then
to refer to the value of the combo box after for example being clicked
forms!formname.comboname.column(0) refers to the valuse of the first column,
and forms!formname.comboname.column(1) to the second column.

List boxes are the same - they can display more than one column and you can
hide columns by giving them 0 width but they can be refered to by using the
relevant columns(n)

Not sure if this helps - I can't quite grab exactly what is is that uyou
want to happen!

Jim,

It's really starting to work now! Thank you for your input.

I am getting a text box prompting me to "Input Parameter Value",
"Forms!frmEditFloor.me!List30". (List 30 is list box 1 and List 47 is
list
box 2). When I input the proper mail code, the correct floors show up in
my
list box 2. However, is there a way it will simply read what was clicked
in
list box 1 to show the corresponding floor codes in list box 2?

Again, many thanks!

Willburr

for the second list box:
Rowsource = "SELECT whatever FROM aTable WHERE aTable.ColumnName =
[Forms]![FormName].[Listbox1name]"
{if the first list box has more than one column you can add column(n)
where
n starts at 0}

for the first list box:
OnClick
me.listbox2.requery

Hi,

I have a database where I need the output from one list box (listbox1)
to
determine the values displayed in a second list box (listbox2).
listbox1
contains the fields MailCodeID and MailCode. Each MailCode is
associated
with one or more FloorCodes, and listbox2 contains the fields
FloorCodeID
and
FloorCode. When a MailCode is chosen in listbox1, I want the listbox2
to
display only the FloorCodes/FloorCodeIDs associated with the MailCode
chosen
in listbox1. I created a query called qryEditFloor which associates
all
MailCodeIDs/MailCodes with their associated FloorCodeIDs/FloorCodes.

I need listbox1 to be unbound, so I have set it up the row source
property
to:

SELECT DISTINCT qryEditFloor.MailCodeID, qryEditFloor.MailCode FROM
qryEditFloor;

Ideally, when listbox2 is populated with the proper
FloorCodeIDs/FloorCodes,
the associated data in qryEditFloor will be displayed on my form for
edification.

I cannot find any SQL commands or VBA syntax which will return only the
FloorCodeIDs and FloorCodes in listbox2 associated with the
MailCodeID/MailCode inputted in listbox1. Has anyone ever encountered
this
problem and if so, did you find a solution? I really appreciate
anyone's
help with this problem!