Re: Primary Key allowing duplicates

josette@xxxxxxx wrote:

I've created a one table database with a number of queries, forms and
reports. I have one primary key. In one of the forms, if I enter a new
record and the primary key is a duplicate, I won't get the error
message unless I tab out of the last field in the form. If I choose to
close the record, it will not save it, which is essentially correct.

Access's behavior of silently discarding record inserts/updates when table or field
level validation rules have occurred when closing the form has always bothered me
greatly.

As a result I, almost, always add the following line in the code behind the close
button.

If me.dirty = true then _
docmd.runcommand accmdsaverecord

I say almost because sometimes I forget.

But, the user won't know that and will think the record has been
saved. How do I tell Access to check for the duplicate value when the
cursor exits the primary key field so the user knows immediately to
fix the error?

Tina's got the right idea but one minor problem exists is when the record has already
been added and the user changes the primary key and changes it back to the same value
it was. Thus the controls BeforeUpdate event is triggered. The dlookup will locate
the current record the user is on and tell you it already exists. So you need to
exclude the current record if you are updating the record and not if you are adding
the record.

While this is a somewhat unusual case many folks might decided it's not worth
worrying about and just pressing Escape several times would cancel all updates to
this record and fix things. Not sure how to add the neccesary "stuff" in dlookup as
I've never used it and don't feel like figuring things out right now. I've used
recordsets with Select and Where in the same situation.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
.

Relevant Pages

  • RE: change access error message
    ... If the Key value is not in the table, DLookup will return Null. ... > I have a textbox in a form to enter new values to a primary key field. ... > create duplicate values in the index, primary key, or relationship. ... > the data in the field or fields that contain duplicate data, ...
    (microsoft.public.access.forms)
  • RE: change access error message
    ... If the Key value is not in the table, DLookup will return Null. ... >> I have a textbox in a form to enter new values to a primary key field. ... >> create duplicate values in the index, primary key, or relationship. ...
    (microsoft.public.access.forms)
  • Re: Access as a RDBMS--why the multiple relationships?
    ... I notice in Microsoft Access the relationship chart can, ... non-unique field in TABLE B. In the relationship diagram this shows ... referenced field is not a primary key, ...
    (comp.databases.theory)
  • Re: How to change the RowSource in a SubForm in VBA code
    ... It makes sense that the same problem situtions could arise when setting the subform's RecordSource on the fly. ... from the Database window onto another form or report or by using the ... Microsoft Access automatically sets the LinkChildFields ... primary key. ...
    (comp.databases.ms-access)
  • Re: Error 3022????
    ... duplicate values in the index, primary key, or relationship. ... field or fields that contain duplicate data, remove the index, or redefine ... Tony Toews, Microsoft Access MVP ...
    (comp.databases.ms-access)