Duplicate Entry

  • Thread starter Thread starter SamDev
  • Start date Start date
S

SamDev

In a form I have added a field named "record number" that is the primary key
so duplicates are not allowed for the field. I want if the a user enters a
duplicate record number a message to pop-up saying the record is dup. But
for some reason the message doesn't come up until after all the fields in
the form have been filled-in and I go to a new record. I want the message to
come up immediately after I have filled in the record number field and it's
a dup.

Any ideas?

Thx!
 
Hi,
the primary key value of a table should have no meaning to you or your
users. It is only for access to work internanlly correct. If you want a
meaningful number you want to display and use then create an extra field for
that. The primary key should be of type autonumber since it will ensure that
each record is truely unique.
With that said you can then use a dlookup or dcount function on the before
update event to check if this data has been entered before or not. For a
complete tutorial check this:
http://www.databasedev.co.uk/duplicates.html
HTH
Good luck
 
I have made a different field the primary key so the Report Number field is
no longer a primary key. The Report Number field is a text data type. I
looked at the code you suggested and have tried (see below) and the message
box doesn't open when a dup is inserted. I don't need it to take me to the
record as the original code does so I haven't included that portion - I'm
rather new at this so I might be missing something in my code (see below).

The table that contains the field ReportNumber is called WIP. Any help would
be appreciated. Thx!!

Private Sub ReportNumber_BeforeUpdate(Cancel As Integer)
Dim RN As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

RN = Me.ReportNumber.Value
stLinkCriteria = "[reportnumber]=" & " ' " & RN & " '"

'Check WPI Table for duplicate Report Number.
If DCount("reportnumber", "wpi", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message Box warning of duplication
MsgBox "Warning Report Number " _
& RN & " has already been entered."
End If
Set rsc = Nothing

End Sub

Thx!!!
 
Hi,
try:

RN = Me.ReportNumber.Value
stLinkCriteria = "[reportnumber]='" & RN & "'"

You might need to play around with the syntax depending on your datatype
(since it is text and not numeric).
Have a look here for more dlookup/dcount samples:
http://support.microsoft.com/?kbid=208786

HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de


SamDev said:
I have made a different field the primary key so the Report Number field is
no longer a primary key. The Report Number field is a text data type. I
looked at the code you suggested and have tried (see below) and the message
box doesn't open when a dup is inserted. I don't need it to take me to the
record as the original code does so I haven't included that portion - I'm
rather new at this so I might be missing something in my code (see below).

The table that contains the field ReportNumber is called WIP. Any help would
be appreciated. Thx!!

Private Sub ReportNumber_BeforeUpdate(Cancel As Integer)
Dim RN As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

RN = Me.ReportNumber.Value
stLinkCriteria = "[reportnumber]=" & " ' " & RN & " '"

'Check WPI Table for duplicate Report Number.
If DCount("reportnumber", "wpi", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message Box warning of duplication
MsgBox "Warning Report Number " _
& RN & " has already been entered."
End If
Set rsc = Nothing

End Sub

Thx!!!

freakazeud said:
Hi,
the primary key value of a table should have no meaning to you or your
users. It is only for access to work internanlly correct. If you want a
meaningful number you want to display and use then create an extra field
for
that. The primary key should be of type autonumber since it will ensure
that
each record is truely unique.
With that said you can then use a dlookup or dcount function on the before
update event to check if this data has been entered before or not. For a
complete tutorial check this:
http://www.databasedev.co.uk/duplicates.html
HTH
Good luck
Click to expand...
Click to expand...
 
I think the data type being text is the issue....I will look at the link you
suggest and go from there - much thanks for your help.


freakazeud said:
Hi,
try:

RN = Me.ReportNumber.Value
stLinkCriteria = "[reportnumber]='" & RN & "'"

You might need to play around with the syntax depending on your datatype
(since it is text and not numeric).
Have a look here for more dlookup/dcount samples:
http://support.microsoft.com/?kbid=208786

HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de


SamDev said:
I have made a different field the primary key so the Report Number field
is
no longer a primary key. The Report Number field is a text data type. I
looked at the code you suggested and have tried (see below) and the
message
box doesn't open when a dup is inserted. I don't need it to take me to
the
record as the original code does so I haven't included that portion - I'm
rather new at this so I might be missing something in my code (see
below).

The table that contains the field ReportNumber is called WIP. Any help
would
be appreciated. Thx!!

Private Sub ReportNumber_BeforeUpdate(Cancel As Integer)
Dim RN As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

RN = Me.ReportNumber.Value
stLinkCriteria = "[reportnumber]=" & " ' " & RN & " '"

'Check WPI Table for duplicate Report Number.
If DCount("reportnumber", "wpi", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message Box warning of duplication
MsgBox "Warning Report Number " _
& RN & " has already been entered."
End If
Set rsc = Nothing

End Sub

Thx!!!

freakazeud said:
Hi,
the primary key value of a table should have no meaning to you or your
users. It is only for access to work internanlly correct. If you want a
meaningful number you want to display and use then create an extra
field
for
that. The primary key should be of type autonumber since it will ensure
that
each record is truely unique.
With that said you can then use a dlookup or dcount function on the
before
update event to check if this data has been entered before or not. For
a
complete tutorial check this:
http://www.databasedev.co.uk/duplicates.html
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de


:

In a form I have added a field named "record number" that is the
primary
key
so duplicates are not allowed for the field. I want if the a user
enters
a
duplicate record number a message to pop-up saying the record is dup.
But
for some reason the message doesn't come up until after all the fields
in
the form have been filled-in and I go to a new record. I want the
message
to
come up immediately after I have filled in the record number field and
it's
a dup.

Any ideas?

Thx!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top