check for duplicates and missing data

[Guest]

In a form BeforeUpdate event I'd like to check for missing data and duplicate
data and pass back custom messages. The code (below) for missing data works
fine but I'd like some guidance on what additional code is needed to check
for duplicate data. The four fields (Account_Number, Result, DateofService,
and TestID) are all set as primary keys.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strMsg As String
'check to see that all data are filled in'

If IsNull(Me.Account_Number) Then
Cancel = True
strMsg = strMsg & "Account Number field required." & vbCrLf
End If

If IsNull(Me.Result) Then
Cancel = True
strMsg = strMsg & "Result field required." & vbCrLf
End If

If IsNull(Me.DateofService) Then
Cancel = True
strMsg = strMsg & "Date of Service field required." & vbCrLf
End If
If IsNull(Me.TestID) Then
Cancel = True
strMsg = strMsg & "Test ID field required." & vbCrLf
End If

'etc for other fields.

If Cancel Then
strMsg = strMsg & vbCrLf & "Complete the record, or press <Esc>
twice to undo."
MsgBox strMsg, vbExclamation, "Invalid record"
End If


End Sub

 

[Guest]

Steve -

I understand what you are trying to do, but not the question as a whole.
What are you trying to check as duplicate? If new entries on this form are
duplicates of data already in the table or ??? If this is the case are you
checking so you can provide custome messages as opposed to the error messages
that Access will give since primary keys can't be duplicated?

 

[Guest]

Thanks for your reply. That's correct, the new entries would be duplicating
data already in the table. I was able to trap the error in the on click
event for the save command button using the code below. It seems to be
accomplishing what I set out to do. Is there a better approach to accomplish
this?

Err_Enter_Result_Click:
ErrorNumber = Err.Number
Beep
Select Case ErrorNumber
Case 3022
Response = acDataErrContinue ' Don't display the default
message
MsgBox "The data you are entering already exists. Press <esc>
twice to undo."
End
End Select