null but not null


G

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!!
 
Ad

Advertisements

J

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

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?
 
J

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]
 
G

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?

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

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

Jennifer said:
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 said:
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!!
 
G

Guest

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

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

Jennifer said:
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 said:
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.
 
Ad

Advertisements

G

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)

Access101 said:
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?

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

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

Jennifer said:
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!!
 
Ad

Advertisements

G

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

jahoobob via AccessMonster.com said:
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!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

When is a Null not a Null? 8
Is Not Null 10
Filter by form is null/not null 0
Query by Form shows Null or Is Not Null 3
Date Is Null 1
Calculations with NULL 8
Is null and Else 2
Null Fields 2

Top