Duplicate Alert: Help!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table called "TRACKING" (My primary key uses autonumber)
The form that uses this table is called "PERMIT TRACKING"
On the form "PERMIT TRACKING", I have a text box "Permit Number" (indexed
with duplicates - this is the way I want it)
However, I would like for the user to be alerted that the entry already
exists: do you want to continue? if a duplicate is entered into that field
on the attached form. I read the answer that you provided to jennyhuffman71,
but I'm having difficulty with the code.
In the answer provided to her, it references a query. Is that just a name,
or is there an actual query? Please forgive my nievity, but I don't know
anything about code, I'm kind of on my own to figure this out. Any help
would be greatly appreciated.
Click to expand...

This is what I have so far:

Private Sub “PERMIT NUMBERâ€_BeforeUpdate(Cancel As Integer)
 
Several issues:

1. The "Private Sub" line does not look right with the quotes. Right-click
the control on your form, and choose Properties. On the Event tab, set the
BeforeUpdate property to:
[Event Procedure]
Then click the Build button (...) beside this. Access will write this line
for you.

2. Your field name has spaces in it. You must enclose in square brackets.

3. A record is not a duplicate of itself (e.g. if the user is editing an
existing record, and changes it back to the original value).

4. If you open the Tracking table in design view, what type of field is
Permit Number? If it is Number (not Text), drop the extra quotes, i.e.:
strWhere = "[Permit Number] = " & Me.[Permit Number]

The procedure will end up something like this:

Dim strWhere As String
Dim strMsg As String
Dim varResult As Variant

If Me.[Permit Number] = Me.[Permit Number].OldValue Then
' do nothing
Else
strWhere = "[Permit Number] = """ & Me.[Permit Number] & """
varResult = DLookup("[Permit Number]", "Tracking", strWhere)
If Not IsNull(varResult) Then
strMsg = "Duplicate. Proceed anyway?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2) <> vbYes Then
Cancel = True
End If
End If
End If
 
Back
Top