Select a STARTING Primary Key

[PaulaCM]

Hello Again Byters (hmmm... that sounded better in my head!)!

Question on Primary Keys / Auto numbering.

I want to make sure that each record has its own individual ID (hence primary key) but, I just want to choose the starting point. So, instead of starting with record ID 1, I'd like to start with record ID 1024. (I'm replacing a paper system with an electronic one, but would like to keep going from where I left off).

So, ideally I would like the next record to automatically be record ID 1025.

I'd like to use this number as my primary key to ensure that I have no duplicates in the future from my starting point forward.

HELP! and Thanks!

best,

[DonRayner]

Create another table with one field set to a number (not autonumber) with the same name as the autonumber field in your origional table. Put one record in the table with number 1023.

Create an append query using the query builder and use it to append the recrod from your new table to the origional table. It will place 1023 into the autonumber field.

You can delete the new record but the table will keep this value as the last value entered and will then start numbering from 1024.

[PaulaCM]

Create another table with one field set to a number (not autonumber) with the same name as the autonumber field in your origional table. Put one record in the table with number 1023.

Create an append query using the query builder and use it to append the recrod from your new table to the origional table. It will place 1023 into the autonumber field.

You can delete the new record but the table will keep this value as the last value entered and will then start numbering from 1024.

Sorry, could you give me more step by step instructions? I don't understand how to do this. It sounds like it's simple, but I'm an access beginnger (it's 2007 btw, dont' know if that makes a difference).

thanks!

[missinglinq]

Autonumbers are intended to be used for one purpose and only one purpose, to provide a unique identifier for each record. Here's a post I've archived from a gentleman named John Vinson, MVP, explaining how autonumbers work:

When using Autonumber, do be aware that there will be gaps in the numbering - any record that's deleted will leave a gap; hitting <Esc> after starting a record will leave a gap; adding records using an Append query may leave a gap, often a huge one; replicating the database will make your invoice numbers random.

In short... it's best NOT to use Autonumbers for human consumption, and particularly not for consumption by accountants and auditors. Invoice sequences like 319, 321, 322, 385, 386, 221841246, -1083225152 make such people get very nervous

For the kind of field you're using, you might want to consider using an auto-incrementing number instead, using a hack such as one of these.

The first code here would be for an IDNumber that is defined in the table as Text datatype. "Number" fields that aren't used for math really should be defined as Text.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
  If RecordsetClone.RecordCount = 0 Then
   Me.IDNumber = "1024"
  Else
   Me.IDNumber = DMax("val([IDNumber])", "YourTableName") + 1
  End If
End If
End Sub 

If you want to use a Numerical field:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
  If RecordsetClone.RecordCount = 0 Then
   Me.IDNumber = 1024
  Else
   Me.IDNumber = DMax("[IDNum]", "YourTableName") + 1
  End If
End If
End Sub
 

Linq ;0)>

[ADezii]

I could show you code that will automatically set an AutoNumber/Primary Key Field to the Value you specify, but it would be a little complex. The good news is that you would not have to understand it, but only know how to Call the Function, which is very simple. If you are interested, let me know, I'll just need your Table Name as well as the Name of the AutoNumber/Primary Key Field, which, if I remember correctly is Record ID.

P.S. - Missinglinq and DanRayner gave you some good advice, take and learn from it.

[mshmyob]

Linq is 100% correct. Sounds like in your situation you should NOT be using an AUTONUMBER data type. Autonumber data types should only be used if the user never needs to reference the PK. Since it appears your PK is based on a number that is critical to the envoronment and is used for specific end user/reporting purposes then DO NOT use an autonumber data type.

As linq says just create a Number data type field and increment that with code.

cheers,

[PaulaCM]

Thanks everyone!

I'm definitely going to keep my primary key an auto number and try to impliment missinglinq's code. Although, i'm a little slow, not having done expressions of any kind before.

So, I'll be back if I can't figure out how to use your awesome code!

thank you again!

[PaulaCM]

Okay. I'm slower than I thought.

would you mind explaining step by step how to incorporate your excellent code into my table?

thanks!