Index or primary key cannot contain a null value.

[Guest]

I have a subform (sfrmPKProfilesQualifications) that's used in multiple
subforms. The problem I have is that there are instances when
sfrmPKProfilesQualifications will have a record and it's parent subform will
not. Of course, this results in the error:

"Index or primary key cannot contain a null value."

I suppose an easy fix to this would be to move sfrmPKProfilesQualifications
out of the subforms, however, I wanted to explore if there's a Before/After
Update code that I could insert into it that would create the primary key
record.

Thanks in advance for your time!

 

[George Nicholson]

Sounds like the subform is automatically trying to create a record for each
record in the parent. I would think you have 2 choices: 1) Either support
this with some default entry information (like "Unknown" or N/A) or, 2) in
the form's BeforeUpdate event:

If SomevalueInRecord <> something then
' No data: don't save record
' Allow movement to another record without saving changes
' 1) Let the Form think we've made zero changes (so we don't end up back
here again)
Me.Dirty = False
' 2) Cancel the current update
Cancel = True
Else
' Legitimate record. continue the update
End if

 

[Dirk Goldgar]

I have a subform (sfrmPKProfilesQualifications) that's used in
multiple subforms. The problem I have is that there are instances when
sfrmPKProfilesQualifications will have a record and it's parent
subform will not. Of course, this results in the error:

"Index or primary key cannot contain a null value."

I suppose an easy fix to this would be to move
sfrmPKProfilesQualifications out of the subforms, however, I wanted
to explore if there's a Before/After Update code that I could insert
into it that would create the primary key record.

Thanks in advance for your time!

Hi, John -

Could you explain in what logical circumstance the subform could have a
record without there being a parent record? In the normal case of a
one-to-many relationship of parent form to subform, referential
integrity forbids this situation.

 

[Guest]

I really don't think so. You may be able to accomplish this, but still, the
reason you are having problems is design. In your original post, you said it
may be a child on more than one form. Already, you are in trouble. How can
you identify the parent if you have only the child?

 

[Guest]

The real problem here is database design. There should never be an instance
where a child exists without a parent.

 

[Guest]

I guess there are exceptions to every rule. Here's an example of my design:

tblPKCorrugatedFinishingAttributes is a parent to tblPKProfilesQualifications.

In this instance, a record for tblPKProfilesQualifications may exist without
a record in tblPKCorrugatedFinishingAttributes. Why? Because a corrugated may
not have a specific finishing attribute defined but may have several
qualifications.

In other words, qualifications are finishing attributes and are properly
assigned as children of tblPKCorrugatedFinishingAttributes.

Is there a before/after update event that can create a parent record?

 

[Guest]

John,
I really don't have any suggestions. I don't really mean to critisize, I
only mean to help. Your database design needs some work. It is not correct
to have a table that is a child to two different tables. Once you have
reanalyzed your data structure, the rest should be easy.

 

[Guest]

To answer your question I will need to construct the more complete design
(note abbreviation of names):

tblProfiles > PARENT to tblPKProfilesQualifications
tblProfiles > PARENT to tblPKCGFinishingAttributes (frmPKCorrugated >
sfrmPKCGFinishingAttributes > sfrmPKProfilesQualifications)

The parent record is created in tblProfiles. The challenge here is to be
able to enter a record into sfrmPKProfilesQualifications without having to
first create a parent record in tblPKCGFinishingAttributes.

What I'm forced to do now is create a record in tblPKCGFinishingAttributes
by entering in a "dummy" value one of the fields. I then simply delete the
dummy value which leaves the parent record in tblPKCGFinishingAttributes. I
can then continue adding records to tblPKProfilesQualifications via
sfrmPKProfilesQualifications.

I hope that better explains the design.

What I'd like to do is simply enter a record into
sfrmPKProfilesQualifications and have the parent record created automatically.

 

[Guest]

Hi, Dirk!

I sure bring some odd stuff to this forum, eh? It's probably because I'm
creating an odd or unusual database that Access was probably not intended to
service. Keep in mind that I'm housing packaging specifications which have a
broad range of data as they include glass and plastic bottles, corrugated
containers, plastic bags and cups, laminated film, etc.

I'll try to express my "logic."

Each packaging specification has a Profile. Each Profile relates to
Physical, Material, Performance, and Finishing Attribute tables. In the
example I previously gave there are Qualifications for each packaging spec.
Therefore, I created tblPKProfilesQualifications in a many-to-one
relationship with tblProfiles. Since Qualifications are a part of Finishing
Attributes, I created sfrmPKProfilesQualifications and made it a child to all
of the Finishing Attributes subforms.

Of course, this results in a parent record beng required in the Finishing
Attributes subforms before a record can be created in
sfrmPKProfilesQualifications. However, there are instances when there
Qualifications specified but no Finishing Attributes. Of course, this results
in "Index or primary key cannot contain a null value." To "override" this I
simply create a "dummy" value in any of the Finishing Attributes fields which
then creates a parent record in the Finishing Attributes table. I then delete
the "dummy" value and enter in the records in sfrmPKProfilesQualifications.

I would prefer not to have to create the "dummy" value as this is a bit
quirky and will confuse other users. I was thinking a before/after update
event might do the trick...?

Thanks for your time!!!

 

[Guest]

Thanks, George! I gave this a try:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If SomevalueInRecord <> something Then
' No data: don't save record
' Allow movement to another record without saving changes
' 1) Let the Form think we've made zero changes (so we don't end up back
here again)
Me.Dirty = False
' 2) Cancel the current update
Cancel = True
Else
' Legitimate record. continue the update
End If

End Sub

When I tested it I still received the primary key null value message. This
coding is foreign to me - have I misunderstood something?

Thanks!