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

[montyb]

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. [sig][/sig]

 

[Guest_imported]

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. [sig]<p>John A. Gilman<br><a href=mailto:gms@uslink.net>gms@uslink.net</a><br>[/sig]

 

[Ruairi]

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

 

[montyb]

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? [sig][/sig]

 

[Francis]

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 (&quot;field1 = x and field2 = y&quot
if rec.nomatch then' can add the record
rec.add
rec!field1 = x
rec!field2 = y
rec.update
else
msgbox(&quot;Duplicate&quot
endif
rec.close
set rec = nothing
db.close
set db = nothing
[sig][/sig]

 

[MoGryph]

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(&quot;SELECT * FROM tbl1 WHERE (([tbl1].[fld1]) = '&quot; _
& Me.txtFld1.Value & &quot AND (([tbl1].[fld2]) = '&quot; & Me.txtFld2.Value & &quot;&quot

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