Preventing duplicate records using two and three fields?

[Ed from AZ]

(Using Access 2007) I have two fields in one table (other than the
autonumber, that is): ItemType and ItemID. ItemType will definitely
be duplicated across the records, and it is conceivable that ItemID
may be duplicated. But the combination of ItemType and ItemID may
never be duplicated. What do I look at to set this up? I especially
want to warn the user who is entering a new ItemType and ItemID via
form that "This combination already exists."

In another table, it's going to be the combination of ItemTpe, ItemID
and WorkDate. Here, data is entered from the form into the table
using
dbs.Execute " INSERT INTO tblData " _

What can I insert into the code prior to the INSERT INTO statement
that would check the three-field combination and warn the user of a
duplicate entry?

Ed

 

[Armen Stein]

(Using Access 2007) I have two fields in one table (other than the
autonumber, that is): ItemType and ItemID. ItemType will definitely
be duplicated across the records, and it is conceivable that ItemID
may be duplicated. But the combination of ItemType and ItemID may
never be duplicated. What do I look at to set this up? I especially
want to warn the user who is entering a new ItemType and ItemID via
form that "This combination already exists."

In another table, it's going to be the combination of ItemTpe, ItemID
and WorkDate. Here, data is entered from the form into the table
using
dbs.Execute " INSERT INTO tblData " _

What can I insert into the code prior to the INSERT INTO statement
that would check the three-field combination and warn the user of a
duplicate entry?

Ed

To prevent duplicates at the database level, look at Indexes. You can
create an index on one or more fields, then set the Index to Unique.
That will cause an error if anyone (or your code) attempts to insert a
duplicate. You can trap for that error and explain it to the user.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[ntc]

probably go with a dcount for that combo

put in the before update event

if the dcount is >0 then put out a msgbox....

 

[Ed from AZ]

Hi, Armen.

To prevent duplicates at the database level, look at Indexes. You can
create an index on one or more fields, then set the Index to Unique.
That will cause an error if anyone (or your code) attempts to insert a
duplicate. You can trap for that error and explain it to the user.

Armen Stein

I looked in the Access Help files and can't find anything about
"setting an Index"?? Or I am probably finding everything and just not
understanding what I'm reading?

Ed

 

[Ed from AZ]

Hi, ntc.

probably go with a dcount for that combo

put in the before update event

if the dcount is >0 then put out a msgbox....

So if I'm reading the Help file right, I would do something like -

intIsDup = DCount("[ItemType]", " & Chr(39) & strType & Chr(39) &
", _
"[ItemID] = " & Chr(39) & strID & Chr(39) & _
" AND [WorkDate] = #" & dtWKDate & "#")

If intIsDup > 0 Then
MsgBox "This information duplicates another record."
Exit Sub
End If

Yah?

Ed

 

[John W. Vinson]

I looked in the Access Help files and can't find anything about
"setting an Index"?? Or I am probably finding everything and just not
understanding what I'm reading?

Open the Table in design view.
Click the Indexes tool on the toolbar - it looks like lightning hitting a
datasheet.
Type some unique name in the left column of the two-column window that pops
up, and the first of the fieldnames in the right column next to it.
On the next row, leave the first column blank and put another fieldname in the
right column. Continue for up to ten fields.

Check the "Unique" checkbox and save the table.

 

[Ed from AZ]

Thank you, John. I was able to set the Index following your
instructions in one table.

Unfortunately, it apparently won't work for this in my main data
table. In the table, this three-field combination can be duplicated
in successive records. I have the ItemType, ItemID, WorkDate, and two
other fields. On the data entry form, I have single controls for the
Type, ID and Date, and multiple controls for Field1 and Field2. I
iterate through the multiple Field1/2 combinations and make my INSERT
INTO string using the same Type, ID and Date values. After the data
is entered, I don't want the users to accidently assign data for one
item to a previously entered item and overwrite the previous data. So
I'll have a View/Edit Data form - but that will probably be another
thread!

Thanks again for your help.
Ed

 

[Armen Stein]

Open the Table in design view.
Click the Indexes tool on the toolbar - it looks like lightning hitting a
datasheet.
Type some unique name in the left column of the two-column window that pops
up, and the first of the fieldnames in the right column next to it.
On the next row, leave the first column blank and put another fieldname in the
right column. Continue for up to ten fields.

Check the "Unique" checkbox and save the table.

Thanks John.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Armen Stein]

In the table, this three-field combination can be duplicated
in successive records. I have the ItemType, ItemID, WorkDate, and two
other fields. On the data entry form, I have single controls for the
Type, ID and Date, and multiple controls for Field1 and Field2. I
iterate through the multiple Field1/2 combinations and make my INSERT
INTO string using the same Type, ID and Date values. After the data
is entered, I don't want the users to accidently assign data for one
item to a previously entered item and overwrite the previous data. So
I'll have a View/Edit Data form - but that will probably be another
thread!

So it sounds like the uniqueness requirement isn't at the database
level, but rather during this "batch" or "screenful" of records. In
that case you're correct, you would need code in your form to check
for duplicates. The DCount idea in another thread is a good place to
start.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com