I am trying to produce a message box if a user enters, through a form, a duplicate Social Security # in the Client table. (Named "tbl Client" I know spaces aren't good....I inherited this thing!) I want the message box to pop up on the Before Update event of the SSNum control rather than waiting until the user enters all of the client information and then tries to save the entire record.
The field is [SS#], the control is SSNum.
Here is the code:
Private Sub SSNum__BeforeUpdate(Cancel As Integer)
Dim Msg As String, Style As Integer, Title As String
Dim DL As String, Criteria As String
DL = vbNewLine & vbNewLine
Criteria = "[SS#]='" & Me!SSNum & "'"
If Not IsNull(DLookup("[SS#]", "tbl Client", Criteria)) Then
Msg = "Social Security # Already Exists in Database!" & DL & _
"Duplicates are not allowed . . ." & DL & _
"Reconsider your data entry and try again . . ."
Style = vbCritical + vbOKOnly
Title = "Duplication SS# Error! . . ."
MsgBox Msg, Style, Title
Cancel = True
End If
End Sub
Any ideas? I'm not the greatest at coding. Thanks!
The field is [SS#], the control is SSNum.
Here is the code:
Private Sub SSNum__BeforeUpdate(Cancel As Integer)
Dim Msg As String, Style As Integer, Title As String
Dim DL As String, Criteria As String
DL = vbNewLine & vbNewLine
Criteria = "[SS#]='" & Me!SSNum & "'"
If Not IsNull(DLookup("[SS#]", "tbl Client", Criteria)) Then
Msg = "Social Security # Already Exists in Database!" & DL & _
"Duplicates are not allowed . . ." & DL & _
"Reconsider your data entry and try again . . ."
Style = vbCritical + vbOKOnly
Title = "Duplication SS# Error! . . ."
MsgBox Msg, Style, Title
Cancel = True
End If
End Sub
Any ideas? I'm not the greatest at coding. Thanks!