Problems with Linking Master/Child Fields to Subform Control

[Ray Spackman]

HI Guys,

I am not new to Access or VBA, but certainly not an expert either.

Structure:
I have form that is a background form; frmSplash

Within frmSplash I have 2 continuous forms; cfrmInstitutions and cfrmPatients, and 1 additional subform control; frmFormHolder.

cfrmInstitutions has only 1 control; Institution(text) and is the Parent to cfrmPatients which has 2 controls; PatientMRN(text) and Patient(text).

Here is the code that the access wizard made two join these two continuous forms:
Private Sub Form_Current()

Dim ParentDocName As String

On Error Resume Next
ParentDocName = Me.Parent.Name
If Err <> 0 Then
GoTo Form_Current_Exit
Else
On Error GoTo Form_Current_Err
Me.Parent![cfrmPatients].Requery
End If

Form_Current_Exit:
Exit Sub

Form_Current_Err:
MsgBox Error$
Resume Form_Current_Exit

End Sub

This what I am trying to do:

When cfrmInstitutions has the focus, the frmformHolder should populate with a subform called frmInstutions - Which it does. It is set up to do this when opening frmSlpash.

When cfrmPatients has the focus, the frmFormHolder should populate with a subform called frmPatients - Which it does although I can't get it to work on any on_focus, or on_current event. I did it using the controls on_clickand on_focusevents on the cfrmPatients sunform using this code:

Forms![frmsplash]![frmFormHolder].SourceObject = "frmPatients"
Forms![frmsplash]![frmFormHolder].LinkMasterFields = ""
Forms![frmsplash]![frmFormHolder].LinkChildFields = ""
Forms![frmsplash]![frmFormHolder].LinkMasterFields = "[cfrmPatients].Form![PatientMRN] "
Forms![frmsplash]![frmFormHolder].LinkChildFields = "PatientMRN"

This code is used on both controls for both events.

When the focus is changed from cfrmPatients back to cfrmInstitutions, the frmFormHolder should populate back to the frmInstitutions, like it did upon opening. - WHICH IT DOES NOT.

I used the same coding type as in the cfrmPatients controls, since that seemed to work, but put it to the on-click and on_focus event for Institution control on the cfrmInstitutions subform and looks like this:

Forms![frmsplash]![frmFormHolder].SourceObject = "frmInstitutions"
Forms![frmsplash]![frmFormHolder].LinkMasterFields = ""
Forms![frmsplash]![frmFormHolder].LinkChildFields = ""
Forms![frmsplash]![frmFormHolder].LinkMasterFields = "[cfrmInstitutions].Form![Institution]"
Forms![frmsplash]![frmFormHolder].LinkChildFields = "Institution"

When going back to the cfrmInstitutions subform, I get a PatientMRN parameter box and after cancelling that I get this a run-time error 2101 saying "The setting you entered isn't valid for this property". After debugging it takes me to this line:

Forms![frmsplash]![frmFormHolder].LinkMasterFields = ""

for the Institution control on the cfrmInstitutions subform.

I researched and found that some said you have to clear the Master and Child Fields before re-assigning so that is why I have those lines.

I need help in getting this to work seamlessly and would also try to achieve my goal using an on-focus event form the subforms so I don't have to run the same code every time I navigate to different records in the subforms.

Not sure if matters or not, but all the subforms are based on their respective tables and not queries.

Sorry for the length of the thread and thank you in advance.

 

[Ray Spackman]

Sorry Forgot to mention that I am using Access 2007.

 

[Ray Spackman]

[SOLVED] Problems with Linking Master/Child Fields to Subform Control

After stumbling on another thread
http://www.access-programmers.co.uk/forums/showthread.php?t=204635
posted by JamesMcS, after some editing to the subform control; frmFormHolder and utilizing the oder of events as he posted, the navigation works just as I needed to. I even added a third form using the same procedures and all works good. I have not found away to accomplish without having to use the on_click or on_focus event of the subform controls, but that's okay at this point. I did find that I needed to either use the on_click OR on_focus event of the control on the subform but NOT both.

Thank you to all who viewed and were attempting to help.

The new code looks like this:

Private Sub NameofControl_Click()
Me.Parent![NameofSubformControl].LinkMasterFields = ""
Me.Parent![NameofSubformControl]].LinkChildFields = ""
Me.Parent![NameofSubformControl].SourceObject = "NameofSubForm"
Me.Parent![NameofSubformControl].LinkMasterFields = "[NameofParentForm].Form![NameofParentFormControl]"
Me.Parent![NameofSubformControl].LinkChildFields = "NameofSubformControl"
End Sub