Protection against adding duplicate data

[wrek]

I have a form that, after clicking a button, appends line items to a table (I run an append query which extracts data from the form).

My problem is:

I want to create a protection against adding a line item which was previously added. I know how to use the RecordSet to search the existing table for duplicate values based on 1 field. However, in this table, an added line item is only uniquely defined by TWO fields. (Two primary keys)

Can anyone help?

[This message has been edited by wrek (edited 06-22-2001).]

 

[dhoffman]

Perhaps I don't quite fully understnad you dilemma, but why don't you simply make both of those two fields the Primary Key for your table?

 

[D-Fresh]

If I'm understanding you correctly, run a quick SQL check on the other table to see if it exists already...

Dim MyDB as database
dim MyRecs as recordset
Dim MySQL as string

MySQL = "SELECT * FROM [Table_Name] WHERE [Field1]='" & me!Value1 & "' AND [Field2]='" & me!Value2 & "'"

Set MyDB = currentdb
Set MyRecs = MyDB.openrecordset(MySQL)

if not MyRecs.eof then
msgbox "This information was already added"
'cancel the process
else
'run append query.
end if

Notice that in MySQL I have ticks(') around the form variables. This is assuming that the variables are string values. If they are numbers, then you can just delete them, and if they are dates, substitute a Pound sign(#) for the tick(').

If the recordset is empty then there is no duplicate values. If it isn't empty, then the values are already stored in the DB. Hope that helps.

Doug