the primary key in new records is null

[Mfarr]

I recently acquired a database that is already established. When entering new date the primary key is null. I've looked at the properties and the best I can figure is it has something to do with a missing property called New Values. Is there any way to fixed this?


Thanks for any help!

[ranman256]

Can you just set the primary key to autonumber ?, then it will NEVER be null.

[Mfarr]

I cannot. I've tried that and I get the following error: Once you enter data in a table, you can't change the data type for any field to AutoNumber, even if you haven't yet added data to that field.

[June7]

Would have to create a new field of autonumber type.

Then if there are dependent records that use the original field as foreign key, would have to run UPDATE query to change the foreign key to the new autonumber value. Then the original primary key field can be deleted. Rename the new field to the old name.

Might have to break some relationships first before the old field can be deleted.

[Mfarr]

Unfortunately the primary key is a membership ID. We cannot reassign this number. I'm trying not to reprogram 968 membership ID number if I don't have to. I can also work around the issue and enter the ID number each time but that means I have to keep up with the last ID that was assigned.

[June7]

Autonumber is not supposed to be used for a value that has meaning to users. And some advocate that a value that has meaning to users should not be a PK/FK.

Creating a custom unique identifier requires code (common topic in forum) - which may exist in the db but is now failing. What you refer to as a property called 'New Values' may be a VBA function. We would have to see the code for analysis of issue. Or provide db. Follow instructions at bottom of my post.

[orange]

Primary key can not be NULL.

A table can have at most one primary key, but it may have more than one candidate key. A primary key is a combination of columns which uniquely specify a row; it is a special case of unique keys. One difference is that primary keys have an implicit NOT NULL constraint while unique keys do not.

Please tell us more about your situation. A Primary Key can not be NULL.

[Mfarr]

I understand that the Primary Key is not supposed to be null. In this case the column that should have been the Primary Key was not given that designation. I added it once I received the database. I'm not sure if that has anything to do with the issue.

[June7]

You added (or tried to) the PK designation? But some records are null in that field? Then you must populate the field and values must be unique then set the PK designation.

As noted in post 6, creating custom unique identifier requires code.

[Mfarr]

I'm guessing there is some sort of issue in the database. I tired to create a sample to share and after deleting all the records to remove personal information the Primary key would not allow a null value. I cannot identify any SQL code in this database or any of the others we manage. All of these databases were set up year's ago and probably by someone who did not understand how to code. What makes these databases work is the following text in the format option under the General Properties for the Primary Key. "MS"00000.

[June7]

Can still provide db for analysis.

That looks like formatting for an autonumber field.

A primary key field should not allow Null.

[Mfarr]

Any time I manipulate the database such as removing personal information the Primary key starts to work correctly. I am unable to send a copy for analysis. Is there any way to program the database to AutoNumber again? I'm pretty sure the last administrator was not personally assigning Member ID, however that function was lost when the database was sent to me.

[June7]

The table does NOT now have an autonumber type field? Post 4 describes how to fix the table.

Without examining db, can't really give more specific guidance.