cascading combo boxes

[Gremlin]

I am creating a form to have the contents of one list change depending on the
user's choice. I have used a table for the main combo box and several tables
for the dependent combo box. I entered a code that has a table that is
assigned as the row source of the main combo box and there are 4 more tables
which will provide in theri turn the row source. My problem is, when I am
looking at the form and click on the pull down arrow, only a blank space
appears--no options from the tables. what did I do wrong?

Private Sub cbotypeSx_AfterUpdate()
On Error Resume Next
Select Case cboTypeSx.Value
Case "Obesity Signs and Symptoms"
cboDx.RowSource = "obestbl"
Case "GI"
cboDx.RowSource = "GItbl"
Case "Abdominal Pain"
cboDx.RowSource = "abdtbl"
Case "Cardiac Symptoms"
cboDx.RowSource = "cardtbl"
Case "Endocrine"
cboDx.RowSource = "endotbl"
Case "Lipid disorders"
cboDx.RowSource = "Liptbl"
Case "Respiratory"
cboDx.RowSource = "Resptbl"
Case "Urinary symptoms"
cboDx.RowSource = "Urintbl"
End Select
End Sub

 

[Barry Gilbert]

I am creating a form to have the contents of one list change depending on the
user's choice. I have used a table for the main combo box and several tables
for the dependent combo box. I entered a code that has a table that is
assigned as the row source of the main combo box and there are 4 more tables
which will provide in theri turn the row source. My problem is, when I am
looking at the form and click on the pull down arrow, only a blank space
appears--no options from the tables. what did I do wrong?

Private Sub cbotypeSx_AfterUpdate()
On Error Resume Next
Select Case cboTypeSx.Value
Case "Obesity Signs and Symptoms"
cboDx.RowSource = "obestbl"
Case "GI"
cboDx.RowSource = "GItbl"
Case "Abdominal Pain"
cboDx.RowSource = "abdtbl"
Case "Cardiac Symptoms"
cboDx.RowSource = "cardtbl"
Case "Endocrine"
cboDx.RowSource = "endotbl"
Case "Lipid disorders"
cboDx.RowSource = "Liptbl"
Case "Respiratory"
cboDx.RowSource = "Resptbl"
Case "Urinary symptoms"
cboDx.RowSource = "Urintbl"
End Select
End Sub

I think you need to requery the combobox after setting it's rowsource:
cboDx.Requery

By the way, it might be simpler to add a column to the table that the first
combobox is based on. In the column, you could store the names of the tables
you want as the source for the 2nd combobox. For example, the table would
have:

Type TableName
Obesity Signs and Symptoms obestbl
GI GItbl
Abdominal Pain abdtbl

In the cboTypeSx combobox, you could hide the new column with the column
widths property. Then in the AfterUpdate, you could replace your select
statement with:

cboDx.RowSource = cboTypeSx.Column(1)

The advantage here is less code and simpler maintenance. If you add a table,
you don't have to change your code.

Barry

 

[Jeff Boyce]

Since everything Access starts from the data, could you describe a bit more
about your data structure. It wasn't clear, but seems like you have a
separate table for each "symptomology". If so, there's a chance that your
database has data embedded in the tablenames, rather than a single table
with an additional field to hold the "symptomology/type/group".

Regards

Jeff Boyce
Microsoft Office/Access MVP