Don't allow a duplicate entry

  • Thread starter Thread starter Nathan Lee
  • Start date Start date
N

Nathan Lee

I think this should be simple, but I can't figure out how to do it.

I have a form, where after updating a field, I need to make sure it's
unique. The table allows multiples of this field, called "UniqueID", but
they must have a different revision number, called "Revision". If the
user tries to enter a new record with the same pairing of "UniqueID" and
"Revision", I need to bring up a message box.

More distinctly:

If Me.uniqueIDField.Value and Me.revisionField.Value exist in the same
record, then MsgBox("Please enter a distinct UniqueID").


Can I do this? I'd really prefer to keep this in regular old VBA, so if
I can avoid using SQL, it would be pretty nice. I could just see if the
UniqueID exists in the table, and then for all records where it does,
check if the Revision exists in the same record. Is there a VBA
procedure that checks whether a value exists in a specific field in a table?

Nathan
 
One way to address this problem is to create an index that
comprises these two columns and set its Unique property to
Yes. You could then put code in the form's Error
eventhandler to check for the duplicate error and provide
your own error message in place of the standard Access
error message.

Hope This Helps
Gerald Stanley MCSD
 
-----Original Message-----
I think this should be simple, but I can't figure out how to do it.

I have a form, where after updating a field, I need to make sure it's
unique. The table allows multiples of this field, called "UniqueID", but
they must have a different revision number, called "Revision". If the
user tries to enter a new record with the same pairing of "UniqueID" and
"Revision", I need to bring up a message box.

More distinctly:

If Me.uniqueIDField.Value and Me.revisionField.Value exist in the same
record, then MsgBox("Please enter a distinct UniqueID").


Can I do this? I'd really prefer to keep this in regular old VBA, so if
I can avoid using SQL, it would be pretty nice. I could just see if the
UniqueID exists in the table, and then for all records where it does,
check if the Revision exists in the same record. Is there a VBA
procedure that checks whether a value exists in a specific field in a table?

Nathan
.
In the table where both these fields exist you can make
Click to expand...
both fields the primary key. That would not allow
duplicate entries. If there are already duplicate entries
you will get an error message when trying to save the
changes to the table. You will then have to go in and
manually remove any duplicates.
 
That worked quite well, thank you. Now I just have to teach myself how
to properly handle errors. I've attached a message event to the On Error
event for the Form, but I'm still getting about six message boxes when I
try to exit the form (I know one or two are from a macro invoked when
the form is closed.

Thanks for the help!
 
Back
Top