Query to filter combo box in subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Access 2000, Beginner/Intermediate Lvl.

I would like to filter the combo box selection in subform A (subCategory)
dependent on combo box selection(s) in subform B (Category).

Relationship:

tblRecord-m1-tblCategoryRcd-1m-tblCategoryList
-m1-tblSubCategoryRcd-1m-tblSubCategoryList

tblSubCategoryList-1m-tblCategoryList

Thanks in advance, I have attempted to get the query set-up right but have
had limited success, I prob just need pointing in the right direction. I have
a vague understanding of SQL.

Nigel
 
Nigel, appologies as I have not taken time to read the relationships, but
this sounds very similar to one of the examples in Nwind where the 1st sub
form has a value selected that is the filter for records in a second sub
form.

The trick in this case was to have a hidden field on the main form that
simply referenced the filter value on the 1st sub form.

in your case the hiden control on the main form would do somenthing like
Name :MyHiddenField
Control Source :=SubFormB_Category.Form!MyComboBox

The combo on the subCategory form would then have a select query something
like

Select * from Mytable where filterfield = forms!MainForm!MyHiddenField
 
John, thanks for the reply, it wasn't quite the answer i was looking for, but
it made me think in the right direction in terms of queries.
Your solution isn't quite right 'cos in the 1st subform (Categories) there
can be multiple selections (e.g. Beatles and Rolling Stones and Elvis) and
this enable the 2nd subform (Sub-Categories) to choose any of their songs
(e.g. Penny Lane and Let It Be and Paint It Black, etc), and have multiple
selections there of. So one field isn't enough, a table is needed, hence a
query, in fact two queries:

The first query makes a list of categories that correspond to the record
selected in the form:

SELECT tblCategoryRecord.fkCategoryList, tblCategoryRecord.fkRecord
FROM tblCategoryRecord
WHERE (((tblCategoryRecord.fkRecord)=[Forms]![tblRecord]![recordKey]));

The second query selects from the sub-categories table where the
sub-categories category is in the first query.

SELECT tblSubCategoryList.strSubCategoryListName,
tblSubCategoryList.fkCategory
FROM tblSubCategoryList, [tblCategoryRecord Query]
WHERE (((tblSubCategoryList.fkCategory)=[tblCategoryRecord
Query].[fkCategoryList]));

now I just got my updates working correctly ;-).

cheers,

Nigel
 
Back
Top