Filter Combo box with Combo box

[cscotty]

Hello All. I've done my search and have not found the same situation that I
have, so I am hoping you can help me.

I have a form with three text boxes: eNumber, Name, Site

I would like to convert all three text boxes into combo boxes. When I select
the eNumber from the combo box, I would like it to filter the results in the
Name combo box and display the applicable records. Then I would like to
select the name from the Name combo box and have it filter the Site box, and
so on.

Is there a way to do this? Please help.

Thanks,
Scott

 

[bismuth83]

The rowsource of Site should have the Name value as part of its
criteria, and the Name rowsource should use the eNumber value in its
criteria. In the AfterUpdate() event of eNumber, use Name.Requery to
reset the Name rowsource, and in the AfterUpdate() event of Name, use
Site.Requery.

 

[cscotty]

The rowsource of Site should have the Name value as part of its
criteria, and the Name rowsource should use the eNumber value in its
criteria. In the AfterUpdate() event of eNumber, use Name.Requery to
reset the Name rowsource, and in the AfterUpdate() event of Name, use
Site.Requery.

Hi Bis,

Thanks for the info. Is it possible that you can give me an example of how
to write the code. Here are my tables:

tblMain:
eNumber, Name, Site

tlbSub:
eNumberId, eNumber

I created a relationship between the two tables, and when I go and view the
tblSub table, I can click the (+) sign and see the info from the tblMain
table. Now what I would like to do is have three combo boxes....eNumber,
Name, and Site. The eNumber should filter the Name, and the Name should
filter the Site.

Thanks,
Scott

 

[bismuth83]

Sure, can you give some sample data for the tables?

 

[cscotty]

Sure, can you give some sample data for the tables?

Sure can! Here you go:


tblMain: eNumber, Name, Site

E2343, Smith, 023
E2343, Jones, 598
E5567, Rafto, 434
E2343, Peterson, 109
E5567, Gomez, 003


tlbSub: eNumberId, eNumber

eNumberID: (random number)
eNumber: (E2343, E5567)


Thanks again!

 

[bismuth83]

Form "frmCombos" has 3 comboboxes with the following properties:

1) Combobox "cboENumber" -

Rowsource =
"SELECT tblSub.eNumber FROM tblSub;"

The After Update Event uses [event procedure] =
Private Sub cboENumber_AfterUpdate()
cboName.Requery
End Sub

2) Combobox "cboName" -

Rowsource =
"SELECT tblMain.Name FROM tblMain WHERE (((tblMain.eNumber)=Forms!
frmCombos!cboENumber)); "

After Update Event =
Private Sub cboName_AfterUpdate()
cboSite.Requery
End Sub

3) Combobox "cboSite" -

Rowsource =
"SELECT tblMain.Site FROM tblMain WHERE (((tblMain.Name)=Forms!
frmCombos!cboName)); "

Each subsequent combobox should update its list when the previous
combobox has made a selection.

 

[cscotty via AccessMonster.com]

Form "frmCombos" has 3 comboboxes with the following properties:

1) Combobox "cboENumber" -

Rowsource =
"SELECT tblSub.eNumber FROM tblSub;"

The After Update Event uses [event procedure] =
Private Sub cboENumber_AfterUpdate()
cboName.Requery
End Sub

2) Combobox "cboName" -

Rowsource =
"SELECT tblMain.Name FROM tblMain WHERE (((tblMain.eNumber)=Forms!
frmCombos!cboENumber)); "

After Update Event =
Private Sub cboName_AfterUpdate()
cboSite.Requery
End Sub

3) Combobox "cboSite" -

Rowsource =
"SELECT tblMain.Site FROM tblMain WHERE (((tblMain.Name)=Forms!
frmCombos!cboName)); "

Each subsequent combobox should update its list when the previous
combobox has made a selection.

Sweet! Thanks so much. Finally a reasonable and friendly way to solve this!

 

[cscotty via AccessMonster.com]

Hey Bismuth,

I tried you suggested code today at work but there seems to be a problem.
The first combo box works fine. When I click on the "cboName" combo box, an
input box pops up that says "Enter Paremeter Value Forms!frmCombos!
cboENumber". I have to insert the eNumber manually and then the correct
Names pop up.

Any hints why that would come up?

Thanks,
Scott

 

[cscotty via AccessMonster.com]

Hey Bismuth,

I tried you suggested code today at work but there seems to be a problem.
The first combo box works fine. When I click on the "cboName" combo box, an
input box pops up that says "Enter Paremeter Value Forms!frmCombos!
cboENumber". I have to insert the eNumber manually and then the correct
Names pop up.

Any hints why that would come up?

Thanks,
Scott

OK. I think I figured it out. I changed the code to the following. It
works, but please let me know if the syntax is correct:

SELECT tblMain.Name FROM tblMain WHERE tblMain.eNumber = Forms!frmQcBeta1!
cboEnumber;


Thanks,
Scott

 

[bismuth83]

Great, glad it works for you. The syntax looks fine to me.