Limit numbers in primary key

[minimalexperience via AccessMonster.com]

I'm trying to limit the numbers in primary key to 5 digits. I'd like all
of the primary keys to be at least 5 digits long as well (i.e. 00001,
00002, 00003 . . . 00010). I also want Access to assign the number to a
record as it is entered into the database. It may not necessarily be in
order, but it will be unique. I would appreciate any assistance that you
may provide. Thanks!

 

[gls858]

I'm trying to limit the numbers in primary key to 5 digits. I'd like all
of the primary keys to be at least 5 digits long as well (i.e. 00001,
00002, 00003 . . . 00010). I also want Access to assign the number to a
record as it is entered into the database. It may not necessarily be in
order, but it will be unique. I would appreciate any assistance that you
may provide. Thanks!

Access has a data type called auto number. If you include this in your
table a new number will be assigned for each line added to the table.
I don't think you can limit it to 5 digits though.

From Access help:

Microsoft Access provides the AutoNumber data type to create fields that
automatically enter a unique number when a record is added. Once a
number is generated for a record, it can't be deleted or changed. An
AutoNumber field can generate three kinds of numbers: sequential numbers
that increment by one, random numbers, and Replication ID (also referred
to as GUIDs— globally unique identifiers) numbers. AutoNumbers that
increment by one are the most common kind of AutoNumber and are a good
choice for use as a table's primary key (primary key: One or more fields
(columns) whose values uniquely identify each record in a table. A
primary key cannot allow Null values and must always have a unique
index. A primary key is used to relate a table to foreign keys in other
tables.). Random AutoNumbers will generate a random number that is
unique to each record within the table. Replication ID AutoNumbers are
used in database replication (database replication: The process of
creating two or more special copies (replicas) of an Access database.
Replicas can be synchronized, changes made to data in one replica, or
design changes made in the Design Master, are sent to other replicas.)
to generate unique identifiers for synchronizing replicas (replica: A
copy of a database that is a member of a replica set and can be
synchronized with other replicas in the set. Changes to the data in a
replicated table in one replica are sent and applied to the other
replicas.).

gls858

 

[Guest]

You ran limit the numbers in the Primary Key by inputting 00000 in the format
property box of your autonumber field. If you want the numbers random your
New Values property should be Random or else Increment if you want the
numbers in order.
Hope that helps.

 

[minimalexperience via AccessMonster.com]

I am aware of the AutoNumber function, but I'm using that for my primary
key. I need another field that is 5 digits long and unique to identify
each customer record within the table. Upon entering the 5 digit customer
ID, if it is a new number a new record will be added to the table. If it
is a current record, the current record will be displayed. Again, any
assistance is GREATLY appreciated as I'm struggling.

 

[Guest]

OH!!! Well you didn't mention that. Access Tables only allow for one
autonumber field per table. I don't know if something can be done in SQL
(doubt it since it's the table..) But there's a work around. It's kinda
complex but check this out.

Assuming we're starting from scratch:
Create a TableA
In this table include a field [Sequence]. This should be an autonumber
field with the properties set as previously described.
Include all the fields that you're going to need in your table except your
other autonumber field.

Create a TableB
In this table include a field [Sequence] that is a number field and contains
the same properties as the corresponding field in TableA. (In respect to
Format length).
*Very important: Make sure you mark this field as Indexed: Yes (No Duplicates)
Include all the other fields from TableA with the same field names.
Add a field [PrimaryKey] or whatever your primary key field is and set it as
an autonumber field with the appropriate properties.

Create a Query.
Using TableA as the source, drop down all fields into the detail section.
From the File Menu | Query | Append Query and choose TableB.
If you labelled each field correctly all the names should already match up.
(Notice you don't see your primary key field which is good.
When you run this Query it will take all the data in TableA and append it to
TableB. The records that are already in TableB will not be appended because
they would produce "duplicate" values in the "Count" field. You'll receive
that message everytime, but if you run the query from a Macro, you can Set
Warnings Off | Run Query | Set Warnings On. That way you or your users won't
see the message. The Query will continue to run despite this message. It
sounds complicated but it's not really.

So in summary, you'll have TableA being the table that accepts new records
into your database. This Table will assign a sequence number to each new
record. All new records will then be appended to TableB (which is what
you'll be doing your work off of) via the Append Query that you created.
This Table will assign it a PrimaryKey value while preserving the value in
the Sequence field from TableA.

Hope this helps.

 

[B. Comrie]

Set the second ReferenceNumber field to Text data type, and indexed / no
duplicates, a number field won't hold leading zeros.

Use a field on the form to trigger a new Reference, or the BeforeUpdate
event on the form.

IE:
Me.ReferenceNumber = Right$("00000" & Me![PrimaryKey], 5)

 

[Guest]

Actually it will, if you set the format as I mentioned. Comrie -- Thanks,
but I tried it first before I made the suggestion.

Set the second ReferenceNumber field to Text data type, and indexed / no
duplicates, a number field won't hold leading zeros.

Use a field on the form to trigger a new Reference, or the BeforeUpdate
event on the form.

IE:
Me.ReferenceNumber = Right$("00000" & Me![PrimaryKey], 5)

--
B Comrie
http://www.codewidgets.com
http://www.comriesoftware.com

I'm trying to limit the numbers in primary key to 5 digits. I'd like all
of the primary keys to be at least 5 digits long as well (i.e. 00001,
00002, 00003 . . . 00010). I also want Access to assign the number to a
record as it is entered into the database. It may not necessarily be in
order, but it will be unique. I would appreciate any assistance that you
may provide. Thanks!