Setting RowSource based on another field in Access97

[wyld]

I have a form that uses 4 reason codes. Based on the reason code, different fields are visible/invisible. Two of the reason codes (A and B) using the same combo box but display different values.

Based on the reason code, I want to use a different query to get the information for Error Reason.

For reason code A, I want to populate the field using qryA.

For reason code B, I want to populate the field using qryB.

(qryA and qryB are selecting information from the same table)

How can I do this? I'm assuming I need to code some sort of Event but I'm not sure what.

Thanks in advance

 

[tomschafer]

Try this, change the blue to match the name of your Combo Box (If that is what you are using to select your reason code), change the green to match the values in the Combo Box...

Private Sub Combo0_AfterUpdate()

If Me.Combo0 = "A" Then
Me.RecordSource = "qryA"
Me.Requery
ElseIf Me.Combo0 = "B" Then
Me.RecordSource = "qryB"
Me.Requery
End If

End Sub