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