Linking a Combo box to variable SQL query in subform

[Mark]

I am in the process of creating a relatively small D'Base that will monitor
employee competency.
There are several tables and relationships within this, but I don't consider
this to be a big project (although my first post-graduation in the real
world).

I have configured the records to be spread over two tables. One is the
tbl_ReviewHearder (containing employee info', review date), the second,
tbl_ReviewContent contains the competency information. This is based on a
1-many relationship (1 header, lots of content)

I have written VB to filter the combo boxes, but I am having trouble getting
the final combo box [Element] to load a subform containing the Criteria. I
have written a simple query, but that requires that I manually enter the
Element value. I would like this to be past to the query automatically, or
possibly on a button click event.

Further info' listed below.

Any pointers or help is always greatly appreciated.

Mark



tbl_ReviewHeader:
ReviewID (autonumber)
Employee_ID (lookup from tbl_employee)
JobTitle (lookup from tbl_jobs)
ReviewPeriod (lookup from tbl_reviewperiod)

tbl_ReviewContent:
ReviewContentID (autonumber)
ReviewID (linked to tbl_ReviewHeader)
Unit (combo selection)
Cluster (combo selection)
Element (combo selection)

tbl_Element_Criteria_Subform:
Criteria
Rating
Notes

I am aiming to get the list of criteria that are under any given 'Element'
to be loaded in the subform as a list. I can then commit these to a record

The competencies are linked on a one to many basis e.g.

Unit = 1
Cluster = 1.1 (1.2, 1.3, 2.1 etc)
Element = 1.1.1 (1.2.1, 1.3.1, 2.1.1 etc)
Criteria = 1.1.1.1 (1.2.1.1, 1.3.1.1, 2.1.1.1 etc)

 

[NetworkTrade]

to pass a criteria to a query from a form ; you need to reference the correct
Form control in the query's criteria: Forms!FormName.ControlName

When that control is a text box it is this simple.

If the control is in a subForm - then you have to reference the subform
first and follow some literal syntax:

Forms!FormName.SubFormName.Form!ControlName

If the control is a combobox then the value of that control is the bound
column - not often what you visually see in the combobox.