Access VBA SubForm Not Filtering Other Subform Whe

Question

I have an unbound form with two subforms embedded into a navigation form, like this:

On the subform on the left side, I have the following code related to the OnFocus Event of the CustomerName field, which filters the subform on the right to show the customer detail that is selected from the right:

Private Sub CustomerName_GotFocus()

Forms!frmCustomers_sjh![Customer Address Form].Form.Filter = "CustomerName = '" & Me.CustomerName & "'"
Forms!frmCustomers_sjh![Customer Address Form].Form.FilterOn = True

End Sub

This works great when I have the frmCustomers_sjh open by itself, but it craps out when I run it from the Navigation form. I get Run-Time Error '2450' that Access can't find the referenced form 'frmCustomers_sjh`.

I've done enough research to know that running the code within the navigation form is different and a bit trickier to play with, but I haven't been able to solve the issue here.

Things to note:

1. Not using the Navigation Form is not an option (client requirements)
2. Splits Form are not an option either, as I need a search directory on only customer name on the right, with a detail form on left.
3. I am open to any other design solutions, as my understanding of Navigation Forms is low since I just started playing with it.

Answer 1

This additional site added more value to my experience to this bug: http://www.mrexcel.com/forum/microsoft-access/635816-visual-basic-applications-buttons-dont-work-navigation-form.html

Answer 2

I solved this!

I took out the VBA code and placed a text box with the control source set to the CustomerName field in the subform on the left in the unbound form. Then I created the parent-child relationship for the subform on the right between that text box and the CustomerName field in that form!

Woohoo!

This link helped a lot!