I agree that there are other ways to filter the sub form, but I'll try to answer your question.
Let's say there are 5 fields in the sub form, "sfrmCustomer", record source: L_Name, F_Name, City, State and Zip.
Each of the 5 controls (NOT fields - forms have controls) has the On Got Focus event set to something like:
Code:
Forms!MainForm!UnboundField = the value in the field that the user clicked on
-------------------------------------------------------------------------------------------
So the code for the Last Name Got Focus event would be
Code:
Private Sub training_L_Name()
Forms!MainForm!UnboundField = Forms!MainForm!sfrmCustomer.Form.L_Name
End Sub
Code for the First Name Got Focus event:
Code:
Private Sub training_F_Name()
Forms!MainForm!UnboundField = Forms!MainForm!sfrmCustomer.Form.F_Name
End Sub
The other controls follow suit.
-------------------------------------------------------------------------------------------
Now the buttons.
Button1 click event would be
Code:
Private Sub Button1_Click()
Forms!MainForm!sfrmCustomer.Form.Filter = "L_Name = Forms!MainForm!UnboundField"
Forms!MainForm!sfrmCustomer.Form.FilterOn = True
End Sub
Button2 click event would be
Code:
Private Sub Button1_Click()
Forms!MainForm!sfrmCustomer.Form.Filter = ""
Forms!MainForm!sfrmCustomer.Form.FilterOn = False
End Sub
So far, so good??
Problems:
1) The first problem is that you need to concatenate the value to the filter string. Otherwise you will be trying to filter using the string "F_Name = Forms!MainForm!UnboundField"
2) My question to you is "How do you change the field to control on? You change the filter VALUE using the unbound control in the main form, but I do not see where the control name is changed.
You click on a name in the Last name control on the sub form, the value in the main form unbound control changes, but the Button1 code for the control to be filtered on in the sub form NEVER changes the control name.
Does this help?
You could have another unbound control (but hidden) in the main form to hold the control name.
Add a line to each of the ON FOCUS events
Code:
Forms!MainForm!ubCtlName = Me.L_Name.Name
Change the Button1 code to something like (air code):
Code:
Forms!MainForm!sfrmTraining2.Form.Filter = " & Forms!MainForm!ubCtlName & " = Forms!MainForm!UnboundField"
If the above doesn't help,