Update main form text boxes as subform is filled in? Also, subforms not linking? (1 Viewer)

[bonzitre]

Good morning,
So, today's issue is this. I have a form [PatientAllSubs] that has two tabs on it (ONN/PSC and SLC) with subforms on each [PatientSubform] and [SLCSubform] respectively. It is loading the information from the [Patients] table:

txtPatientTitle uses the formula:

=Nz([MRN] & " - " & [LastName] & ", " & [FirstName] & " - " & [DOB],"Untitled")

Users get to [PatientAllSubs] from my home navigation [HOME] on subform/navigation form of home [PatinetSearchSubform]. They can either search for a patient which carries the information over to [PatientAllSubs] or click a button to enter a new patient.

The code for Open Patient Record is:

Private Sub cmdOpenPatient_Click()

On Error GoTo ErrorHandler

DoCmd.OpenForm "PatientAllSubs", , , "[ID]=" & [lstResults]
Exit Sub
ErrorHandler:
Select Case Err.Number
Case Else
MsgBox "A Patient must be selected. "
End Select
End Sub

There is other code for the search, but that is primary.

The Code for New Patient Entry is just:

Private Sub cmdPtEntry_Click()

DoCmd.OpenForm "PatientAllSubs"
DoCmd.GoToRecord , , acNewRec

End Sub

So the code is sending the patient ID to the Main form [PatientAllSubs] which is then linked as the Master to the [PatientSubform] and is also the Master for the Child [SLCSubform].

SO, the issue is, when you create a NEW patient by clicking "New Patient Entry" from either the search subform or the NEW PATIENT button the the [PatientAllSubs] form, I am wanting it to update the MAIN form ID, MRN, LastName, FirstName, and DOB fields from the subform to the main form as they are being typed, but it would also have to save the record in order to pull an autokey from the table. Because without updating the MAIN form, if I try to click on the SLC tab or go to a NEW ACTIVITY LOG it won't pull the data with it. Even if I save the record.

Code for the NEW ACTIVITY LOG is:

Private Sub cmdCallLog_Click()
On Error GoTo ErrorHandler
DoCmd.RunCommand acCmdSaveRecord
Dim strFrmName As String
strFrmName = "ActivityEntryF"

    DoCmd.OpenForm strFrmName
        With Forms(strFrmName)
            .MRNcall = Me.MRN
            .LastNamecall = Me.LastName
            .FirstNamecall = Me.FirstName
            .DOBcall = Me.DOB
            .PatientIDcall = Me.ID
        End With
Exit Sub
ErrorHandler:
MsgBox "Duplicate MRN Found", vbInformation, "Error"
End Sub

Code for Save Record:

Private Sub cmdSaveRecord_Click()
On Error GoTo ErrorHandler

DoCmd.RunCommand acCmdSaveRecord
MsgBox "Record Saved", vbInformation, "Save"
Exit Sub
ErrorHandler:
MsgBox "Duplicate MRN Found", vbInformation, "Error"
End Sub

SOOOO, my question is, how do I get it to, once MRN, LAST, FIRST, and DOB are entered on the subform, auto save the record so it pulls a primary key ID, and in return, update the main form with the info so it can drive the other subform and activity buttons? Because if I add refresh or requery to the save button it pulls my default TEST patient.

Would I need to add code so that when NEW ACTIVITY LOG, SAVE RECORD, or NEW PATIENT is clicked it pulls from the subform to the mainform, THEN opens the items? Would I need to add that to the on click event for the SLC tab?

As always, you are all wonderful.

Just an FYI, coding and databases aren't my job. I am in project management and process improvement but I am putting this together as a stopgap until our tech people can find me a good commercial product outside our typical electronic health record. I code only out of nessecity and haven't for years so I REALLY appreciate the help from this community.


EDIT:
I added:

If Me.Dirty = True Then
   Me.Dirty = False
End If

to my After Update for MRN which IS pulling a record ID to the subform.


So, I added code to my save button:

Private Sub cmdSaveRecord_Click()
On Error GoTo ErrorHandler

DoCmd.RunCommand acCmdSaveRecord
Dim strFrmName As String
strFrmName = "PatientAllSubs"

    DoCmd.OpenForm strFrmName
        With Forms(strFrmName)
            Me.MRN = Me!PatientSubform.Form!MRN
            Me.LastName = Me!PatientSubform.Form!LastName
            Me.FirstName = Me!PatientSubform.Form!FirstName
            Me.DOB = Me!PatientSubform.Form!DOB
            Me.ID = Me!PatientSubform.Form!ID
        End With

MsgBox "Record Saved", vbInformation, "Save"
Exit Sub
ErrorHandler:
MsgBox "Duplicate MRN Found", vbInformation, "Error"
End Sub

I don't want to use the OPENFORM because it will open the default test patient. I just need to update the main form info and keep the subform record open.

 

[GaP42]

You said

SO, the issue is, when you create a NEW patient by clicking "New Patient Entry" from either the search subform or the NEW PATIENT button the the [PatientAllSubs] form, I am wanting it to update the MAIN form ID, MRN, LastName, FirstName, and DOB fields from the subform to the main form as they are being typed, but it would also have to save the record in order to pull an autokey from the table. Because without updating the MAIN form, if I try to click on the SLC tab or go to a NEW ACTIVITY LOG it won't pull the data with it. Even if I save the record.

You are expecting the main form (is this the Oncology Nurse Navigation form) to show the new patient record AS THE RECORD IS BEING ADDED? Until the new patient record is added (and entries validated) you cannot expect it to appear in the search list of patients and the autokey for the record to have been created.
You need to create the record then refresh your search list.

 

[Gasman]

I don't want to use the OPENFORM because it will open the default test patient. I just need to update the main form info and keep the subform record open.

You can open a form to any record you like (as long as it exists?)

What scares me however, is that such an important system like you show, is being created by amateurs/novices.
No disrespect, but this is a system so called professionals should be creating, and even then, they can get it wrong.

If your links are not correct, you could be given the wrong treatment to the wrong patient, etc.
There is so much that could go wrong, and with potential serious consequences.

 

[Pat Hartman]

@bonzitre It is dangerous to open a form to a record the user did not specifically request. Bind your forms to queries with selection criteria. They will open to a new record. Then the user can use your search feature to find the record he wants to work with.

 

[gemma-the-husky]

Really the main form is independent of the subform, in a way.

If you have a patient, enter the patient's details on the main form. Enter the patient's appointment data on a sub form, but you don't need to re-enter the patient data. That's already there as part of the patient record that's related to the sub form.

You design the data structure accordingly, and then the form design can follow the data design, in a harmonious fashion.