Using Autonumber as PK?

Does Access have a problem using an autonumber for a PK? I've got 3 tables.
tbl-Parts
pk-PartsID - Autonumber
PartNumber - Number
PartDescription - Text

tbl-Universal
pk-UniversalID - Autonumber
UniversalPartNumber - Number
UniversalDescription - Text

tbl-UniversalParts
fk-PartsID - Number
fk-UniversalID - Number

I then set up some forms per the instructions on this topic I received last
week, basically one form is Parts with a subform Universal, and another form
Universal with a subform Parts. My problem is finding a work around because
both PartNumbers and UniversalPartNumbers may have duplicates at times and
can't be used as the pk. Access doesn't seem to like my setup and my friend
told me it was the autonumber as the pk but he couldn't explain why or what I
needed to do to work around this.

Thank you.

Autonumbers are generally used as PK.

Did you set a one-to-many relation between the tables?
Click on TOOLS - Relationships and add the tables by clicking on the Show
Table icon that is a yellow plus sign with a datasheet.
The primary key will be in bold. click on the primary key and drag to the
foreign key of the related table. Select Enforce Referential Integerity and
Cascade Updates.

I see you are using a number field for part numbers. I think you need to
use text for this datatype as you will never need to do math (add, subtract,
multiply, or divide) these numbers. You can have a problem if there is a
dash in the number.

"Novice" wrote:

Does Access have a problem using an autonumber for a PK? I've got 3 tables.
tbl-Parts
pk-PartsID - Autonumber
PartNumber - Number
PartDescription - Text

tbl-Universal
pk-UniversalID - Autonumber
UniversalPartNumber - Number
UniversalDescription - Text

tbl-UniversalParts
fk-PartsID - Number
fk-UniversalID - Number

I then set up some forms per the instructions on this topic I received last
week, basically one form is Parts with a subform Universal, and another form
Universal with a subform Parts. My problem is finding a work around because
both PartNumbers and UniversalPartNumbers may have duplicates at times and
can't be used as the pk. Access doesn't seem to like my setup and my friend
told me it was the autonumber as the pk but he couldn't explain why or what I
needed to do to work around this.

Thank you.

No problem, in fact it's quite normal to do that.

-Dorian

"Novice" wrote:

Does Access have a problem using an autonumber for a PK? I've got 3 tables.
tbl-Parts
pk-PartsID - Autonumber
PartNumber - Number
PartDescription - Text

tbl-Universal
pk-UniversalID - Autonumber
UniversalPartNumber - Number
UniversalDescription - Text

tbl-UniversalParts
fk-PartsID - Number
fk-UniversalID - Number

I then set up some forms per the instructions on this topic I received last
week, basically one form is Parts with a subform Universal, and another form
Universal with a subform Parts. My problem is finding a work around because
both PartNumbers and UniversalPartNumbers may have duplicates at times and
can't be used as the pk. Access doesn't seem to like my setup and my friend
told me it was the autonumber as the pk but he couldn't explain why or what I
needed to do to work around this.

Thank you.

Thanks for the input/advice. I'll change the PartNumber &
UniversalPartNumber to text, that may have been my whole problem because my
numbers do have dashes in them. And yes I have one-to-many relationships
between,
(tbl-Parts - tbl-UniversalParts) and (tbl-Universal -
tbl-UniversalParts) and I have selected Enforce Referential Integerity and
Cascade Updates in each relationship.

Thanks again to all of you.

You probably don't need to enable cascading updates since you're going
to be using autonumbers as surrogate promary keys. However, I would
enable cascading deletes to that the deletion of a parent record will
delete all of tha related child records, leaving no orphans.

HTH
--
-Larry-
--

"Novice" wrote in message
...
Thanks for the input/advice. I'll change the PartNumber &
UniversalPartNumber to text, that may have been my whole problem
because my
numbers do have dashes in them. And yes I have one-to-many
relationships
between,
(tbl-Parts - tbl-UniversalParts) and (tbl-Universal -
tbl-UniversalParts) and I have selected Enforce Referential
Integerity and
Cascade Updates in each relationship.

Thanks again to all of you.