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?
9 3387 zmbd 5,501
Expert Mod 4TB
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.
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.
I mean 2 field cannot be same filled, see the picture
zmbd 5,501
Expert Mod 4TB
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.
search the field that you dont want to duplicate before you send the data to the database .
zmbd 5,501
Expert Mod 4TB
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.
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 5,501
Expert Mod 4TB
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Charles |
last post by:
Hi MS Access & MS SQL Server Gurus !
I am trying to delete duplicate data from a LINKED SQL Server 2000
table (LinkedTable) and insert the result into a local (in Access)
table.
The following...
|
by: craig.brenizer |
last post by:
I have a table of data that has duplicate values in the pagenumber
field.
How can I combine the data so that the page numbers of duplicate part
numbers are on one record for that part number?
...
|
by: tom |
last post by:
Hi,
I have a datagrid which display the data like this:
bookName Author
------------------
book1 Bob
book1 Nancy
book2 Tim
------------------
|
by: Mike Charney |
last post by:
I have a temp table that has data I am inserting into a SQL DB table.
There is a column in both tables called PAT_ID. I need to check the SQL
table against the temp table for duplicate data in...
|
by: sandi |
last post by:
hi there
i have fields name called ..
PAF (text) , PNo (Number ) , LastName (Text)
2006/214/2 , 220101 , Winne
2006/321/3, 521496 , Joe
2006/321/1 , 521496 , Joe
2006/541/4 , 521496 ,...
|
by: psbasha |
last post by:
Hi,
How to remove the duplicate data from List,Tuples and Dictionary?.
Thanks in advance
PSB
|
by: anjanareddy |
last post by:
Hai,
i am unable to inserting duplicate data into UNIQUE index column,
Is there any other process, to insert duplicate data into UNIQUE index column?
plz let me know..
Thanks & Regards...
|
by: oaklander |
last post by:
I would like to make sure there are no duplicate data entries in my Oracle 9i table (called MainTable) which has an Id field that is the primary key, ValData with a varchar data type, Fid and Fid2...
|
by: killswitch101 |
last post by:
I need to get all of the non-duplicate data out of 2 tables (ci and hi)
i can't seem to work it out
i think i need to use the minus command but that will only display the data for 1 table
can...
|
by: AccessBeetle |
last post by:
I am working with SQL server 2005 backend and Access 2003 frond end.
I have a form bound to a table tblReportDetails. There are several(5) tabs on the same form. Each tab has its own subform. Every...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |