Index or Primary Key error

[Guest]

I have created a form that contains informaton about Purchase Orders. I have
inclued a subform where I enter the part name and number. I have look up
fields that are linked to their corresponding tables so that I can choose
from the drop down list. There can be many part numbers for each Purchase
Order that is why I have the part information as the sub form.

My problem is that I keep gettng an error "Index or Primary key cannot
contain a null value" when I finish entering data into the first line of the
subform and tab to the second line. I am not sure why I am getting that error
because each field has data in it.

 

[Guest]

My thought is that even though all the data has been entered into the
subform, the table that the subform is bound to doesn't have all the data.
Check the table and the subform to make sure there aren't any hidden fields.

J

 

[Guest]

I created the tables and forms from scratch. If there was a hidden field then
I would know about it. Any other suggestions?

 

[Allen Browne]

Presumably you have 2 tables:
- PurchOrder:
PurchOrderID primary key
PurchOrderDate date/time
...

- PurchOrderDetail
PurchOrderDetailID primary key
PurchOrderID Relates to PurchOrder.PurchOrderID.
PartNum
...

Your main form will be bound to the PurchOrder table, and your subform to
the PurchOrderDetail table.

The error message could mean any of the following:

a) The PurchOrderDetailID is lacking a value.
If it is not an AutoNumber, you have to supply a value for it.

b) The PurchOrderID is lacking a value.
Open the main form in design view.
Right-click the edge of the subform control, and choose Properties.
On the Data tab of the Properties box, look at these properties:
Link Master Fields
Link Child Fields
They need to contain the names of the primary key and foreign key
respectively (both called PurchOrderID in the example above.)

c) You have another required index in your table.
Open the PurchOrderDetail table in design view.
Open the Indexes box (View menu.)
Post back if you can't see the problem.

d) Interaction with other tables.
If the subform is based on a multi-table query, there are other
possibilities. Post back details.

 

[el gecko verde]

Allen,

I haven't seen any follow-up on this exchange, but I have the same
problems as sk006; perhaps you can provide some advice? I'm stuck on
this one issue, and my head is getting sore from all the banging...

I'm using nested subforms to create project budgets, and have a few
tables involved:

1) tblProjects
strIndex - primary key; not Autonumber; created before budget
data is entered

2) tblProjectBudgets
strIndex - relates to tblProjects
numBudget - Autonumber field
dtmBudgetDate - Now()

3) tblProjectBudgetDetails
numBudget - relates to tblProjectBudgets
strAccountCode - chart of accounts entry
curAmount - amount in each account line

The budget details subform is nested in budget subform (linked on
numBudget), which is nested in project form (linked on strIndex).

Adding the first line to the budget works fine, second line returns
"Index or primary key cannot contain a Null value". I mirror the
process for the user with budget revisions, using separate tables and
subforms, also linked to the project form, and I am getting the same
error on that side. There should only be one "original" budget per
project, but could be multiple revisions per budget.

Let me know what other information you may need to identify the problem
with the forms. Alternately, if this is a table design problem on my
end, and I could achieve the same result more efficiently, it's early
enough in the design phase to make changes with little difficulty.

Thanks in advance for your (or anyone's) help with this,
Nate

 

[Allen Browne]

Tables look fine.

The message indicates that there is a field that is null when it cannot be.

It is the innermost subform (bound that tblProjectBudgets) that has the
problem?
Open tblProjectBudgets in design view.
Remove any Default Value on any field.
Remove any Default Value from the controls on the form as well.
If the subform is bound to a query that includes other tables, open them
also and remove any Default Values.

Next, add a text box to the subform for the numBudget field. The subform's
LinkMasterFields/LinkChildFields settings should cause this to populate
automatically, but you want to see that this is happening.

Are you using a combo for strAccountCode? If so, check Bound Column and
RowSource of the combo. Right field being assigned?

What is the primary key of tblProjectBudgets. Does this field have a value
assigned?

Are there any other fields in the table that have their Required property
set to Yes?

Are there any other fields that are indexed "No Duplicates" in that table?

 

[el gecko verde]

Allen ~ thanks for the quick reply. Responses and questions in-line
with your reply:

Tables look fine.

The message indicates that there is a field that is null when it cannot be.

It is the innermost subform (bound that tblProjectBudgets) that has the
problem?
Open tblProjectBudgets in design view.
Remove any Default Value on any field.
Remove any Default Value from the controls on the form as well.
If the subform is bound to a query that includes other tables, open them
also and remove any Default Values.

Done. There was a default value, of "Now()", in the date field, but I
removed that.

Next, add a text box to the subform for the numBudget field. The subform's
LinkMasterFields/LinkChildFields settings should cause this to populate
automatically, but you want to see that this is happening.

I added numBudget to sfrmProjectBudgetDetails and strIndex to
sfrmProjectBudget. strIndex is being populated in sfrmProjectBudget,
but numBudget is not in sfrmProjectBudgetDetails. I verified that
numBudget is set in the Master/Child fields for those subforms. I also
verified that the tables are related on that field.

Are you using a combo for strAccountCode? If so, check Bound Column and
RowSource of the combo. Right field being assigned?

Yes, the right field is assigned.

What is the primary key of tblProjectBudgets. Does this field have a value
assigned?

Primary key of tblProjectBudget is foreign key of strIndex, linked as
combo box to [tblProjects]![strIndex]. Primary key of
tblProjectBudgetDetails is dual: foreign key of numBudget linked as
combo box to [tblProjectBudget]![numBudget], and strAccountCode (to
keep users from entering an account code more than once per budget).

Are there any other fields in the table that have their Required property
set to Yes?
No

Are there any other fields that are indexed "No Duplicates" in that table?

No

I am now getting the "null value" error upon entering the first value
in sfrmProjectBudgetDetails. I can enter data in the fields, but
receive the error when tabbing to the next line (and thus attempting to
save the data). Interestingly, for projects with existing data in
sfrmProjectBudgetDetails, I can add records to sfrmProjectBudgetDetails
with no error (unless I try to add an account code already used on that
budget, so the primary key is working). Any thoughts?

 

[Allen Browne]

Notable aspects:
A) You say:
: Primary key of tblProjectBudget is foreign key of strIndex
So this means you have a one-to-one relation between tblProjects and
tblProjectBudgets.

