Thread: Access - VBA - If record Exsists, find and ask to Edit

Hello,

I'm looking for some logic on my Access form. Currently, I have "email" set as a primary key so when someone enters a new email address it pops up saying the record already exists. I have a simple error check VBA message for this currently which brings up the message box.

My problem is from that point on, even if you blank out the email field it keeps giving you an error until you enter in something unique. And I have to manually remove it from the table.

I would like to expand the logic to point out if the record already exists based on the email field, and if so, ask the user if they would like to edit the the information. If Yes is selected, have it bring up the record they tried to enter inside the forum so they can change name or address information.

If No is selected, then do nothing and allow the user to enter a different email if desired.

I tried writing this code - but it doesn't really work:

Private Sub Command18_Click()

On Error GoTo Err_chk

DoCmd.GoToRecord , , acNewRec

Err_chk:
If Err.Number = 2105 Or 3022 Then
If MsgBox("Record already exsists!" & vbCr & vbCr & "Do you wish to update it?", vbYesNo) = vbYes Then

DoCmd.RunCommand acCmdDeleteRecord
DoCmd.FindRecord Text0.Value, , True, , True, acCurrent
Else
DoCmd.RunCommand acCmdDeleteRecord
End If
End If

End Function

Thanks in advance!