help with filtered combo box

[Darryn]

Hi all
I have three bound combo boxes on my form and have read many posts
about how to get the contents of one box to limit the selections in
the next one.

The examples I have seen have mainly used an unbound box but I need it
bound to enter info into a main table. Here is the details of what I
have tried so far to get just the first two working,

Combo 1 named cboCategoryID
looks up the main category from
TblCategory (PK)
CategoryID
Category
its row source is set to TblCategory, columns 2 bound column 0

with the followoing procdure to requery the combo box
Private Sub cboCategoryID_AfterUpdate()
Me!cboSubCategID = Null
Me!cboSubCategID.Requery
End Sub

Combo 2 named cboSubCategID
looks up the sub category from a junction table
TblSubCategories
SubCategoryID (PK)
SubCategory
CategoryID

I have its row source set as the following statement
SELECT TblSubCategories.SubCategory
FROM TblSubCategories
WHERE (((TblSubCategories.CategoryID) Like
[forms]![FrmEquipment]![cboCategoryID]));

From what I have read this should make combo 2 look at the first combo
for its row source, but I can't get to work. I have tried changing to
bound column of combo 2 from 0,1,2 but this has no effect.

Can anyone help me as to what I am doing wrong

Thanks

Darryn

 

[Emilia Maxim]

Darryn,

The examples I have seen have mainly used an unbound box but I need it
bound to enter info into a main table. Here is the details of what I
have tried so far to get just the first two working,

Combo 1 named cboCategoryID
looks up the main category from
TblCategory (PK)
CategoryID
Category
its row source is set to TblCategory, columns 2 bound column 0

Bound column should be 1. When setting this property, numbering starts
with 1, there's no column 0.

with the followoing procdure to requery the combo box
Private Sub cboCategoryID_AfterUpdate()
Me!cboSubCategID = Null
Me!cboSubCategID.Requery
End Sub

Combo 2 named cboSubCategID
looks up the sub category from a junction table
TblSubCategories
SubCategoryID (PK)
SubCategory
CategoryID

I have its row source set as the following statement
SELECT TblSubCategories.SubCategory
FROM TblSubCategories
WHERE (((TblSubCategories.CategoryID) Like
[forms]![FrmEquipment]![cboCategoryID]));

I suppose you would want to have the SubCategoryID in your main table,
not the SubCategory (description or whatever of it). Plus you don't
need the LIKE operator because there are no wildcards involved; also
the IDs are probably numbers, not texts. So you would then need:

SELECT TblSubCategories.SubCategoryID, TblSubCategories.SubCategory
FROM TblSubCategories
WHERE (((TblSubCategories.CategoryID) =
[forms]![FrmEquipment]![cboCategoryID]));

Column width: 0; 1.5
Bound column: 1

To display the correct value in combo 2 when navigating in saved
records, you'll also need to requery it in the form's OnCurrent event
procedure:

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

HTH

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de

 

[Darryn]

Darryn,

The examples I have seen have mainly used an unbound box but I need it
bound to enter info into a main table. Here is the details of what I
have tried so far to get just the first two working,

Combo 1 named cboCategoryID
looks up the main category from
TblCategory (PK)
CategoryID
Category
its row source is set to TblCategory, columns 2 bound column 0

Bound column should be 1. When setting this property, numbering starts
with 1, there's no column 0.

with the followoing procdure to requery the combo box
Private Sub cboCategoryID_AfterUpdate()
Me!cboSubCategID = Null
Me!cboSubCategID.Requery
End Sub

Combo 2 named cboSubCategID
looks up the sub category from a junction table
TblSubCategories
SubCategoryID (PK)
SubCategory
CategoryID

I have its row source set as the following statement
SELECT TblSubCategories.SubCategory
FROM TblSubCategories
WHERE (((TblSubCategories.CategoryID) Like
[forms]![FrmEquipment]![cboCategoryID]));

I suppose you would want to have the SubCategoryID in your main table,
not the SubCategory (description or whatever of it). Plus you don't
need the LIKE operator because there are no wildcards involved; also
the IDs are probably numbers, not texts. So you would then need: Ok
SELECT TblSubCategories.SubCategoryID, TblSubCategories.SubCategory
FROM TblSubCategories
WHERE (((TblSubCategories.CategoryID) =
[forms]![FrmEquipment]![cboCategoryID]));

Column width: 0; 1.5
Bound column: 1

To display the correct value in combo 2 when navigating in saved
records, you'll also need to requery it in the form's OnCurrent event
procedure:

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

HTH

Thanks Emilia

Darryn