B) Having added text boxes to show all fields in the subforms, you can't see
any field that is still null, yet you are still getting a message that a
field is Null.

C) Having removed all default values, the message still appears.

D) There are no other required fields or unique indexes.

E) It works for existing records.

Does this happen only if you try to add a record to the subform while one of
the parent forms is at a new record?

Presumably you tried a Compact/Repair (Tools | Database Utilities) in case
this is due to a bad index.

What version of Access?
What service pack?
What version of msjet40.dll (typically in \windows\system32)?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen ~ thanks for the quick reply. Responses and questions in-line
with your reply:

Tables look fine.

The message indicates that there is a field that is null when it cannot
be.

It is the innermost subform (bound that tblProjectBudgets) that has the
problem?
Open tblProjectBudgets in design view.
Remove any Default Value on any field.
Remove any Default Value from the controls on the form as well.
If the subform is bound to a query that includes other tables, open them
also and remove any Default Values.

Done. There was a default value, of "Now()", in the date field, but I
removed that.

Next, add a text box to the subform for the numBudget field. The
subform's
LinkMasterFields/LinkChildFields settings should cause this to populate
automatically, but you want to see that this is happening.

I added numBudget to sfrmProjectBudgetDetails and strIndex to
sfrmProjectBudget. strIndex is being populated in sfrmProjectBudget,
but numBudget is not in sfrmProjectBudgetDetails. I verified that
numBudget is set in the Master/Child fields for those subforms. I also
verified that the tables are related on that field.

Are you using a combo for strAccountCode? If so, check Bound Column and
RowSource of the combo. Right field being assigned?

Yes, the right field is assigned.

What is the primary key of tblProjectBudgets. Does this field have a
value
assigned?

Primary key of tblProjectBudget is foreign key of strIndex, linked as
combo box to [tblProjects]![strIndex]. Primary key of
tblProjectBudgetDetails is dual: foreign key of numBudget linked as
combo box to [tblProjectBudget]![numBudget], and strAccountCode (to
keep users from entering an account code more than once per budget).

Are there any other fields in the table that have their Required property
set to Yes?
No

Are there any other fields that are indexed "No Duplicates" in that
table?

No

I am now getting the "null value" error upon entering the first value
in sfrmProjectBudgetDetails. I can enter data in the fields, but
receive the error when tabbing to the next line (and thus attempting to
save the data). Interestingly, for projects with existing data in
sfrmProjectBudgetDetails, I can add records to sfrmProjectBudgetDetails
with no error (unless I try to add an account code already used on that
budget, so the primary key is working). Any thoughts?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[el gecko verde]

My replies follow your text:

So this means you have a one-to-one relation between tblProjects and
tblProjectBudgets.
Yes.

B) Having added text boxes to show all fields in the subforms, you can't see
any field that is still null, yet you are still getting a message that a
field is Null.

numBudget is null in sfrmProjectBudgetDetails. Since that field is
part of the dual primary key of tblProjectBudgetDetails, I can see why
I'm getting the error but I can't figure out how to fix it. For a new
project, the strIndex field does automatically populate in
sfrmProjectBudget, but I suppose that's because the project record
itself already exists. So the problem is how to create a record in
tblProjectBudget for the first time?

I think the problem is slightly different than I originally framed, but
I now have a better understanding of what's going on, just not how to
fix it. I could write an append query and run it from a "create
budget" button, but I'm trying to keep this as simple as possible,
where the user simply goes to the budget tab and starts entering budget
data.

D) There are no other required fields or unique indexes.
No

E) It works for existing records.

Does this happen only if you try to add a record to the subform while one of
the parent forms is at a new record?

It happens for a new budget only; once the initial budget record has
been created (since I've been playing in the tables, for instance),
budget data can be entered into sfrmProjectBudgetDetails. This makes
intuitive sense, since prior to a budget being created, there is no
numBudget to serve as a foreign key in tblProjectBudgetDetails, but I'm
not clear how to create the budget record initially.

Presumably you tried a Compact/Repair (Tools | Database Utilities) in case
this is due to a bad index.

It's set to compact on close, for now.

What version of Access?
What service pack?
What version of msjet40.dll (typically in \windows\system32)?

Access 2003, SP 1, msjet 4.0.8618.0

 

[Allen Browne]

No, you are not going to create a record in 2 tables from the one subform.

The user needs to create the parent record first, and then enter the subform
to create the related record.

 

[el gecko verde]

No, you are not going to create a record in 2 tables from the one subform.

Ah, I was afraid that would be the answer.

I'm only a novice in VBA; is there a way to have a "Create Budget"
button for the user to initially create the project budget, but then
become inactivate once a budget record has been created? Does it
matter where on the main form or which subform that button is placed?
Will the user be able to press the button and immediately enter budget
data, or will they need to navigate away from and back to the prjoect
record for Access to recognize a budget has been created?

Thanks for your help. I tend to operate in a little vacuum, so even
just "talking" this through with someone has helped.

Nate

 

[Allen Browne]

What you would normally do is use a form with a subform.

Main form is bound to tblProjectBudgets, with a combo for selecting
strIndex.

Then the subform is bound to tblProjectBudgetDetails.

User has to enter (or find) the budget in the main form before they can
enter the details of that budget in the subform.