Setting AutoNumber to Non-Default Value

I would like to use the AutoNumber data type within a field.  However I would 
like the sequential autonumbering to begin from 6000 rather than the default 
value of 1(for the first record).  Thanks for your help in advance. 
Jeff
0
Utf
9/4/2007 11:22:04 PM
access 16762 articles. 3 followers. Follow

3 Replies
767 Views

Similar Articles

[PageSpeed] 16

I suggest you may not want to use Autonumber for that use.  Autonumbers are 
designed to provide unique numbers.  It in not designed to provide numbers 
in order and for a number of reasons may not do so.  As a result using them 
in any application where the user sees the numbers is likely to end up with 
confusion.

    There are other ways of providing the numbers you want depending on the 
particual application.

    If you want to try anyway (no guarantee of success) put in a dummy 
record with 6000 manually input.  See if your next number is 6001.  BTW 
don't be surprised if it misses a few numbers from time to time or if it may 
decide to go back and start using those unused numbers like 2,589.

-- 
Joseph Meehan

 Dia 's Muire duit


"Jeffreyu" <Jeffreyu@discussions.microsoft.com> wrote in message 
news:760D41BD-9AF0-4603-8D9E-D1A77A1A7D81@microsoft.com...
>I would like to use the AutoNumber data type within a field.  However I 
>would
> like the sequential autonumbering to begin from 6000 rather than the 
> default
> value of 1(for the first record).  Thanks for your help in advance.
> Jeff 

0
Joseph
9/5/2007 12:12:04 AM
If you actually care what the value of the field happens to be then
you should not be using the Autonumber datatype.  It exists for the
sole purpose of generating unique values for a surrogate Primary Key.
Use it for any other purpose and you will find parts of your life
wasted in trying to get it to behave as you wish.  It is not
guaranteed to be sequential and, in most cases, eventually is not.

HTH
-- 
-Larry-
--

"Jeffreyu" <Jeffreyu@discussions.microsoft.com> wrote in message
news:760D41BD-9AF0-4603-8D9E-D1A77A1A7D81@microsoft.com...
> I would like to use the AutoNumber data type within a field.
However I would
> like the sequential autonumbering to begin from 6000 rather than the
default
> value of 1(for the first record).  Thanks for your help in advance.
> Jeff


0
Larry
9/5/2007 2:41:00 AM
On Tue, 4 Sep 2007 16:22:04 -0700, Jeffreyu
<Jeffreyu@discussions.microsoft.com> wrote:

>I would like to use the AutoNumber data type within a field.  However I would 
>like the sequential autonumbering to begin from 6000 rather than the default 
>value of 1(for the first record).  Thanks for your help in advance. 
>Jeff

Well... Don't.

Autonumbers are designed for one purpose, and one purpose only: to provide a
meaningless unique key. They WILL develop gaps; not only will deleting a
record leave a gap, so will hitting <Esc><Esc> at any point after starting a
new record. Loading data into the table with an Append query can generate a
gap, often a big gap. Replicating your database will make all autonumbers
random!

If you care about the value in an ID field, use a Long Integer and maintain it
yourself with code, rather than using an autonumber. For instance, in a Form
you can use code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtID = NZ(DMax("[ID]", "[tablename]"), 5999) + 1
Me.Dirty = False
End Sub

to increment the largest existing ID and immediately write the record to disk.

             John W. Vinson [MVP]
0
John
9/5/2007 5:16:43 AM
Reply:

Similar Artilces:

Set Form Property from a Different Form
In Access 2003 I have a form with a property defined as follows: Public wtPrelimFinal as string This form, and a couple of similar forms, call a dialog form where the user picks between two options. I want to pass the name of the form and the proprty to the called form as follows, for example: "Forms!ThisForm.wtPrelimFinal", using OpenArgs, then storing the string in a variable, say strForm in the called form. When the user selects an option, I want to reset the value of wtPrelimFinal in the calling form. Hard coded, it would be somthing like: Forms!ThisForm.wtPrelimFinal = ...

How can i set the source-data-range of pivottable2 to the source .
In order to save memory for excel files with multiple pivot-tables, I should be able to reference to the source-data-range of pivot1 for the source-data-range of pivot2. Any idea how (apart from the 1-time question at first creation of the new pivot)? ...

