Filter subform combobox on option button in main form

[Emma]

Hello,

I have a main form (frm_Jobs) that gives four options
(Option Group: Biopsy, Interventional, Surgical, Other)
for literature type which, when one is selected stores the
value in the "Discipline" Field of the Table "Tbl_Jobs".
Currently, the subform (based on Tbl_Lit_Broch_Line) has a
combo box which lists all the literature and brochures
available (populated by
Tbl_Lit_Broch "Description" field).

The tables are structured as follows:

Tbl Jobs:
Job_No (PK)
Description
Discipline

Tbl_Lit_Broch:

Description (PK)
Discipline

Tbl_Lit_Broch_Line:

Job_No (PK)
Description (PK)
Quantity

I would like my subform combobox to list only the
literature and brochures for the Discipline selected in
the Main form.

What I don't understand is how to filter the combobox
field in my subform on the current record's discipline on
the main form. Any clues?

Thanking you in advance.

Emma

 

[Allen Browne]

If you are asking how to limit the combo so the choices in its dropdown list
are limited to those that match the Discipline field in the main form, use
the AfterUpdate event of the option group in the main form to create a SQL
string and assign it to the RowSource of the combo. (You may also need to
handle the Undo event of the main form.) More information:
http://www.mvps.org/access/forms/frm0028.htm

If you are asking how to limit the subform so its records are filtered to
the Discipline selected in the main form, that woud normally be done by
setting the LinkMasterFields and LinkChildFields properties of the subform
control. Open the main form in design view, to set these properties.

 

[Emma]

Okay, I think I'm getting closer. However, still running
into errors.

I think if I get this error tackled the rest will fall
into place.

Option Group AfterUpdate code is:

Private Sub Discipline_Frame_AfterUpdate()
Dim strSQL As String
'Select the discline on which to filter from Option group
strSQL = "Select " & Me!Discipline_Frame
strSQL = strSQL & " from Tbl_Lit_Broch"
Forms!Sfrm_Tbl_Lit_Broch_Line!
Lit_Broch_Combo.RowSourceType = "Table/Query"
Forms!Sfrm_Tbl_Lit_Broch_Line!
Lit_Broch_Combo.RowSource = strSQL

'Requery of combo box control
Forms!Sfrm_Tbl_Lit_Broch_Line!Lit_Broch_Combo.Requery

End Sub

Error that I am getting is

"Run-time Error '2450'
Microsoft Access can't find the
form "Sfrm_Tbl_Lit_Broch_Line" referred to in in the Macro
or Visual Basic Code."

The form referred to is my subform than contains my combo
box "Lit_Broch_Combo". Why can't access find my subform.