By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,143 Members | 1,919 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,143 IT Pros & Developers. It's quick & easy.

Select a STARTING Primary Key

P: 19
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,
Oct 7 '08 #1
Share this Question
Share on Google+
7 Replies


DonRayner
Expert 100+
P: 489
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.
Oct 7 '08 #2

P: 19
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!
Oct 7 '08 #3

missinglinq
Expert 2.5K+
P: 3,532
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. If Me.NewRecord Then
  3.   If RecordsetClone.RecordCount = 0 Then
  4.    Me.IDNumber = "1024"
  5.   Else
  6.    Me.IDNumber = DMax("val([IDNumber])", "YourTableName") + 1
  7.   End If
  8. End If
  9. End Sub 
If you want to use a Numerical field:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. If Me.NewRecord Then
  3.   If RecordsetClone.RecordCount = 0 Then
  4.    Me.IDNumber = 1024
  5.   Else
  6.    Me.IDNumber = DMax("[IDNum]", "YourTableName") + 1
  7.   End If
  8. End If
  9. End Sub
  10.  
Linq ;0)>
Oct 7 '08 #4

ADezii
Expert 5K+
P: 8,669
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.
Oct 7 '08 #5

mshmyob
Expert 100+
P: 903
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,
Oct 7 '08 #6

P: 19
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!
Oct 7 '08 #7

P: 19
Okay. I'm slower than I thought.

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

thanks!
Oct 8 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.