Outlook 2002 & Automatic send & receive settings
In outlook 2002 I have my mail send and receive options set to send/receive every 3 minutes. Often I have to perform a manual send & receive to download my mail. My laptop is connected to the internet via wifi and I havent noticed a break in the connection. Is there anything that causes outlook to require a manual send/receive when options are set to perform the operation automatically? Thanks Hello, No dont need to do send/receive manually if an option Schedule set s/r automatically for x min. Since you are on Wifi network. Let me know any errors in the right bottom in outlook. Explai...

Where is value for CP_UTF8 defined?
I have Microsoft Visual C++ 2005 Express Edition. I just searched all files in C:\Program Files\Microsoft Visual Studio 8\ and never found "CP_UTF8", yet my program which uses it compiles fine. So from where is the compiler getting a value for CP_UTF8? (I did find in my old Microsoft Visual C++ 6.0 file WINNLS.H which defines this value.) In general where is a file defining names for CodePages values? "Susan Rice" <srice1@cox.net> wrote in message news:ZYfrh.709$iz5.605@newsfe14.phx... >I have Microsoft Visual C++ 2005 Express Edition. I just searched > a...

Fun with Time values
Hello all, The boss gave me a new one today, and there is one formula I can no get my head around. Here is the situation: In part of the Excel sheet, I have a colum listing scheduled deliver times for various locations. These Time Values are static, i.e non-changing. Each colum to the right of these projected Time Value will contain Actual Delivery times. One colum per each day of the mont for Actual Delivery times. What I need to do with these values is this: 1) Automatically highlight any Delivery Time that exceeds the projecte Delivery Time by 1 hour. This I have done with Conditional...

CRM 3.0 in a non-active directory environment?
Hi We run an environment primarily based on a Linux server, but we have a MS Server 2003 Std Ed to run some of our MS apps. Our client machines are all WinXP laptops though. We would like to use MS CRM 3.0 (which we get in our Action Pack) but it wants to force us to install AD on our MS Server. Is it possible to run MS CRM 3.0 without an AD? Thanks! James James, I'm sure that AD is required with CRM 3.0. Sorry - It's probably not what you want to hear. "James" wrote: > Hi > > We run an environment primarily based on a Linux server, but we have a > MS S...

range select a value in a cell?
I have formulas calculating values to produce cell references such as J10, L15 etc the formulas are similar to ="I"&TEXT(Q4, "d") I am creating a macro where I want to select the cells calculated from my formulas... instead of using range("J10:K10").select i want the range to use the specific cell reference calculated in my formula... because the range i want to select changes every time a new record is added. is there a way to do this? any help is much appreciated. thanks b2: a2:a4 c2: =SUM(INDIRECT(B2)) --- HTH, David McRitchie, Microsoft MVP -...

Generate Controls Dynamically,FormView-Scroll,Default,MFC
Hai I am looking to generate Edit controls Dynamically in a FormView and when the no of edit controls increases the Size Of the Horizantal ScrollBar increeases and should be able to scroll and is there any other way of doing other that "SetScrollSizes" as i want the FormView to handle that scrolling and not forcibly Thanks and Regards suresh suresh at his best wrote: > Hai > I am looking to generate Edit controls Dynamically in a FormView and when > the no of edit ...

Just set up outlook
I just set up outlook to use my yahoo account. I dont like that my emails are no longer going to my cell phone or my yahoo account, if accessed directly through the internet. How can I change the email address being used for outlook? Assuming its a pop account you perhaps need to check the option, within Outlook / Accounts to leave a copy on the server "Nina" <Nina@discussions.microsoft.com> wrote in message news:F3126A7E-B674-42FD-9DF8-7BB724EEB332@microsoft.com... > I just set up outlook to use my yahoo account. I dont like that my emails > are > n...

Rejecting email for non-existant users
Hello, How can I configure my Exchange 2000 / 2003 servers to reject email destined for email accounts that no longer exist? I do not wish to have an NDR sent. Thanks, in advance for any assistance rendered. -- Anthony Create a DG with no members. Add the SMTP addresses of the old email accounts to it. They will magically vaporize with no NDRs. On Fri, 27 Aug 2004 00:12:13 GMT, Anthony V. Vitale <anthony.vitale@mindspring.com> wrote: >Hello, > How can I configure my Exchange 2000 / 2003 servers to reject email >destined for email accounts that no longer exist? &...

