do not allow duplicates with the error message without VBA? [ACCESS 2010]

[causative]

Hello,
I input data each day to the simple query:

ID, Date, Customer, Order_Number, Details, Customer&Order_Number

How could I block with the error message, the move from Order_Number filed to the Details field if the concatenation of Customer and the Order_Number is a duplicate?



Thx in advance

[CJ_London]

to use the standard access error message, create a multi field index in the table for customer and order number fields and set the allow duplicates property to no. But this will only work when the record is updated/created. Otherwise you will need to use VBA.

[causative]

Thank You, I have tested multi field index a minute ago and its some kind of good solution.
One additional question, If I decided to write a VBA code is it able to show the msgbox in the table view during input the data or the form must be created at first?

[aytee111]

You can't have VBA functions while entering data directly into a table. In fact, you shouldn't be allowing direct entry at all, tables should be hidden from users.

[CJ_London]

you can create a datasheet form which looks exactly the same as a query 'form' based on your query, then you can use vba in the order number after update event to check if the combination of customer and order number already exists, and if it does, take appropriate action.

however you need to consider all possibilities - what if a user enters an order number before entering a customer for example?

[causative]

Thank You. I will consider it