Advanced Search

Check for duplicate value (text)

/ Published in: Visual Basic
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. Private Sub LastName_BeforeUpdate(Cancel As Integer)
  2. On Error GoTo Err_Handler
  3.  
  4. Dim LN As String
  5. Dim stLinkCriteria As String
  6. Dim rsc As DAO.Recordset
  7.  
  8. Set rsc = Me.RecordsetClone
  9.  
  10. LN = Me.LastName.Value
  11. stLinkCriteria = "[LastName]=" & "'" & LN & "'"
  12.  
  13.     'Check table for duplicate
  14.     If DCount("LastName", "tblPatients", stLinkCriteria) > 0 Then
  15.         'Undo duplicate entry
  16.         Me.Undo
  17.         'Message box warning of duplication
  18.         MsgBox "The last name " & LN & " is already in use." & _
  19.         Chr(13) & Chr(13) & "Please check to see if this patient has already been entered. " & _
  20.         "If not, then assign the patient a different number.", vbInformation, "Duplicate Last Name"
  21.         'Go to the duplicate record
  22.         'rsc.FindFirst stLinkCriteria
  23.         'Me.Bookmark = rsc.Bookmark
  24.     End If
  25.  
  26. Set rsc = Nothing
  27.  
  28. Exit_Handler:
  29.     Exit Sub
  30. Err_Handler:
  31.     If Err.Number = 94 Then
  32.         ' Invalid use of Null error
  33.         Resume Exit_Handler
  34.     Else
  35.     End If
  36. End Sub

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.