Maximum number of LUNs for Windows Server 2008 (non-R2)
Would anyone know what the maximum number of LUNs allowed by Windows Server 2008 32/64bit are? I recall reading somewhere that Server 2003 SP1 had a maximum of 256, but I cannot seem to find the 2008 information anywhere. Hi sBox, I personally haven't heard of an LUN limitation in Windows 2008 so far yet but what exactly do you want to achieve here ? Your first limitation will be the drive letters but this can be solved with mountpoints. Just for my interesting, what is your setup ? You should check "storage limits" on technet, if that does helps you. Regard...

Compare/Sepearte Values from Cell
I have a set of survey data in Excel format. Some questions are in the format of "check all that apply" and the answers i have are in a single column of cells with each answer seperated by a coma. The answer could also be "other". For some reason, the survey doesn't mark the answer as other, but simply record the data, so i can have answers like A,B,C C,E,custom input data1 A,custom input data 1 I want to get the data into seperate columns with binary indicating values like A | B | C | D | E | Other | Value of Other 1 1 1 1 ...

filter for values in another table
In my query i have records that contain a field named "id" from all the records in table a. In table b i have a list of ids that i would like to exclude from the list of records that return from table a in my query. Can i set the filter to look for the values in my table instead of typing out 50 ids in the criteria section? How would that be done? Sure, There are a couple of ways to do this. 1. The first, and probably fastest with regards to how quickly your query runs is to create a query that some people call a "frustrated outer join". In this type of query, yo...

