Basing one Combo Box on another

[Chucklez]

Can anyone help me. I am trying to base one combo box (SubCategoryList) on
the value in another combo box (CategoryList) I have downloaded the same data
base from MicroSoft and followed the instructions provided, but was unable to
get the "lookup" to work. I have the combo fields on a Form and have written
queries for the look up in the Category Field and in the Subcategory field.
Then I built the code for event, but for some reason it is not working. My
tables are
1. Category, with fields - ID (auto number), Category Name and CatNotes
2. Subcategories, with fields - ID (auto number), CatID (same as ID from
Category Table), Sub-Category, and Keywords
My form - called frmtestCombined has two combo boxes named cboCategorySelect
and this box works correctly. Second box is named cboSubCatSelect and
provides me with zero values to select from. Here is the syntax that I have
used for the "event" for the cboCategorySelect box:

Option Compare Database

Private Sub CatName1_AfterUpdate()
Me!cboSubCatSelect.Requery

End Sub

Private Sub cboCategorySelect_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub cboSubCatSelect_AfterUpdate()

End Sub

Private Sub Form_Current()
Me!cboSubCatSelect.Requery
End Sub

Any help would be greatly appreciated - I am about ready to throw the
computer out the window!

 

[pietlinden]

here's an example...
http://www.mvps.org/access/forms/frm0028.htm

 

[Chucklez]

Thank you for your response, but I am still having a problem. Now on my
second combo box I at least get some options, but it is the entire list, not
just the ones that have the "category name" that is reflected in the first
combo box. I know it has to be a simple syntax error, but I cannot figure it
out. Here are my tables
Category Table
ID CategoryName CatNotes
1 Local Services
2 Accommodations
3 Arts
4 Dining & Spirits
5 Recreation & Adventure
6 Shopping
7 Getting Around Homer
8 Fishing
9 Individual Members
10 Beyond Homer

Subcategories Table (just partial list)
ID CatID Sub-Category Keywords
1 Local Services
2 Local Services Automotive & RV Sales & Services
3 Local Services Banking, Investments & Mortgages
4 Local Services Beauty Salons & Barbers
48 Accommodations Apartments & Condos
49 Accommodations Bed & Breakfasts
50 Accommodations Cabins & Cottages
51 Accommodations Camping & RV Parks
52 Accommodations Hostels
57 Arts Art Galleries & Studios
58 Arts Art Organizations & Groups
61 Dining & Spirits Bakeries & Coffee Shops
62 Dining & Spirits Breweries & Wineries
63 Dining & Spirits Casual Dining
70 Recreation & Adventure Birding
71 Recreation & Adventure Bowling
75 Recreation & Adventure Four Wheeling
76 Recreation & Adventure Golfing
77 Recreation & Adventure Horseback Riding & Tours
84 Shopping Antiques & Second Hand
85 Shopping Arts & Gifts
86 Shopping Automobile Parts & Sales
87 Shopping Books, Music & Videos
109 Fishing
111 Individual Members

and this is the criteria from my qrySubCatLis
IIf(IsNull([Forms]![frmtestCombined]![cboCategorytSelect]),[CatID],[Forms]![frmtestCombined]![cboCategorySelect])

Again, any assistance would be greatly appreciated