Use one button to open one of two linked forms...

[Pharma Down]

[My understanding of coding is nil (as you'll know if you have seen my other posts), so sorry if this query is too simple and should be posted elsewhere!]

I have a form that displays a list of 'active' records that are read/accessed on a different form - like a menu, showing the title/date of the record. Because of the way these records are created some might be linked to a patient (it's a healthcare info database) or not, but all will have a record (intervention) ID. If the recod is not linked to a patient the foreign key (Patient_ID) =0 (null?).

At the moment, to access the records I have two buttons:
- one opens patient linked records (Patient_ID = 1,2,3 etc): an intervention subform within a patient details form
- the other button opens the intervention (sub)form on its own with no patient details form, because no patient is linked.

This works, but is clumsy... if you click the patient-linked button where there is no patient you open the form with no record. This is what the code looks like for the two buttons:

Private Sub btn_open_Frm_interventions_Click()
On Error GoTo Err_btn_open_Frm_interventions_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Frm_interventions"

stLinkCriteria = "[intvn_ID]=" & Me![intvn_ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_btn_open_Frm_interventions_Click:
Exit Sub
Err_btn_open_Frm_interventions_Click:
MsgBox Err.Description
Resume Exit_btn_open_Frm_interventions_Click

End Sub
-------
Private Sub btn_opne_Frm_patient_interventions_Click()
On Error GoTo Err_btn_opne_Frm_patient_interventions_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Frm_patient_interventions"

stLinkCriteria = "[Patient_ID]=" & Me![Patient_ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_btn_opne_Frm_patient_interventions_:
Exit Sub
Err_btn_opne_Frm_patient_interventions_Click:
MsgBox Err.Description
Resume Exit_btn_opne_Frm_patient_interventions_

End Sub

I have been doing a little reading, to try to understand the coding a little better and have come across iif and IsNull.

Assuming that when an intervention is not linked to a patient (the form displays Patient_ID=0) this is a 'null,' could I use one button to open one form or the other? Something like:

iif (IsNull([Patient_ID]), open-the-intervention-form-linked-by-intvn_ID, open-the-patient-intervention-form-linked-by-Patient_ID

Obviously, I don't know how to go about combining all the code for the two buttons, especially since stLinkCriteria could refer to two things!

Is this possible?

The code (in blue) is created by the button wizard - I don't know which bits are actually necessaary!

Andy

 

[pr2-eugin]

Hello Pharma, what you want to achieve is possible, but I have one question, Where is the Patient ID? Is it displayed on the Form? Or is the Form bound to Intervention table where the ID of Patient is put as 0 if it has no patient record?

Your code would look something like..

Private Sub [COLOR=RoyalBlue]openNewForm[/COLOR]_Click()
    On Error GoTo Err_btn_open_Frm_interventions_Click
    
    Dim stDocName As String
    Dim stLinkCriteria As String
    If Nz([B][COLOR=Red][Patient_ID][/COLOR][/B],0) = 0 Then
        stDocName = "Frm_interventions"
        stLinkCriteria = "[intvn_ID]=" & Me![intvn_ID]
    Else
        stDocName = "Frm_patient_interventions"
        stLinkCriteria = "[Patient_ID]=" & Me![Patient_ID]
    End If
    
    DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_openNewForm_Click:
    Exit Sub
Err_openNewForm_Click:
    MsgBox Err.Description
    Resume Exit_openNewForm_Click
End Sub

 

[Pharma Down]

Ah, no! The intervention form is boud to an intervention table, which contains a foreign key - so that you can link the intervention record to a patient if needed. You have two routes to record an intervention:
1) open intervention (sub)form on its own - recors an intervention in the intervention table
2) go vi athe patient route - open intervention via patient form - opens a form that displays patient details (linked to patient table) and contains the intervention subform - this automatically 'pastes' the Patient_ID into the foreign key for the intervention table... (in option 1 nothing is pasted in to the Patient_ID field and it reads '0')

.... I hope that makes sense!

 

[pr2-eugin]

1) open intervention (sub)form on its own - recors an intervention in the intervention table
2) go vi athe patient route - open intervention via patient form - opens a form that displays patient details (linked to patient table) and contains the intervention subform - this automatically 'pastes' the Patient_ID into the foreign key for the intervention table... (in option 1 nothing is pasted in to the Patient_ID field and it reads '0')

Okay so what you want to do now? Where do you have the Button that will open a one of the two forms??

 

[Pharma Down]

I have sorted it! Thank you - that's ace.

I was having problems, becuaseI had failed to set the On_Click to 'Event Procedure' (so it wasn't doing anything).

It now works a treat!

Andy