Newbie: Inter-related combos on form, how-to?

[Ben Fidge]

This will probably be really simple, but I'm trying to
knock up a simple knowledge base application using Access
2000, taking advantage of the in-built Forms and
Reporting.

I have two tables, Categories and Sub-Categories, that
have a one-to-many relationship. For each Category there
could be many Sub-Categories.

I have a Form for adding new Articles to the database and
want to provide a Categories combo, which filters a Sub-
Categories combo according to the Category selected.

I added the follwoing code the the main Category's
OnChange event handler, but it doesn't work:

"cboSubCategory.RowSource = "select UKeyMainCategory,
Description from SubCategories where UKeyMainCategory = "
+ cboMainCategory.ItemData(1)"

Can anyone point out where I'm going werong, or provide a
better method?

Thanks

Ben

 

[Gerald Stanley]

Try the following

Place you code in the AfterUpdate() event handler rather
than the Change event handler

Use the value of the ComboBox rather than the first item in
the list e.g.

cboSubCategory.RowSource = "select UKeyMainCategory,
Description from SubCategories where UKeyMainCategory = "
& cboMainCategory.value

Hope That Helps
Gerald Stanley MCSD
-----Original Message-----

 

[Ben Fidge]

Gerald,

Thanks a lot. It is now performing the functionality I
was after, with the exception, that whenever a row is
selected in the SubCategories combo, it is not being
displayed in the combo's text area! The combo flashes as
if refreshing, but no value is being displayed.

Any ideas? Could this be something table-level?

Ben

 

[Gerald Stanley]

Ben

The first thing to check is whether you have any event
handlers for the SubCategories combo. If you have, then I
would suggest removing then to prove that the combo box
text area will populate correctly.

Hope That Helps
Gerald Stanley MCSD

 

[Ben Fidge]

Hi Gerald,

There's never been any events on the SubCategory combo.
When designing the Articles table, the UKeySubCategory
field had some SQL in the Lookup->RowSource box.

I removed this so that the RowSource is only populated
via the evetn handler mentioned previously.

Now, the SubCategory combo gets populated fine, but when
any item is selected it defaults back to the first item
in the list!

Ben

 

[Gerald Stanley]

Ben
Can you provide some clarification on the third paragraph.
Specifically

1.Is the Combo Text component getting populated now?

2.How do you know that it is defaulting to the first item
in the drop down list no matter what item is selected?

Gerald

 

[JeffW]

Hi Ben,

so, UKeySubCategory is the PK of your SubCategories table and
UKeyMainCategory is a FK in the same table, right?
'warning: aircode!
'assumes UKeyMainCategory is an integer and is the bound field in
cboMainCategory
'set column widths to 0;2;0 to show only Description

Private Sub cboCategory_AfterUpdate()
me.cboSubCategory.rowsource = "SELECT UKeySubCategory," & _
"Description,UKeyMainCategory FROM SubCategories " & _
"WHERE (((SubCategories.UKeyMainCategory)=" & Me.cboMainCategory & "));"
End Sub

'if UKeyMainCategory is text, then a slight modification to the where clause
is needed:
"WHERE (((SubCategories.UKeyMainCategory)='" & Me.cboMainCategory & "'"));"

HTH,
Jeff

 

[Ben Fidge]

Got it working. I used the lookup field wizard when
designing the Articles table. I removed the field and
just created a plain integer field called UKeySubCategory
in the articles table. Work fine now with the event based
filtering.

Thanks anyway.

Ben