How to Show/calculate End-values of Trendline?
Hello, (Excel 2007) I created a graph and a linear trendline, showing the equation: y = mx+b B determines the starting point. How can I get the value of the end of the trendline? Thanks for your help rgds Harry The b parameter (the "intercept" ) is value of y at x=0 (where the line crosses the y axis). It is not really an 'end'. To compute the value of y at any other value of x (let's say at a value of x stored in G3) use =slope*G1 + intercept. Now you could copy the slope and intercept values from the chart but you are better off using the SLOPE and INTER...

How to set/reset word wrapping in a CEdit control?
Hello everyone, How can I do it dynamically, from a menu option say? I know that if I change the multiline CEdit style to ES_AUTOHSCROLL, it will do it automatically. But how can I modify the CEdit style to enable/disable ES_AUTOHSCROLL dynamically? Thanks, Geo Take a look at ModifyStyleEx for that control variable. It will allow you to change the 'styles' of the control allowing for such change. Regards, James Simpson Thank you for your response. I tried it and it's not working. I added a menu item called "Word Wrap". Here's what I'm doing: 1. In t...

Setting Worksheet Name = to Cell in Worksheet #2
I appreciate the help, but I'm not sure where to put you recommendation. Which object and procedure do I use -- arwhitle ----------------------------------------------------------------------- arwhitley's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1631 View this thread: http://www.excelforum.com/showthread.php?threadid=27706 Hi you need VBA. This is some part of a macro procedure. Without VBA no chance -- Regards Frank Kabel Frankfurt, Germany "arwhitley" <arwhitley.1fimzz@excelforum-nospam.com> schrieb im Newsbeitrag news:arwhitley.1...

Non Excel Postings
Will the people who post non excel information as below, refraining fro doing so, start your own forum somewhere if you want to save the world. Thanks Thie -- Thie ----------------------------------------------------------------------- Thiem's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2747 View this thread: http://www.excelforum.com/showthread.php?threadid=49342 Please don't add to the level of noise in these groups! These ngs (if read via msnews or MS web portals) are remarkably free of spam and off-topic material due to MS's filters. Occasio...

#VALUE# error with Data Validation
Cells B10:K10 contain sales data. Cells B20:K30 display the percentage of Product A included in cells B10:K10. To reduce clutter in B20:k30, I added data validation that only permits entries if the corresponding row in B10:k10 contains numbers greater than zero. The problem is that cells B20:K30 are used in subsequent multiplications and, if they are left unpopulated as a result of data validation restrictions, I end up with a bunch of #VALUE# errors. Is there a work-around? KG wrote: > Cells B10:K10 contain sales data. Cells B20:K30 display the percentage of > Product A includ...

Return value of a point in a bar chart
anyone know how to set the value of a variable to the value of a poin in a chart. this dosen't work but I'm looking for something like this: x = ActiveChart.SeriesCollection(1).Points(6).Valu -- Eric_ ----------------------------------------------------------------------- Eric_B's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2448 View this thread: http://www.excelforum.com/showthread.php?threadid=52967 Sub One() MsgBox WorksheetFunction.Index(ActiveChart.SeriesCollection(1).Values, 6) End Sub Sub Two() Dim v As Variant v = ActiveChart.SeriesCol...

Setting OverWrite Mode in RichEdit
I need a way to force a rich edit control into overwrite mode. My current solution is to set a flag at creation, the first time I get an OnSetFocus, I send a VK_INSERT keystroke sequence via SendInput, and then intercept subsequent VK_INSERT calls so it can't be taken out of insert mode (it represents in-place hex editing). Anyone have a better solution? thanks joe Joseph M. Newcomer [MVP] email: newcomer@flounder.com Web: http://www.flounder.com MVP Tips: http://www.flounder.com/mvp_tips.htm Hi Joe, You could monitor the incoming characters and force them to update the one ch...

Diagram show blank as zero value when based on a formula
When a chart is based on a formula e.g. =IF(A1=0;"";A1*B1) then the chart will show the blank value "" as zero even if you whant to plot gaps or plot the empty cell interpolated. Yes, I know Excel say empty cell and the cell is not empty when there is a formula. Is there anybody who have found a "work around"? I often have formulas that generate zero in the result but I want to plot the result as if it was empty. I found the reply myself in a question from another user similar to this issue. I will use NA() instead of "" Thank you that works! &q...

How to convert a string value to byte
any idea on how do i convert a string value to an byte thank you, ben CString s = "hello"; byte* barr = new byte[s.GetLength()]; for(int i=0; i<s.GetLength(); i++) barr[i] = s[i]; //... delete[] barr; -- Regards, Nish [VC++ MVP] http://www.voidnish.com http://blog.voidnish.com "ben" <ben@buruso.com> wrote in message news:ufwS3O4VFHA.3840@tk2msftngp13.phx.gbl... > any idea on how do i convert a string value to an byte > > thank you, > ben > > Thank you very much. "Nishant Sivakumar" <nish@nospam.asianetindia.com> wro...

Comparing text and returning boolean values
Hi, I need to compare text values in one cell to text values in another range of cells. Basically I need a formula that tells me if the text in cell E406 equals the text in any of the cells between M3 and M403. I tried =E406=M3:M403, but that didn't work. If anyone knows how to do this please let me know! -- nicoleeee ------------------------------------------------------------------------ nicoleeee's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23442 View this thread: http://www.excelforum.com/showthread.php?threadid=389445 How about this: =ISNUMB...

Run IE8 with different connection settings (e.g. proxy, favourites)
Hi all, I use my laptop both at work and at home. I would like to personalize IE 8 in a way that at work I have my favourites sites and my proxy configuration, while at home I have different favourites, saved pwd and no proxy enabled? I know in firefox this is possible, wonder if it is the same for IE. Thanks, Mattia Always state your full Windows version (e.g., WinXP SP3; WinXP 64-bit SP2; Vista SP1; Vista 64-bit SP2; Win7; Win7 64-bit) when posting in a forum or newsgroup. Please do so in your next reply. gervaz wrote: > Hi all, I use my laptop both at work and at home...

hyperlink to value #2
frank, thanks for the reply. here is the situation: i use a macro to create hyperlinks. the hyperlinks show up a Sheet2!A1. i then use a line in the same macro to truncate the !A1, an the end result (visible) is Sheet2 (still a hyperlink). i would like t be able to use that value in a formula. is that possible? now, thi part of it needs to be done using a worksheet function only, as th formulae might have to be changed depending on the requirement. peopl not cognizant with macros might be wary of doing that. hence the nee for a worksheet function to convert this hyperlink value to tex val...