alert when i double up information

  • Thread starter Thread starter 123leannep
  • Start date Start date
1

123leannep

i am entering addresses in a column and i want to be informed when i have
doubled up on the address.
 
i am entering addresses in a column and i want to be informed when i have
doubled up on the address.
Click to expand...

You'll need to do this in a Form; table datasheets don't have any usable
events (yet) to allow this.

In a Form you can use the address textbox's BeforeUpdate event to detect a
duplicate; sample code might be

Private Sub txtAddress_BeforeUpdate(Cancel as Integer)
Dim rs As DAO.Recordset
Dim iAns As Integer ' variable for user's response
Set rs = Me.RecordsetClone
rs.FindFirst "[Address] = """ & Me!txtAddress & """"
If Not rs.NoMatch Then
iAns = MsgBox("Duplicate address. Click Yes to add it anyway," _
" No to jump to the duplicate, Cancel to erase this record and start over",
vbYesNoCancel)
Select Case iAns
Case vbYes
' do nothing, just let it be added
Case vbNo
Cancel = True
Me.Undo
Me.Bookmark = rs.Bookmark ' jump to found record
Case vbCancel
Cancel = True
Me.Undo
End Select
End If
End Sub
 
You can only run code from a form in Access, not tables or queries. You can
however use a datasheet view in the form to simulate working in a table. In
the BeforeUpdate event of the address field's textbox, do a DLookup of the
address. If found cancel the event. Here's some air code:

Sub txtAddress_BeforeUpdate(Cancel As Integer)
Dim x
x = DLookUp("[Address]","[tblWhatever]","[Address] = """ & Form![txtAddress]
& """)
If Not IsNull(x) Then
MsgBox "oops a dupe"
Cancel = True
End If
End Sub
 
Back
Top