How to make warning duplicate data?

[yosiro]

In the table i have 2 field: Name and Status
I create a form datasheet view for data input
How to create warning if i type duplicate data on that form?

[zmbd]

Set the fields at the table level to "no duplicates"

If you're wanting a VBA solution to this, then you need to either open a record set and build a query or use one of the aggregate functions (DCOUNT()) to check for the existence of the value.

I don't usually post completed code unless the poster has shown their work first.

[TheSmileyCoder]

The question here is if you want to prevent it in which case the simplest approach is to set 1 index spanning both fields in your table, and specify no duplicates.

Also you should not use Name as a field name, as that is a reserved word in access and you will run into trouble and weird errors if you use field names that access has reserved for internal use.

[yosiro]

I mean 2 field cannot be same filled, see the picture

[zmbd]

Now you've changed the question; however, the answer is now as TheSmilelyCoder has stated, one index over both fields - no duplicates is the simple answer. The VBA is still as I have suggested. You also need to change the field names slightly so as not to use reserved names - google those or search BYTES.

[lyodmichael]

search the field that you dont want to duplicate before you send the data to the database .

[neelsfer]

I would try and get a unique number for each name as many people can have the same names
look at this example from previous posts with a unique id for products

http://bytes.com/topic/access/answers/889086-how-prevent-duplicate-based-more-than-one-field

[zmbd]

Neelsfer,
IF you take a look at his example, the duplicate name does not appear to be the issue. It is the duplication of the compound entry of name and status. We have no idea from the information presented if the name field is linked or if there is a need to track an individual by name against multiple status states. IN either case, with the information we have, assigning a unique name may cause other issues.

The unique index on the two fields is IMHO the best option in that it doesn't require VBA to work, then the search on two fields via VBA.

[yosiro]

Maybe i would combine those data in to one field like concatenate in excel. I use append query for that purpose. Then i wil set that field with no duplicate. I hope it will work but still cannot give a custom warning. :'(

[zmbd]

You can do that in a query.
The unique index is not that hard to setup.
Open the table in design view.
V2010, Ribbon, Show Hidden, Indexes
Table will show up, enter a name for the index in the first column, enter the first field name in the second, set the property to unique, on the next row, leave the name field blank, enter the field in the second, set the unique field.

http://office.microsoft.com/en-gb/access-help/create-and-use-an-index-to-improve-performance-HA010341594.aspx

Very similar in V2003