Two Key Primary Key

[knowshowrosegrows]

I get the concept of a two key Primary key making a unique identifier. Love
that. When I test my database though and put in a record with identical
primary key fields, the database throws an errors and freezes up. Putting in
records more than once is just the kind of thing that will happen with this
database so, can you tell me the elegant way to have the user notified of the
error and not freeze up so they can change their entry?

 

[Jeff Boyce]

Are you saying that you have a multi-column(2) primary key, and let the
users enter potential values in the respective controls, but have a problem
with Access reminding them that they are entering duplicate keys?

First, are the users entering valid records that happen to duplicate values
(you don't mention what you are using) for the multi-column primary key? If
so, don't use those columns as a primary key if there's actually legitimate
duplicate values.

Second, if your users are entering values "to see" if they can enter the
values, consider giving them a different way of learning if the values are
already taken. One example would be using a combo box with LimitToList set
to yes. The user could "find" that the value they were entering was already
taken.

A little more info about what two fields you are using and what the
underlying data is like would help.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[knowshowrosegrows]

Thanks for your swift reply.

What I have is a combo box with many drug rehab program names -it is limited
to list. The user has to input the daily census for each program. I don't
want them to space out and enter a census number for the same program and the
same date so I made CensusDate and Prm_Code the primary keys. When I test it
and put a duplicate record into one I already have I get an error that reads:

"The changes you requested were not successful because they would create
duplicate values in the index, primary key or relationships. Change the data
in the field that contains duplicate data, remove the index or redefine index
to permit duplicate entries and try again."

When I try to change the program in the drop down or the census date on the
form, I get the same error until I finally have to get out of Access
altogther. I want them to get an error when they put a record for the same
date and program, but I want them to be able to change it and go on.

 

[Jeff Boyce]

Have you tried pressing the <Esc> key to "empty" those fields?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Another potential approach would be to use an Autonumber Primary Key field
(hidden from the user) and a unique index on the two fields you have now.
You might have more control over the message by intercepting the error in an
error-handling routine.

Regards

Jeff Boyce
Microsoft Office/Access MVP