×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

how do you get Access to recognize duplicate records upon entry?

how do you get Access to recognize duplicate records upon entry?

how do you get Access to recognize duplicate records upon entry?

(OP)
I am creating a database and need to be notified when a record is entered that is a duplicate (from a form).  I can't make this partiular field the key field.  Any advice on how to get notified of a duplicate record would be greatly appreciated.  Thanks in advance.

RE: how do you get Access to recognize duplicate records upon entry?

If the user is working on a form bound to the table, you could use the Form_before_update event to test the value you need to prevent. If the test fails, cancel the update event of the form. If you need more help, just post a reply.

John A. Gilman
gms@uslink.net

RE: how do you get Access to recognize duplicate records upon entry?

You don't have to make a field the key field to put a uniqueness constraint on it.

RE: how do you get Access to recognize duplicate records upon entry?

(OP)
How would I go about doing this?

Let me clarify a little exactly what I am doing.  I will be entering information on railcars on a form.  If I am entering data on 100 cars, all but one field will be the same (the Unit Number). I am in the process of creating a form that will allow me to enter all the duplicate data on the form as well as a range of unit numbers so access will create the said range with the duplicate data (so I won't have to enter all 100 cars individually).

After the cars (in the range) have been entered, the unit numbers will need to be married to a field called Mark (one of the duplicate fields).  The two fields TOGETHER need to be a unique identifier.  I will of course have a seperate field for both Mark and UnitNo (called Mark&UnitNo).  The question is, how do I get Access to notify me each time a Mark&UnitNo is duplicated?

RE: how do you get Access to recognize duplicate records upon entry?

You could add a primary key to the TWO fields, then Access will only allow unique combination.

Or you could search the recordset for field1 = x and field2 = y before entering the record. Sort of like this.

dim db as database
dim rec as recordset
set db = opndatabase(yourDBName)
set rec = yourDB.Opendatabase(yourtable)
rec.findfirst ("field1 = x and field2 = y")
if rec.nomatch then' can add the record
   rec.add
   rec!field1 = x
   rec!field2 = y
   rec.update
  else
   msgbox("Duplicate")
endif
rec.close
set rec = nothing
db.close
set db = nothing

RE: how do you get Access to recognize duplicate records upon entry?

One method I like to use is populating a recordset with a SQL query on the fields that I want to find a duplicate on. Then I check the recordset's RecordCount property, to see if it's 0 or greater than 0. (without moving to a different record, the record count will only be 1 if there's more than one record. If you try moving, and there are no records however, you will get an error.

' Assume tbl1 is the table I'm searching for records in.
' The fields I'm searching for duplicate in are fld1 and fld2.
' The corresponding controls on the form are txtFld1 and txtFld2

Dim dbs as Database
Dim rst as Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM tbl1 WHERE (([tbl1].[fld1]) = '" _
      & Me.txtFld1.Value & ") AND (([tbl1].[fld2]) = '" & Me.txtFld2.Value & ");")

If rst.RecordCount > 0 Then   ' this is a duplicate entry
      ' Enter code here
Else         ' This is not a duplicate entry
      ' Enter code here
End If

Set rst = Nothing
Set dbs = Nothing

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close