"Indexed or Primary Key cannot contain a Null value" / Autonumbers

  • Thread starter Thread starter Kim M.
  • Start date Start date
K

Kim M.

I keep getting this message whenever I try to enter a new record via a form.
I click OK on the error message box, and then can continue without problems.
But I'd still like to get rid of the message box.

What I THINK is happening is this: The primary key is an autonumber (and
does not appear on the form). I tried making it visible on the form to
experiment and this is what happens: It registers as "(new)", which I guess
is a null value, until I click the OK on the error message box, at which
point it converts to an autonumber. So I guess my question is how do I
trigger the assignment of the autonumber WITHOUT having to go through that
pesky error message first?

(The program didn't always do this, but I am unable to pinpoint when it
started or what I did to bring it on.)

As always, thanks for your input!
Kim M.
 
On Sat, 8 Aug 2009 14:55:01 -0700, Kim M.

Did you compact the database; this behavior is quite unusual. The
autonumber is generated when the first character is entered in a new
record, whether the field is on the form or not.

-Tom.
Microsoft Access MVP
 
Tom's suggestion to compact/repair the database is a good one.

If that doesn't solve it, tell us a bit more about this form. For example:

a) Is it a form bound to a query that uses multiple tables (in which case
Access may be trying to add a value to the other table -- particularly if
that table has any fields with a Default Value assigned.)

b) Is it a form with a subform that's bound to the same table (or a split
form since this seems to be A2007.)

c) Is there any Default Value in the text box for the autonumber field on
your form, or in the AutoNumber field if you open the table in design view?
 
I comact/repair at least once per day, so that is not it.

a) It is bound to a query, but that query only draws from one table.
b) There are subforms, but they are all bound to different tables/queries.
c) No default values for either.

Any light you could shed on this would be most appreciated!
Kim M.
 
Hmm: if this is an attached table, did you compact the back end database?

You may want to run these recovery steps on both the front end (where the
forms are) and the back end (if the tables are attached):
http://allenbrowne.com/recover.html

If that doesn't solve it, post the exact error message you are receiving.
And double-check if there's anything in the form's BeforeInsert event.
 
Everything compacted, still happening. Nothing in "BeforeInsert" event for
form.

Here is the error:
Index or primary key cannot contain a Null value. (Error 3058)
 
Kim, I'm beginning to think this may be another index in your table.

Open it in design view.
Open the Indexes dialog.
What indexes are there?
 
There are two indexes:

1. CaseAutonumber (the autonumber field)
2. CaseID (this is the primary key)
 
Jim,
Yes I have a NEW button. It was created using the automated options in
Access, so there is a macro rather than code associated with it. Is it
possible to do a breakpoint/stepthru in a macro?
Kim M.
 
You can use Single Step (on the menu) to step thru a macro, but you do
have to be in Macro design view to do that.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Back
Top