Removing data from a field which violates the primary key

  • Thread starter Thread starter LMISSENDA
  • Start date Start date
L

LMISSENDA

Good Evening,
I am programming a db for recording patient reviews. I have a form with a
subform which asks the user to enter the Patient's ID. I want to alert the
user if they enter an ID that already exists in the PTInfo table. If the user
chooses to return to the existing patient, I want the program to change the
form from DataEntry= Yes to DataEntry= No and go to the existing patient's
record.
I was able to write an On Error code when the PTID fails the the primary key
violation. In this code I was able to change the DataEntry property of the
form to No and find the appropriate record. My problem is that I can't remove
the open record which violates the primary key to remove the error before the
code can proceed.

I tried attaching the code to the On Exit and AfterUpdate events.

My code:
On Error GoTo Err_txtPTID_AfterUpdate
Dim PTID As String
Dim ResponsePTID as String

PTID= Me.txtPTID
DoCmd.DoMenuItem acFormBar, acEditMenu,10,,acMenuVer70
Exit_txtPTID_AfterUpdate:
Exit Sub
Err_txtPTID_AfterUpdate:
If Err.Number = 3022 then
ResponsePTID=MsgBox("Do you want to enter a second review for this
patient?",vbYesNo+vbQuestion,"Duplicate PTID")
If ResponsePTID= vbYes Then

Me.txtPTID.Value= Me.txtPTID.OldValue
' this returns a null value which fails
Me.DataEntry= False
DoCmd.FindRecord PTID
Else
'Do nothing and let the user change the ID
End IF
Else
MsgBox Err.Description
End If
End Sub

Any suggestions?

Thanks in advance

(e-mail address removed)
 
I fixed my intial problem using DoCmd.RunCommand acCmdUndo

It works great for existing patients, but now when the user enters a new
patient a find dialog boxs pops up.

Thanks
 
Back
Top