I have a subform with a combo. The uses selects a choice. On the 2nd subform, embedded in the first subform, I have another combo box. I want the Row Source on the 2nd subform to load from one of 3 lookup tables. I'm sure this is simple but I think that the problem that I am having is proper referencing in my IF statement. I have the IF statement tied to the After Update event on the 1st combo box.

I am using Access 2003.

Any help or direction will be appreciated.

Papa

Hard to see what's wrong with the if statement when it's not included.
Making the assumption that the name of the child object on the first subform is Second_Child and you want to change which field is used by the value of the first combo so the second combo would show values from that field in the same table then the code would be something like this:
---------------------------------------------------------------------------
Private Sub First_Combo_AfterUpdate()

  Dim sSource As String
  If First_Combo.Value = "" _
    Then sSource = "<Desired Default Select>" _
    Else sSource = "Select " & First_Combo.Value & " From Table1 Order By " & First_Combo
  Second_Child.Form.Second_Combo.RowSource = sSource

End Sub
---------------------------------------------------------------------------

Hope this helps,
Al

Al:

Here is the code that I've tried.
Parent form: frmParkInfoZone
1st child form: sbfZone4Areas; cboArea
2nd child form: ssubZone4; cboDetail

The choice of Area (4 different areas) will change the Detail combo box Record Source

Thanks for taking a look & your response,

Papa

==============

Private Sub cboArea_AfterUpdate()

    'reset detail list based on Area choice
    If Me.cboArea = "Main St." Then
        Me![frmParkInfoZone4]![sbfZone4Areas]! _
           [ssubZone4]![cboDetail].RecordSource = "qryDetailZ4MainSt"
    ElseIf Me.cboArea = "Windows." Then
        [ssubZone]![cboDetail] = "qryDetailZ4Windows"
    End If
End Sub

Private Sub cboArea_AfterUpdate()

    'reset detail list based on Area choice
    Select Case Me.cboArea
        Case "Main St."
            Me.ssubZone4.Form.cboDetail.RecordSource = "qryDetailZ4MainSt"
        Case "Windows."
            Me.ssubZone4.Form.cboDetail.RecordSource = "qryDetailZ4Windows"
        Case "Your Third Choice (you said four)"
            Me.ssubZone4.Form.cboDetail.RecordSource = "qryDetailZ4Choice3"
        Case Else
            Me.ssubZone4.Form.cboDetail.RecordSource = "qryDetailZ4Choice4"
    End Select

End Sub

I fixed a typo in my last post.

Note that you seem to have four queries that may do the same thing but are based on the choice of area. You may not need to do this. You should probably have ONE query that accepts the value of the combobox and runs using that value.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division