Building button to open form with a Where condition

[partana]

Hello,
I am currenly working with two Forms
Form 1- Patient Form (PatientNum)
Form 2- Patient History (PatientNum)
I have just viewed a great YouTube video on how to make a button Open a Form and go to a matching record using the Where Condition. I got it to work great, but it only works once when opening the new form from the navigation bar. It doesn’t seem to want to work while I have both forms opened. I have to close Form 2 and used the button on Form 1 to make the event happen.
I am using a simple macro to Open Form 2 with the following Where conditional statement.
[PatientNum]= [Forms]![PatientForm]![PatientNum]
Because I am entering a lot of data I generally have both forms open and flip back and forth using the tabs. I would like, when I press the Open Form button for the Patient Number in Form 1 to match the Patient Number on Form 2.
It has been suggested that I use a subform- and i know how to do this- i would really like to learn how to create the macro so that i can use it in other forms as they are developed.
Thanks.. you guys have never let me down

 

[boblarson]

Re: Building buttonto oepn form with a Where condition

The Where clause method only works when opening a form from a closed state. You can make it close and reopen using the where clause if you were to do something like this in the button on form 1:

Application.Echo False
   If CurrentProject.AllForms("Form 2").IsLoaded Then
       DoCmd.Close acForm, "Form 2", acSaveNo 
   End If
   DoCmd.OpenForm "Form 2", acNormal, , "yourWhereClauseHere"
Application.Echo True
End If

By the way, the acSaveNo in the close of the form code has to do with DESIGN changes not records.

 

[partana]

Re: Building buttonto oepn form with a Where condition

Hi Boblarson,
Sorry, really new at this. Can you please take me through the steps for attaching this code?
I have gone to the button on Form 1 in Design View and opened the Maco. When hit the three dots on the Where Condition it brings me to the expression builder which I don't think is right for inserting this code.
I tried creating a whole new Command button for Form1 and on the Event Tab On Click command I have selected the Code builder. Here is where i have inserted your code substituting my information:

Private Sub Command118_Click()
Application.Echo False
If CurrentProject.AllForms("2 Medical History Form ").IsLoaded Then
DoCmd.Close acForm, "2 Medical History Form ", acSaveNo
End If
DoCmd.OpenForm "2 Medical History Form ", acNormal, , "[Biomarker Number]=[Forms]![1 Patient Registration Form]![Biomarker Number]"
Application.Echo True
End If
End Sub

Thanks!

 

[boblarson]

Re: Building buttonto oepn form with a Where condition

Does the new button work? And to put the code with your existing button you just open the form the button is on (2 Medical History Form) in DESIGN VIEW and click on the button to select it. In the properties dialog - EVENT TAB, select [Event Procedure] from the list and click the Ellipsis (...) that appears to the right. That will open the VBA window where you would put the code I gave you.

 

[partana]

Re: Building buttonto oepn form with a Where condition

Sorry for the delay in replying, but i have been:banghead:. Sadly I could not get the code you sent to work. I did all the steps you suggested. Someone sent me the following for the Where condition and it works great
="[Biomarker Number]=" & "'" & [ Biomarker Number] & "'".

NB: Can you explain why in some of the answers i have received for Access 2007, some of the expressions are written with ( ), some with [ ], now this one with the ""& .

Thanks for your help