Primary Key

[Jesse]

I Have a database that uses our reciept numbers as the
primary key. All reciepts are 6 numbers long. We have been
typing in the value of the reciept number but now we have
receipts that will be in consecutive order. Starting with
the number 000103. How can I set the defalt value in the
table to place that number into the data base. In the
expression builder what expression can I use to have the
numbers generated. PLease help if you can. Thanks in
advance.

 

[Les]

I pulled this out of Access 97 help.

Change the starting value of an incrementing AutoNumber
field

For a new table that contains no records, you can change
the starting value of an AutoNumber field that has its
NewValues property set to Increment to a number other than
1. For a table that contains records, you can also use
this procedure to change the next value assigned in an
AutoNumber field to a new number.

1 Create a temporary table with just one field, a
Number field; set its FieldSize property to Long Integer
and give it the same name as the AutoNumber field in the
table whose value you want to change.

How?

2 In Datasheet view, enter a value in the Number
field of the temporary table that is 1 less than the
starting value you want for the AutoNumber field. For
example, if you want the AutoNumber field to start at 100,
enter 99 in the Number field.
3 Create and run an append query to append the
temporary table to the table whose AutoNumber value you
want to change.

How?

Note If your original table has a primary key, you must
temporarily remove the primary key before running the
append query. Also, if your original table contains fields
that have the Required property set to Yes, the Indexed
property set to Yes (No Duplicates), or field and/or
record ValidationRule property settings that prevent Null
entries in fields, you must temporarily disable these
settings.

4 Delete the temporary table.
5 Delete the record added by the append query.
6 If you had to disable property settings in step 3,
return them to their original settings.

When you enter a record in the remaining table, Microsoft
Access uses an AutoNumber field value 1 greater than the
value you entered in the temporary table.

Note If you want to compact the database after changing
the starting AutoNumber value, make sure to add at least
one record to the table first. If you don't, when you
compact the database, the AutoNumber value for the next
record added will be reset to 1 more than the highest
previous value. For example, if there were no records in
the table when you reset the starting value, compacting
would set the AutoNumber value for the next record added
to 1; if there were records in the table when you reset
the starting value and the highest previous value was 50,
compacting would set the AutoNumber value for the next
record added to 51.

 

[Rick Allison]

Jesse,

Remember an autonumber primary key is 100% number. So 000103 will actually
be stored as 103. If you need the zeros you'll want to do that in reports
or queries.

If the field is always six characters long I do something like this...

strReceiptID = right("000000" & recordset!ReceiptID,6)
That way if the receipt number is 103 or 1030 the results will be 000103 or
001030.

Cool?

Rick

 

[Van T. Dinh]

Since you wrote that you have been typing in the value of the receipt
number, I assumed that you are not using AutoNumber.

You can default the value of the Receipt Number to the next available number
on the data-entry Form (using the Form_Current Event) but not directly in
the Table (since there are no "Table Events" that you can use to run the
code). However, this means that all data entry must be done via the Form.

Assume that you have a TextBox Control "txtReceiptNumber" on the Form, you
can use the Form_Current Event to se this value with code like:

If Me.NewRecord Then
Me.txtReceiptNumber = DMax("ReceiptNumber", "tblReceipt") + 1
End If

If it is a multi-user database and it is possible that 2 or more users
entering data at the same time, you need to use a slightly more complex code
to avoid the same ReceiptNumber being allocated to 2 or more different
users.