By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 1,709 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

Code for Duplicate Records Warning

P: n/a
Can anyone advise me what is wrong with this code? I want it to tell me if
I am entering a duplicate record. However, I always get the "Dupe" error
message, whether I have entered a duplicate or not.

The field REGISTRATION1 is in the form [Aircraft Registrations] and
Registration is the Control Source in the table [Aircraft Registrations]
(Can't remember why I named the two fields differently, but there was a
reason at the time)
thanks
Peter

Private Sub REGISTRATION1_AfterUpdate()

Dim strWhere As String

With Me.REGISTRATION1
If .Value = .OldValue Then
'do nothing
Else
strWhere = "Registration1 = """ & .Value & """"
If Not IsNull(DLookup("Registration", "[Aircraft
Registrations]", strWhere)) Then
MsgBox "Dupe!"
End If
End If
End With
End Sub
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
"Peter" <peter@_nospam_exeter23.freeserve.co.uk> wrote in
news:10*************@corp.supernews.com:
Can anyone advise me what is wrong with this code? I want it
to tell me if I am entering a duplicate record. However, I
always get the "Dupe" error message, whether I have entered a
duplicate or not.

The field REGISTRATION1 is in the form [Aircraft
Registrations] and Registration is the Control Source in the
table [Aircraft Registrations] (Can't remember why I named the
two fields differently, but there was a reason at the time)
thanks
Peter

Private Sub REGISTRATION1_AfterUpdate()

Dim strWhere As String

With Me.REGISTRATION1
If .Value = .OldValue Then
'do nothing
Else
strWhere = "Registration1 = """ & .Value & """"
If Not IsNull(DLookup("Registration", "[Aircraft
Registrations]", strWhere)) Then
MsgBox "Dupe!"
End If
End If
End With
End Sub

strWhere = "Registration1 = """ & .Value & """"
should be
strWhere = "Registration = """ & .Value & """"
--
Bob Quintal

PA is y I've altered my email address.
Nov 13 '05 #2

P: n/a

"Peter" <peter@_nospam_exeter23.freeserve.co.uk> wrote in message
news:10*************@corp.supernews.com...
Can anyone advise me what is wrong with this code? I want it to tell me if I am entering a duplicate record. However, I always get the "Dupe" error
message, whether I have entered a duplicate or not.

The field REGISTRATION1 is in the form [Aircraft Registrations] and
Registration is the Control Source in the table [Aircraft Registrations]
(Can't remember why I named the two fields differently, but there was a
reason at the time)
thanks
Peter

Private Sub REGISTRATION1_AfterUpdate()

Dim strWhere As String

With Me.REGISTRATION1
If .Value = .OldValue Then
'do nothing
Else
strWhere = "Registration1 = """ & .Value & """"
I don't think this is written correctly . . . but am not sure. It looks
like you're trying to construct a string that will evaluate to
"Registration1 = ########", where ######## is some unknown number (or maybe
characters, I've no idea). Is that actually what the contents of the
Registration column of table [Aircraft Registrations] looks like?
If Not IsNull(DLookup("Registration", "[Aircraft
Registrations]", strWhere)) Then
Let's see if I understand correctly. If DLookup returns Data, IsNull
evaluates True, which causes the If Not to evaulate False, falling out. If
DLookup returns Null, IsNull evaluates False, which cases the If Not to
evaluate True, hitting the MsgBox?

The question then becomes, does strWhere actually have data in it when it
hits DLookup, and (based on the above), does that data actually look like
what is in the table?
Sincerely,

Chris O.


MsgBox "Dupe!"
End If
End If
End With
End Sub

Nov 13 '05 #3

P: n/a
> >
With Me.REGISTRATION1
If .Value = .OldValue Then
'do nothing
Else
strWhere = "Registration1 = """ & .Value & """"
I don't think this is written correctly . . . but am not sure. It

looks like you're trying to construct a string that will evaluate to
"Registration1 = ########", where ######## is some unknown number (or maybe characters, I've no idea). Is that actually what the contents of the Registration column of table [Aircraft Registrations] looks like?


Nevermind, of course a DLookup is supposed to appear that way.
Nov 13 '05 #4

P: n/a
Peter wrote:
Can anyone advise me what is wrong with this code? I want it to tell me if
I am entering a duplicate record.


Why don't you just take the field in question and put an index on it
with no duplicates permitted? YOu can then trap the error which would
be, I think, 3022. In the on error event of your form, do this:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Select Case DataErr

Case 3022 'duplicate value in index/PK

MsgBox "You're an idiot, you've already entered this " & _
"registration number!", vbExclamation, "Twit!"

Response = acDataErrContinue

Case Else

Response = acDataErrDisplay 'shows normal error message

End Select

End Sub

It truly is the least worrisome approach to have as much of your data
constraints as possible as part of your table design (in this case,
indexing the registration number). If you have to depend a lot on code
in your forms to constrain your data, you will have loops holes and
bugs, plus anyone who, for whatever reason, may have to enter data
directly to the tables (perhaps you, as the system administrator) is
bound to forget about form based constraints eventually and end up with
bad data. With constraints on your tables, the limitations are
fundamental to your design.

Thus Spake E.F.Codd
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #5

P: n/a
Thanks.
Yes you were right. Works fine now.
Peter
"Bob Quintal" <rq******@sPAmpatico.ca> wrote in message
news:1106609575.8c0f86030d5a1589fa4a83efd6f18332@t eranews...
"Peter" <peter@_nospam_exeter23.freeserve.co.uk> wrote in
news:10*************@corp.supernews.com:
Can anyone advise me what is wrong with this code? I want it
to tell me if I am entering a duplicate record. However, I
always get the "Dupe" error message, whether I have entered a
duplicate or not.

The field REGISTRATION1 is in the form [Aircraft
Registrations] and Registration is the Control Source in the
table [Aircraft Registrations] (Can't remember why I named the
two fields differently, but there was a reason at the time)
thanks
Peter

Private Sub REGISTRATION1_AfterUpdate()

Dim strWhere As String

With Me.REGISTRATION1
If .Value = .OldValue Then
'do nothing
Else
strWhere = "Registration1 = """ & .Value & """"
If Not IsNull(DLookup("Registration", "[Aircraft
Registrations]", strWhere)) Then
MsgBox "Dupe!"
End If
End If
End With
End Sub

strWhere = "Registration1 = """ & .Value & """"
should be
strWhere = "Registration = """ & .Value & """"
--
Bob Quintal

PA is y I've altered my email address.

Nov 13 '05 #6

P: n/a
"Peter" <peter@_nospam_exeter23.freeserve.co.uk> wrote in
news:10*************@corp.supernews.com:
Thanks.
Yes you were right. Works fine now.
Peter
See Tim Marshal's reply. He makes a valid point about table level
constraints.

Bob
"Bob Quintal" <rq******@sPAmpatico.ca> wrote in message
news:1106609575.8c0f86030d5a1589fa4a83efd6f18332@t eranews...
"Peter" <peter@_nospam_exeter23.freeserve.co.uk> wrote in
news:10*************@corp.supernews.com:
Can anyone advise me what is wrong with this code? I want
it to tell me if I am entering a duplicate record. However,
I always get the "Dupe" error message, whether I have
entered a duplicate or not.

The field REGISTRATION1 is in the form [Aircraft
Registrations] and Registration is the Control Source in the
table [Aircraft Registrations] (Can't remember why I named
the two fields differently, but there was a reason at the
time) thanks
Peter

Private Sub REGISTRATION1_AfterUpdate()

Dim strWhere As String

With Me.REGISTRATION1
If .Value = .OldValue Then
'do nothing
Else
strWhere = "Registration1 = """ & .Value & """"
If Not IsNull(DLookup("Registration", "[Aircraft
Registrations]", strWhere)) Then
MsgBox "Dupe!"
End If
End If
End With
End Sub

strWhere = "Registration1 = """ & .Value & """"
should be
strWhere = "Registration = """ & .Value & """"
--
Bob Quintal

PA is y I've altered my email address.



--
Bob Quintal

PA is y I've altered my email address.
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.