Hello,
I have two linked Tables 'tblPatients' and 'tblDSA' and two continues forms 'frmPatients' and 'frmDSA'. When I create a new patient via 'frmPatient', I would like to add a new record for that patient in 'frmDSA' without closing the form. The record source for the frmDSA is a query that includes fields from both tables.
On 'frmPatients' next to each record there is a button 'Enter_DSA_Record' that should do all of the following:
(1) saves a new record to 'tblPatients' if a new Patient has been entered. (2) opens 'frmDSA' to display related records for that Patients.
Here is what happens:
Please suggest how to fix the If statement that checks if this record already exists in 'tblPatients'.
'If this patient is new'
Do I need to use DAO.Recordset and DAO.Recordset2 to add records to two tables in one sub routine?
Thanks,
Here is the code:
I have two linked Tables 'tblPatients' and 'tblDSA' and two continues forms 'frmPatients' and 'frmDSA'. When I create a new patient via 'frmPatient', I would like to add a new record for that patient in 'frmDSA' without closing the form. The record source for the frmDSA is a query that includes fields from both tables.
On 'frmPatients' next to each record there is a button 'Enter_DSA_Record' that should do all of the following:
(1) saves a new record to 'tblPatients' if a new Patient has been entered. (2) opens 'frmDSA' to display related records for that Patients.
Here is what happens:
Please suggest how to fix the If statement that checks if this record already exists in 'tblPatients'.
Code:
If CurrentDb.OpenRecordset("Select count(*) from tblPatients where LABCODE=" & Forms!frmPatients!LABCODE & ";").Fields(0) < 0 Then
Do I need to use DAO.Recordset and DAO.Recordset2 to add records to two tables in one sub routine?
Thanks,
Here is the code:
Code:
Private Sub Enter_DSA_Record_Click()
Dim db As DAO.Database
Dim PatientTable As DAO.Recordset
Dim DSAtable As DAO.Recordset2
Dim errMsg As String
Dim errData As Boolean
Dim i As Integer
Dim x As Integer
Dim errorArray(0 To 1) As String 'Array to hold the error messages so we can 'use them if needed.
'need to check if a record for Patient already exists before creating a new patient
If CurrentDb.OpenRecordset("Select count(*) from tblPatients where LABCODE=" & Forms!frmPatients!LABCODE & ";").Fields(0) < 0 Then
MsgBox ("This Patient is new")
If Me.LABCODE.Value = "" Then
errorArray(0) = "Must Enter Labcode."
errData = True
End If
If Me.LastName.Value = 0 Then
errorArray(1) = "Must Enter Patient Number"
errData = True
End If
'MsgBox "errData = " & errData
If errData = True Then
i = 0
x = 0
For i = 0 To 1
If errorArray(i) <> "" Then
If x > 0 Then
errMsg = errMsg & vbNewLine & errorArray(i)
Else
errMsg = errorArray(i)
x = x + 1
End If
End If
Next i
MsgBox errMsg & vbNewLine & "Please try again."
errMsg = ""
Me.LABCODE.SetFocus
Exit Sub
End If
Set db = CurrentDb()
Set PatientTable = db.OpenRecordset("tblPatients")
With PatientTable
.AddNew
!LABCODE = Me.LABCODE.Value
!LastName = Me.LastName.Value
!FirstName = Me.FirstName.Value
!MRN = Me.MRN.Value
!MRNTwo = Me.MRN2.Value
Debug.Print Me.MRN.Value
'!CPI#2 = Me.MRN2.Value
!Kidney = Me.cbKidney.Value
!Heart = Me.cbHeart.Value
!Lung = Me.cbLung.Value
!Liver = Me.cbLiver.Value
!Pancreas = Me.cbPancreas.Value
!DateLogged = Format(Date, "MM/DD/YY")
.Update
End With
'End If
Set DSAtable = db.OpenRecordset("tblDSA")
With DSAtable
.AddNew
!LABCODE = Me.LABCODE.Value
.Update
End With
'Let the user know it worked.
MsgBox "This patient has been added successfully.", vbOKOnly
DoCmd.OpenForm "DSAfrm", _
WhereCondition:="LABCODE=" & Me.LABCODE
Else
'if record exists show correspondin records in 'tblDSA'
DoCmd.OpenForm "DSAfrm", _
WhereCondition:="LABCODE=" & Me.LABCODE
End If
End sub