null but not null

[Guest]

I have a formula that populates my primary key as other fields on my form are
filled in, but when I go to save the new record I am informed that the index
or primary key cannot contain a null value. How do I get my form to
recognize that the field is populated by the formula? Help PLEASE!!

 

[jahoobob via AccessMonster.com]

If the formula depends on other fields in the form you could set the field to
the reults of the calculation by coding the Afterupdate of the last field
needed for the calculation. You probably need to use the forms object names
(Me!) in the formula and not the tables fields as the fields have not been
updated until the PK Null issue is resolved.

 

[Guest]

Thank you for the quick reply. I'm kind of learning as I go. I have a
formula that pulls the values into the primary key automatically.

=[Drawing #] & "-" & [Sheet #] & [Revision]

The only primary key related information in the VB code is below...

Private Sub PrimaryKey_Exit(Cancel As Integer)


End Sub


What do I need to do with this?

 

[jahoobob via AccessMonster.com]

In the AfterUpdate property of the text box code in which you enter the last
bit of data, say Revision, place this code:

Private Sub Revision_AfterUpdate()
Me!ID = Me![Drawing] & "-" & Me![Sheet #] & Me![Revision #]
End Sub

Set the Tab Stop property of the ID text box to No and the ID needs to be
Text.
Hope this helps,
Bob

Thank you for the quick reply. I'm kind of learning as I go. I have a
formula that pulls the values into the primary key automatically.

=[Drawing #] & "-" & [Sheet #] & [Revision]

The only primary key related information in the VB code is below...

Private Sub PrimaryKey_Exit(Cancel As Integer)

End Sub

What do I need to do with this?

If the formula depends on other fields in the form you could set the field to
the reults of the calculation by coding the Afterupdate of the last field

[quoted text clipped - 6 lines]

 

[Guest]

I use the AutoNumber DataType for a primary key in my tables, and it makes
life very easy for me. I don't know if there are objections to this.

And you could still have your combination field made up in a query if you
wanted a semparate or familiar reference.

It's my understanding that Primary Keys (or any index for that matter) are
faster if they are Long Integers instead of Text fields.

Anyone want to chime in?

Try putting the formula in the Before Insert event of text box for your
primary key.

Me.PrimaryKey =[Drawing #] & "-" & [Sheet #] & [Revision]

Thank you for the quick reply. I'm kind of learning as I go. I have a
formula that pulls the values into the primary key automatically.

=[Drawing #] & "-" & [Sheet #] & [Revision]

The only primary key related information in the VB code is below...

Private Sub PrimaryKey_Exit(Cancel As Integer)


End Sub


What do I need to do with this?

If the formula depends on other fields in the form you could set the field to
the reults of the calculation by coding the Afterupdate of the last field
needed for the calculation. You probably need to use the forms object names
(Me!) in the formula and not the tables fields as the fields have not been
updated until the PK Null issue is resolved.

Jennifer wrote:
I have a formula that populates my primary key as other fields on my form are
filled in, but when I go to save the new record I am informed that the index
or primary key cannot contain a null value. How do I get my form to
recognize that the field is populated by the formula? Help PLEASE!!

 

[Guest]

Try putting the formula in the Before Insert event of text box for your
primary key.

Me.PrimaryKey =[Drawing #] & "-" & [Sheet #] & [Revision]

Thank you for the quick reply. I'm kind of learning as I go. I have a
formula that pulls the values into the primary key automatically.

=[Drawing #] & "-" & [Sheet #] & [Revision]

The only primary key related information in the VB code is below...

Private Sub PrimaryKey_Exit(Cancel As Integer)


End Sub


What do I need to do with this?

If the formula depends on other fields in the form you could set the field to
the reults of the calculation by coding the Afterupdate of the last field
needed for the calculation. You probably need to use the forms object names
(Me!) in the formula and not the tables fields as the fields have not been
updated until the PK Null issue is resolved.

 

[Guest]

I agree with you on the use of autonumbers. I have seen others argue that
they prefer to use meaningful Unique data.
IMHO, there are a couple of problems there. At some point, you stand a good
chance of getting duplicate data. Lets say you are using phone number as a
primary key. Seems unique enough, but then (although chances are rare, why
take them), Sam moves out of town and cancels his phone service. Then Sally
moves into town, starts her phone service and gets Sam's old number and
becomes a client/member, whatever of your organization. Opps!

Or we are using a Supervisors Last Name to link from the Employee table to
a Supervisor table. Fred has 37 people that report to him. Fred gets fired
and Wally takes his place. Now we have to change a lot of records. If,
instead, we are properly normalized and the [SuperVisor] field is an FK to
the supervisor table and we are using autonumbers, All that is necessary is
to go to the supervisor table, change the [Name] field and all related
queries, forms, etc. now show Wally instead of Fred. This is good (and we
didn't really like Fred all that much anyway)

I use the AutoNumber DataType for a primary key in my tables, and it makes
life very easy for me. I don't know if there are objections to this.

And you could still have your combination field made up in a query if you
wanted a semparate or familiar reference.

It's my understanding that Primary Keys (or any index for that matter) are
faster if they are Long Integers instead of Text fields.

Anyone want to chime in?

Try putting the formula in the Before Insert event of text box for your
primary key.

Me.PrimaryKey =[Drawing #] & "-" & [Sheet #] & [Revision]

Thank you for the quick reply. I'm kind of learning as I go. I have a
formula that pulls the values into the primary key automatically.

=[Drawing #] & "-" & [Sheet #] & [Revision]

The only primary key related information in the VB code is below...

Private Sub PrimaryKey_Exit(Cancel As Integer)


End Sub


What do I need to do with this?

:

If the formula depends on other fields in the form you could set the field to
the reults of the calculation by coding the Afterupdate of the last field
needed for the calculation. You probably need to use the forms object names
(Me!) in the formula and not the tables fields as the fields have not been
updated until the PK Null issue is resolved.

Jennifer wrote:
I have a formula that populates my primary key as other fields on my form are
filled in, but when I go to save the new record I am informed that the index
or primary key cannot contain a null value. How do I get my form to
recognize that the field is populated by the formula? Help PLEASE!!

 

[Guest]

I've tried several of the suggestions in the thread, but none seem to correct
the issue of the db reading the PK as NULL. It is generating the primary key
exactly as I want it. The tab stop property of the primary key text box is
set to no and I have it locked down so that it can't be inadvertently edited,
but can be copied to place elsewhere. I really just need for the field to be
recognized as populated rather than null. I thought I had accidentally
stumbled into correcting this problem 2 days ago, but as soon as I
transferred this db out to the share as released and ready for use - it came
up broken again. Apparently whatever I thought I did to fix it (which I've
already forgotten as I've tried so many things) wasn't a very good fix anyway.

Jennifer

In the AfterUpdate property of the text box code in which you enter the last
bit of data, say Revision, place this code:

Private Sub Revision_AfterUpdate()
Me!ID = Me![Drawing] & "-" & Me![Sheet #] & Me![Revision #]
End Sub

Set the Tab Stop property of the ID text box to No and the ID needs to be
Text.
Hope this helps,
Bob

Thank you for the quick reply. I'm kind of learning as I go. I have a
formula that pulls the values into the primary key automatically.

=[Drawing #] & "-" & [Sheet #] & [Revision]

The only primary key related information in the VB code is below...

Private Sub PrimaryKey_Exit(Cancel As Integer)

End Sub

What do I need to do with this?

If the formula depends on other fields in the form you could set the field to
the reults of the calculation by coding the Afterupdate of the last field

[quoted text clipped - 6 lines]

or primary key cannot contain a null value. How do I get my form to
recognize that the field is populated by the formula? Help PLEASE!!