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

[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.

 

[Tom van Stiphout]

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

 

[Allen Browne]

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?

 

[Kim M.]

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.

 

[Allen Browne]

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.

 

[Kim M.]

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)

 

[JimBurke via AccessMonster.com]

I wouldn't think that error was due to your auto-number field. Do you have a
button on your form that you use for creating a new record? If so, I would
set a breakpoint at the beginning of that button's on-click event and then
step through to see exactly where the error occurs. I'm assuming this form is
bound to a table or query. If so, does that table have related tables? Maybe
the error is due to a field that is used as a key in a related table.

 

[Allen Browne]

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?

 

[Kim M.]

There are two indexes:

1. CaseAutonumber (the autonumber field)
2. CaseID (this is the primary key)

 

[Kim M.]

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.

 

[JimBurke via AccessMonster.com]

I don't think there's any way to step through a macro. If the macro uses
'RunCode' to call a function you could step through the called function, but
that's about it. What exactly does the macro do? Is it simply going to a new
record? Can you list the macro Actions and their associated parameters? Is
there ANYTHING in the NEW button's click event other than the macro call and
error handling?

You mentioned that the problem doesn't always occur. Are you able to recreate
it or do you have no idea what situations trigger it?

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.

I wouldn't think that error was due to your auto-number field. Do you have a
button on your form that you use for creating a new record? If so, I would

[quoted text clipped - 20 lines]

 

[John Spencer]

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
'====================================================