Prevent duplicate enteries on same type of "order "

[scopes456]

I search a few forums and how to prevent a user from entering duplicate item. i have a main table called MASTER_TBL. MASTER_TBL has a DATE, ORD_TYP, LOCATION fields. I would like to prevent users when they enter the same MASTER_ID,DATE AND ORD_TYP AND LOCATION, they will get a msg box notifying that it is already entered, which would then delete the record.

i listed the fields and data type, i saw depending on the data type it effects what code to use. The code i tired i kept getting "mismatch error"

MASTER_ID ---AUTONUMBER ---PRIMARY KEY
DATE---------DATE/TIME
ORD_TYP-----NUMBER - its a lookup field that gets info from [ord_typ_tbl]
LOCATION----SHORT TEXT

 

[CJ_London]

If master_id is an autonumber it cannot be duplicated so you will never get a message saying it has already been entered

 

[jdraw]

You could investigate unique composite index, but I think you should tell us about your application and what you are trying to do. There seems to be some confusion with your use of autonumber PK and duplicates.

Good luck.

 

[sneuberg]

As far as the mismatch problems are concerned I suggest reading

http://www.baldyweb.com/buildsql.htm

 

[scopes456]

thank you for replying, let me try to explain a bit further, listed below is an example of how the table appears when a user enters an order. A user is able to put 2 of the same orders on the same DATE,ORD_TYP,LOCATION.This is what i am trying to prevent, we a user tries to put a order in on the same DATE ,ORD_TYP ,LOCATION.


MASTER_ID DATE ORD_TYP LOCATION
1 12/10/15 RUSH NEW YORK
2 12/10/15 RUSH NEW YORK

i was trying to user Dcount function

 

[sneuberg]

You can check for duplicates with dcount in the before update event of the form. The code would be like:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If DCount("*", "[MASTER_TBL]", "[Date] = #" & Me.Date & "# AND [ORD_TYP] = " & Me.ORD_TYP & " AND [LOCATION] = '" & Me.LOCATION & "'") > 0 Then
    MsgBox "The combination of date, order type and location must be unique.  The combination you entered already exists in the database"
End If

End Sub

You can see this work in the attached database. You should note in this code the three ways the three types are handle. The Date (You really should change that to something like Order Date) is enclosed in pound signs. The ORD_TYP which you said was a number is just concatenate in with nothing enclosing it and finally the LOCATION which is short text is enclosed in single quotes. In the example you gave you showed ORD_TYP as "RUSH". If it's a text field in the [MASTER_TBL] then you will need to enclose it in single quotes.

I suggest that you also make the combination of these three field a unique index as a further safe guard against duplicates. That by itself would prevent them but you still should check this in the before update as you can provide the user with a better error message.

 

[scopes456]

sneuberg a million thank you,