Access 2007 create new record will not update form

0
votes

I am setting up a database containing patient information. Right now, users can navigate to a particular patients information (Patient Home Page) using a search/confirm form. The Home Page has links to other forms with additional patient information. The code works when users search for a patient that was put in from the beginning (aka the "patients" I put in as test people at the beginning will show up and the code behaves as it is supposed to). However, when I try to add a new patient, the confirmation form will no longer work. The new record is created in the patient list table and the search form will find the new patient, but when I click on the continue button on the confirmation form, the Patient Home Page pulls up as a new (blank) record. The code only breaks with new patients.

I am also having an issue creating a new record in the "Equipment Log" form when I add a new patient to the home page form. I have a 1-to-1 relationship between the equipment log and patient home page, with pt ID being the primary key for both tables. When I add a new patient through the "pt home page", I need to create a linked record in the equipment log that is connected via the pt ID. So far, I have tried code similar to that found below and other similar things, but nothing seems to work.

Code for the search and confirmation pages are below, as well as part of the code for the patient home page (although I don't know that that code is very useful). Any help is appreciated, I've been stuck on this for awhile.

Patient Search Page:

Private Sub Patient_Search_Click()
On Error GoTo Patient_Search_Click_Err

DoCmd.OpenForm "Confirmation", acNormal, "", "", acReadOnly, acNormal


Patient_Search_Click_Exit:
Exit Sub

Patient_Search_Click_Err:
MsgBox Error$
Resume Patient_Search_Click_Exit

End Sub

Private Sub add_new_Click()
On Error GoTo add_new_Click_Err

' _AXL:<?xml version="1.0" encoding="UTF-16" standalone="no"?>
' <UserInterfaceMacro For="Patient Search" Event="OnClick"   xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application" xmlns:a="http://schemas.microsoft.com/office/accessserv
' _AXL:ices/2009/11/forms"><Statements><Action Name="OpenForm"><Argument Name="FormName">Confirmation</Argument><Argument Name="DataMode">Read Only</Argument></Action></Statements></UserInterfaceMacro>
DoCmd.OpenForm "Patient Home Page"
DoCmd.GoToRecord , , acNewRec

add_new_Click_Exit:
Exit Sub

add_new_Click_Err:
MsgBox Error$
Resume add_new_Click_Exit

End Sub

Patient Confirmation Page (*Note: I edited this and added a breakpoint at the debug.print. When I run the command nothing prints and now nothing will open)

Private Sub confirm_Click() 
testid = [Patient List_ID]
Debug.Print 
DoCmd.OpenForm "Patient Home Page", acNormal, "", "[ID]=" & "'" & [Patient List_ID] & "'" 
End Sub

Patient Home Page:

Private Sub Form_Current()
If Me.NewRecord Then
Me.equipment_log.Visible = False
Me.echos.Visible = False
Me.open_logs.Visible = False
Me.openadvisory.Visible = False
Me.save.Visible = True
Me.edit.Visible = False
Me.cancelupdate.Visible = True
Last_Name.Locked = False
First_Name.Locked = False
Middle_name.Locked = False
Device.Locked = False
Patient_List_ID.Locked = False
MRN.Locked = False
DOB.Locked = False
Implant_Date.Locked = False
Transplant_Goal.Locked = False
Status.Locked = False
Caregiver.Locked = False
Address.Locked = False
City.Locked = False
State.Locked = False
Zipcode.Locked = False
Phone_1.Locked = False
Phone_2.Locked = False
Phone_3.Locked = False
Else
Me.equipment_log.Visible = True
Me.echos.Visible = True
Me.open_logs.Visible = True
Me.openadvisory.Visible = True
Me.save.Visible = False
Me.edit.Visible = True
Me.cancelupdate.Visible = False
Last_Name.Locked = True
First_Name.Locked = True
Middle_name.Locked = True
Device.Locked = True
Patient_List_ID.Locked = True
MRN.Locked = True
DOB.Locked = True
Implant_Date.Locked = True
Transplant_Goal.Locked = True
Status.Locked = True
Caregiver.Locked = True
Address.Locked = True
City.Locked = True
State.Locked = True
Zipcode.Locked = True
Phone_1.Locked = True
Phone_2.Locked = True
Phone_3.Locked = True
End If
End Sub

Create new record in equipment log from new entry in patient list (inside of if statement for create new record)

Dim crit As String
crit = [Patient List_ID]
DoCmd.OpenForm "Patient Equipment Log"
DoCmd.GoToRecord , , acNewRec
Forms![Patient Equipment Log]![Patient Equipment.ID] = crit
2
vbams-access
Can you please expand on the second question regarding the equipment log? Such as exactly what doesn't work and what you have tried up to this point. – Newd
I can't really tell you exactly what I've tried because I've tried a lot of different things from other posts/sites and delete it when it doesn't work. Editing original question to expand more on the question. – Alisa
Hopefully that helps, let me know if I can clarify more. Out of everything I have tried, that approach seemed to make the most sense. – Alisa

2 Answers

1
votes

From the sounds of it your new patients aren't being saved before you try to open up a form to view them. So basically you create a new patient and tell a second form to view that patient before they ever actually "exist" in the database's eyes. 

Private Sub Patient_Search_Click()
On Error GoTo Patient_Search_Click_Err

    DoCmd.Save
    DoCmd.OpenForm "Confirmation", acNormal, "", "", acReadOnly, acNormal


Patient_Search_Click_Exit:
    Exit Sub

Patient_Search_Click_Err:
    MsgBox Error$
    Resume Patient_Search_Click_Exit

End Sub
1
votes

I finally got it...apparently somewhere I had the equipment and patient lists related by MRN. I added an if statement under the "save" command on the patient home page to check if there was a record with an identical MRN in the patient equipment list. If not, I created a new record.

Private Sub save_Click()
DoCmd.save
Dim crit As String
crit = [Patient List_ID]
DoCmd.OpenTable "Patient Equipment"
qcrit = DLookup("[ID]", "Patient Equipment", "[Patient List_ID]=" & "'" & crit & "'")
If StrComp(crit, qcrit) = 0 Then
    DoCmd.Close
Else
    DoCmd.OpenForm "Patient Equipment Log"
    DoCmd.GoToRecord , , acNewRec
    Forms![Patient Equipment Log]![MRN] = Forms![Patient Home Page]![MRN]
    Forms![Patient Equipment Log]![Patient Equipment.ID] = Forms![Patient Home Page]![Patient List_ID]
    Forms![Patient Equipment Log]![Implant Date] = Forms![Patient Home Page]![Implant Date]
    DoCmd.Close
End If