Validate data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a form for a user to enter data. Once they type in all the
information for a record, they click an 'Add Record' button which saves the
record and moves the user to a blank, new record. I'm writing this in VBA.
The fields that determine a duplicate record are LOCATION and PART_NUM, and
are being saved in a table called ADDS. The fields on my form are also named
LOCATION and PART_NUM.

How can I make this work so that a check is performed to see if this record
has already been entered before...ie...look for a duplicate record? Can
anyone help me? Thanks.
 
Well, if you set the fields to Indexed = Yes (No Duplicates) the users won't
be able to add the record if either is already used...
 
That won't work. It's the combination of the two fields that determines if
it's a duplicate. One location can be entered many times with different part
numbers, and the same part number can be in many locations. I need it to
check for a duplication of location AND part number on a record.
 
Oh sorry - I thought you meant either/or. You could accomplish the same by
using a multi-field primary key composed of those 2 fields.
 
Scorpiorc,

You can add a multiple-field, no duplicates index and maintain the
simplicity of a single-field AutoNumber primary key. In table design view,
choose View, Indexes. Add a new index name, and the name of the first field.
On the next line, leave the Index Name blank, and type the second field. In
the window at the bottom of the view, set Unique to Yes.

Sprinks
 
I'm trying to accomplish something very similar to what scorpiorc is trying
to do. Once data is entered in a form, the user would click on "Save and
Create New Record" or the user would just continue to tab until the form
displays a new blank record for data entry. How do I make it so that when
the user performs either of these actions, the data entered is checked
against all current records of the table and the user is notified when it is
a duplicate entry. A duplicate entry means that data of multiple fields are
the same (Last Name, First Name, XType and ID#).

I'm a beginner, so a thorough explanation is much appreciated (By the way, I
don't know what indexes are or what their purpose is).
 
ETC,

Follow my guidelines and create a multi-field index, and Access will monitor
whether you have a duplicate or not. Load the table in Design mode, click
View, Indexes. Give the index a name of your choice in the Index Name
column, e.g., NameTypeID, and enter the fields in the Field Name column,
leaving the Index Name column blank in all rows except the first. Then
change the Unique field in the bottom window to Yes, afterwhich Access will
enforce that their are no duplicates of a combination of these fields, and
display a message to the user.

Sprinks
 
I tried to make the index (I'm using Access 2002). When I enter the fields
in the Field column, it only lets me select one field. So, I tried selecting
the appropriate fields in the rows underneath it (even though that didn't
seem like the right thing to do), but that didn't work either. Because when
I tried to save and exit, it would let me. It said that there were
duplicates. The same person can be in there more than once, but with
different XTypes. What am I doing wrong? (Thanks again for all your help.)
 
ETC,

Your 2nd strategy was the correct one. The name of the index (up to you)
goes on only the first line. The other fields of the multi-field index go on
successive lines with the IndexName field left blank. Then, returning to the
top row of the index, set Unique to Yes.

However, it will not let you create the index if there are already
duplicates of this combination of fields. An easy way to determine whether
you have duplicates is to create a new query, selecting these fields, and see
how many records are returned. Then right-click on the Query window, select
Properties, and set Unique Values to Yes. I suspect it will return fewer
records, indicating that you have duplicates. Resolve these, and then you
can recreate the index.

Sprinks
 
It worked!!! Thanks SO much!!

But now I have another question...
In the form, when a duplicate entry is entered, the error message given is
"You can't go to the specified record." Is there a way to change this so
that it says "Duplicate Entry" in front of it?
 
Back
Top