Prevent Duplicate Record

[alwsid]

Hello...
How to prevent the duplicate records.
That's mean when I'm punch in the data to my
table or copy the data from another source to my table, msgbox will prompt the message.

waiting response from you all...
Thanks

[Clunietp]

Setup your table to not allow duplicate entries in the field....

You can do this manually by selecting count from table where primarykey = ####. If > 0, then it already exists

It would be more efficient to allow the DBMS to do the validation, but that depends on your DBMS, if you have one.

Tom

[alwsid]

Hello Tom...
I've two tables. Mytable1 and Mytable2.
In Mytable1 I've one primary key name CustID and in Mytable2 CustID is a foreign key.

So how to prevent duplicate records in Mytable2. That's mean when the records have same CustID, same OrderItem and OrderDate, msgbox will prompt that is a duplicate record.

Still waiting answer from you Tom...
Thanks.

[Clunietp]

If ALL 3 fields must be the same to determine if it is a duplicate, then query table2 using all 3 conditions. If recordcount > 1, then error, else, insert.

If you just need to check if the foreign key already exists, setup your foreign key field to not allow duplicates

[alwsid]

Hello Tom...
I've been sit over the night to resolved my problem, but I've no clue to do that.

I just get,
Dim db As Database
Dim rs As Recordset

Set db=OpenDatabase("c:\Mydb.mdb")
Set rs=db.OpenRecordset("SELECT * FROM Mytable2")

If rs.RecordCount > 1 Then
MsgBox("You've Enter A Duplicate Record")
End If

But I don't know which part to put that 3 conditions above when CustId, OrderItem and OrderDate is a same record.

Hopefully Tom, you can continue with my code.

still waiting your response.

[Clunietp]

change this:

"SELECT * FROM Mytable2"

to this:

"SELECT * FROM Mytable2 where CustID = " & MyCustIDVariable & " AND OrderItem = " & MyOrderItemVariable & " AND OrderDate = #" & MyOrderDateVariable & "#"

This assumes CustID and OrderItem are numeric fields, and OrderDate is a date field.

You could also use the COUNT sql function, but we won't get into that right now....

Good luck!
Tom

[alwsid]

Hello Tom...
I don't know with my code, it's doesn't work.
Please you check my code:

Dim db As Database
Dim rs As Recordset
Dim MyCustId As Long
Dim MyOrderItem As Long
Dim MyOrderDate As Date

"SELECT * FROM Mytable2 where CustID = " & MyCustID & " AND OrderItem = " & MyOrderItem & " AND OrderDate = #" & MyOrderDate & "#"

If rs.RecordCount > 1 Then
MsgBox("You've Enter A Duplicate Record")
End If

Where to put this event either in the Form Activate or Form After Update.

still waiting response from you Tom...

[Clunietp]

Are you assigning any values to your variables? you declare MyCustId, MyOrderID, MyOrderDate, but you should be getting values for these from a textbox or grid, etc. If you do not assign values to these, they will be zero (for longs), etc.

If you are assigning values to these variables, make sure that CustID and OrderItem are defined as numeric in your database, and OrderDate is defined as a date field....

[alwsid]

No, I don't assigning any values to my CustID,OrderItem and OrderDate.
Every day I've a lot of order from many CustID. When I'm punch in the record may be it's more than one a same record I've punch in to my table.
Because in Mytable2 CustID is a foreign key
so it's allow a duplicate record.
If a same CustID was ordered a same OrderItem at the same day (OrderDate), this is a duplicate record should I prevent to Mytable2.

Thanks.

[Clunietp]

I meant assigning values to the variables, not the fields. Your variables need to have something in them so the query will work

[alwsid]

Hello Tom...

I'm try thinking how to assigning values to the variables but I don't know how to do it.

Still needs your help.

Thanks

[Clunietp]

Hi

example of assigning values to variables:


dim strMyValue as string

strMyValue = "Hello World!"

msgbox strMyValue