By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,362 Members | 3,489 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,362 IT Pros & Developers. It's quick & easy.

How to make warning duplicate data?

yosiro
P: 34
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?
Sep 26 '12 #1
Share this Question
Share on Google+
9 Replies


zmbd
Expert Mod 5K+
P: 5,397
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.
Sep 26 '12 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Sep 26 '12 #3

yosiro
P: 34
I mean 2 field cannot be same filled, see the picture
Attached Images
File Type: jpg 2012-09-27_14-31_Microsoft Excel.jpg (22.8 KB, 318 views)
Sep 26 '12 #4

zmbd
Expert Mod 5K+
P: 5,397
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.
Sep 26 '12 #5

P: 75
search the field that you dont want to duplicate before you send the data to the database .
Sep 26 '12 #6

100+
P: 547
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/answer...than-one-field
Sep 30 '12 #7

zmbd
Expert Mod 5K+
P: 5,397
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.
Sep 30 '12 #8

yosiro
P: 34
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. :'(
Sep 30 '12 #9

zmbd
Expert Mod 5K+
P: 5,397
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/ac...010341594.aspx

Very similar in V2003
Sep 30 '12 #10

Post your reply

Sign in to post your reply or Sign up for a free account.