Filling in primary key based on an update query

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

Guest

Hi All,

I know this is probably a simple question, but I haven't had much luck with
it. I have a simple Personal Contact table (first name, last name, address,
etc). The primary key in the table is the full name (first and last
concatenated with a space in between). I want the key to be automatically
filled in when the first and last names are entered into a form. I have the
update query correct, I just don't know where to run it from. I've tried
several spots, but I always get the "Primary key cannot contain a null value"
error.

Thanks
 
Using the name combination is poor as a primary key as there can easily be
more than one John Smith. It is much better to use an Autonumber as primary
key.

You can not run an update on a record that does not exist and the record
does not exist until the primary key is entered.
Use a macro SetValue to accomplish filling the primary key field.
 
Hi Shaun,

Create your form with unbound controls, add a "Save"button, and in the
button's on-click create the primary key value and use an INSERT SQL
statement (or DAO or ADO if you prefer) utilizing the values in the unbound
controls to add the record to the table.
 
sounds like you need to fix your table structure.

firstly, if you want to have the full name as a primary key, then you
should make a composit key using the the fields firstName and lastName
together. there is no need for another "full name" field.

secondly, it's not a "strong" key anyway. you cant be guaranteed unique
names. so you can either
a) select something which definately uniquely identifies the person -
say for example their SSN or drivers licence issue number or something
like that. OR
b) use a redundant data field, such as "PersonID". I'd agree that
autonumber is the easiest option here, but often for real world systems
where these numbers wind up on reports and other outputs, you could
come up with a format. eg. M020606-21 could signify Male, 21st person
to be entered on the 2nd june 06. But this is harder to code.
 
Back
Top