Open (sub)form linking 2 criteria on current form

[websterh]

Hi, THIS IS AN URGENT POSTING (it's for work)

Sorry if this sounds repetitive but i have searched on the internet and other forums for for at least 4 hours to find a clear answer to this but when i try those steps "Type mismatch" and other error messages always appear.

Table structure:
Primary Key = MRN
Primary Key = Facility (due to facilities possibly having the same MRN)

I have one form (CLIENT) that holds/displays the main information.
I click on a button to open the popup form (CLIENT Edit).


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?.

[ssanfu]

...<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:

   Dim strWhere as String

then

' 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.

[websterh]

Thank you so much for the quick reply (and great coding) Steve.

It works.
For everyone's reference the coding is below.

Private Sub CMD2ClientEdit_Click()
Dim strWhere As String
strWhere = "[MRN]=" & Me.[MRN] & " and [Facility] = '" & Me.[Facility] & "'"
DoCmd.OpenForm "CLIENT Edit", acNormal, , strWhere, acFormEdit, acWindowNormal
End Sub

I seemed to have the strWhere and the single quote, i.e. ' , missing but... yay --- this has def been the highlight of my day.