Selection Criteria using multiple combo boxes on form witha subform

[rfstott]

This database is used to track TV series that are being watched or have been watched.
I have multiple combos on a tab form to to use as filter options. After making selection in the verious combo boxes, I then choose a to click (prodecure Event) buttton that uses the following Code to anylize the records.

Private Sub btn_Select_Records_Click()
On Error GoTo Err_btn_Select_Records_Click
 
    Dim xAnd As String
    Dim SelCrit As String
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "Frm_TV_Series_Edit"
 
    'Selection Criteria
'Intialization
    xAnd = ""
    SelCrit = ""
'This Section Processes Series Items for a specific Series
    If IsNull(Me![Select_Series_Name]) = False Then
    SelCrit = SelCrit & xAnd & "[TV_Series_Name_ID] =" & Me![Select_Series_Name]
    xAnd = " And "
    End If
'This Section Processes Series Items for Specific Source
    If IsNull(Me![Select_Series_Source]) = False Then
    SelCrit = SelCrit & xAnd & "[Source_ID] =" & Me![Select_Series_Source]
    xAnd = " And "
    End If
'This Section Processes Series Items for Specific Status
    If IsNull(Me![Select_Series_Status]) = False Then
    SelCrit = SelCrit & xAnd & "[Viewing_Status_ID] =" & Me![Select_Series_Status]
    xAnd = " And "
    End If
 
    DoCmd.OpenForm stDocName, , , SelCrit
 
Exit_btn_Select_Records_Click:
    Exit Sub
 
Err_btn_Select_Records_Click:
    MsgBox Err.Description
    Resume Exit_btn_Select_Records_Click
End Sub

The form Frm_TV_Series_Edit has a subfrom frm_Season. The form uses a query that includes the tbl_TV_Series and directley related tables.
The subform uses a query that includes tbl_TV_Season and some dirctely related tables. The two queries use the (PK)TV_Series_ID.

The select Specific Series and Specific Status work just fine (the are associted with the main form query). The select Specific Source does not work (it is associted with the subform query)

I know I need different syntax other than just "[Source_ID] =" since it should be comparing the field on the subform (not the main form) with the combo box Me![Select_Series_Source].

I know that the subform is considered a control but after extensive searching online and reading - (lots of trial and errors) - I just can't figure out the correct syntax.

[NeoPa]

You may find the following links helpful :
Referring to Items on a Sub-Form.
Example Filtering on a Form.
Let us know if you still have problems. If so, explaining exactly what and where your problem is would be likely to encourage other experts to offer help.

[rfstott]

Here is code that I have which can't find the Source_ID on the main form (query) that being called - it needs to look at the subform to find Source_ID.

'This Section Processes Series Items for Specific Source
    If IsNull(Me![Select_Series_Source]) = False Then
    'SelCrit = SelCrit & xAnd & "[Source_ID] =" & " & Me![Select_Series_Source]"
    xAnd = " And "
    End If

I tried the various syntax examples that should refer to the subform and its Source_ID; the "[Source_ID]" needs to replaced with syntax that refers to the subform.
I tried Me.[frm_Series]![Source_ID] - it still can't find the Source_ID which needs to match [Select_Series_Soure] which is from a combo box on the selection criteria form.

I guess I am just to old to understand this!

I am happy to post whatever is needed - this is the first time that I had to deal with the kind of one to many relationships (where the form has a subform)

[NeoPa]

This may be tricky then. I need more info from you in order to give specific answers. The linked article is my attempt to explain the concepts as clearly as possible without a specific scenario.

We often to a bit of extra hand-holding - and that's fine - but I'd need to know the names of all the relevant objects and how they relate to each other in order to understand your situation fully enough to walk you through it.

Maybe you could use the linked article to go through your form structure and post here what's what.

[rfstott]

'This Section Processes Series Items for Specific Source
    If IsNull(Me![Select_Series_Source]) = False Then
    SelCrit = SelCrit & xAnd & " [Source_ID] =" & " & Me![Select_Series_Source]"
    xAnd = " And "
    End If

Okay! To start with I have a form with tabs , Select Series, Reports, & Edit Fields. The Select Series tab has three combo boxes to pick what is to be selected; Series, Source, & Status.

The form that is being called for is Frm_TV_Series_All that uses a query Qry_Main. That form has a subform (Frm_Season) that uses a query (qry_season) the form and subform are linked with Series_ID. The Series & Status selection criteria work fine because they are attributes of the form (Frm_TV_Series_All) and its query.


I change the code to try to get it to refer to the subform which has the Source_ID

'This Section Processes Series Items for Specific Source
    If IsNull(Me![Select_Series_Source]) = False Then
    SelCrit = SelCrit & xAnd & " Me.[frm_Season].Form.[Source_ID] =" & " & Me![Select_Series_Source]"
    xAnd = " And "
    End If

I get the followig error: Syntax error (missing operator) in the query expression 'Me.[Frm_Season].Form.[Source_ID] = & Me![Select_Series_Source]

The Select_Series_Source is the name of the combo box on the Series tab of the selection criteria form.