473,324 Members | 2,400 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Code for Duplicate Records Warning

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
6 1797
"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

"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
> >
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
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
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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Hafeez | last post by:
I am having real trouble compiling this code http://www.cs.wisc.edu/~vganti/birchcode/codeHier/AttrProj.tgz The attachment shows errors when compiled using the current version of g++ in a...
9
by: Catherine Jo Morgan | last post by:
Can I set it up so that a certain combination of fields can't contain the same entries, on another record? e.g. a combination of FirstName/LastName/address? Or FirstName/LastName/phone? Or...
8
by: Ray | last post by:
I have a data input form and need to automatically duplicate the existing record as a new record by clicking a button. The main purpose to duplicate the record is that the new record is very...
2
by: news | last post by:
I just upgraded to PHP 4.4.2 on my Slackware 10.2 system. And Apache/mySQL/PHP all work great through a browser. No errors. But when I try to run a PHP script through the command line, which I...
2
by: Carroll | last post by:
I'm looking for a way in SQL to find duplicate records in a single table, that are the same based on 3 columns, regardless of what is in the other columns in the duplicate records. I would like to...
0
by: Johan P | last post by:
Having just compiled DBG 2.15.1 and trying to use it with PHP (cli) give some some problem. Verions: DBG 2.15.1 PHP 4.4.4 (cli) It works fine compiled with the Apache module but when I try...
9
by: jjstevens | last post by:
I am trying to set up a message box warning (or open to some sort of other warning) when a duplicate record is entered. I do want to allow duplicate records but I am looking to warn when it does...
2
by: nethravathy | last post by:
Hi, The following table namely elcbtripselect contains 5147 records.I want to know wether this table contains duplicate records or not. I tried with following query 1)SELECT...
2
by: nomvula | last post by:
hi guys i need some help to duplicate records on my form datasheet: here's the example of my form results: ClientLookup DateCaptured ForecastDate Description ForecastQuantity Forecast Actual UJ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.