...<snip>I am wanting to open this form linking the active record (on CLIENT) to the opened record (on CLIENT Edit) using both the MRN & Facility references.
The following works for the first MRN matching the criteria:
DoCmd.OpenForm "CLIENT Edit", acNormal, , "[MRN]=" & Me.[MRN], acFormEdit, acWindowNormal
This however only gets the first Facility name (sorted A-Z) instead of the actual record i would like. Example: MRN 001231 are both at HosA and HosT; whenever the button is clicked HosA will always appear when it loads even though HosT is the record/facility i want.
Any chance someone could please post the VBA to this as i have tried roughy 20 different variations of possibly workarounds/answers but they do not seem to work?.
At the beginning of the procedure add:
Code:
Dim strWhere as String
then
Code:
' If the field "[Facility]" is a string, use delimiters
strWhere = "[MRN]=" & Me.[MRN] & " and [Facility] = '" & Me.TEXT_BOX_NAME & "'"
DoCmd.OpenForm "CLIENT Edit", acNormal, , strWhere, acFormEdit, acWindowNormal
where "Me.TEXT_BOX_NAME" is the name of the control the has the Facility name. Change "Me.TEXT_BOX_NAME" to the name of your control.
If you set a breakpoint, you can check strWhere to see if the "Where" string has the correct parameters.