Incremental field

[gwboolean]

I had previously thought to use an autonumber field with a user defined starting value (100000). The consensus advise is that doing that is not the best way to go. Although I did find that it is possible to do.

That being the case, I would like to know how one would make an integer field with a starting number of 1000000 auto increment?

[ItsMe]

A common way is to use DMax() function to determine the largest number in your table/Domain. But, that can have its drawbacks too.
https://msdn.microsoft.com/en-us/lib.../ff835050.aspx

[ssanfu]

In your previous post,

Another option: you could always use a custom autonumber field (ie roll your own numbers)

See BaldyWeb.com http://baldyweb.com/CustomAutonumber.htm

or Google "custom auto number in access"

[gwboolean]

ItsMe,

I took a look at that, but it appears a whole lot more complicated that what I am doing now. My database us run as a dataset in VB and I used the code below in the load event to achieve what I want. I am not thrilled about the way I went about it, but it seems less complicated than using DMax. To begin with a have a table that just has a record of existing item numbers, starting at 10000000. What I am looking for is a way to automatically do this in ACCESS without having to go through VB.

'Create CR for new document
Me.SetMBItemNumTableAdapter.FillByLastRecord(Me._M asterBase1_0DataSet.setMBItemNum) 'This is a query that goes to the last record in the item number table.
Dim intDocNum As Integer = CInt(txtInputDocNum.Text) + 1 'This takes the value placed in a form control after the query is run, adds 1 and then places it in a variable
Me.setMBItemNumBindingSource.AddNew() 'This creates a new record in the item number table
txtInputDocNum.Text = intDocNum 'This places the new variable value into the form control (From the save event this value is saved to the newly created record)
Me.LnkMBChgRequestBindingSource.AddNew() 'This creates a new record in the change request table
lblDocNum.Text = intDocNum 'This puts the variable value into the CR form control (From the save event this value is saved to the newly created record)


ssanfu

I looked at your info and I get it now. I didn't look carefully enough before and missed the following:

The proper way, then, is to create your own function that creates the number sequence you need.
Have the last¹ number stored in a table. It would probably be a good idea to make this a one record table, with this value the only value in the record. A one-value table.

Based on that I think that all I need to do is use the VB method that I already have, but do not add a record every time I need a new number. Just replace the old with the new. I had not thought about it before, but there is no need to keep a record of the numbers previously used unless I want to reconstitute an obsoleted number (I used to do that, but I have no idea why). And when you have millions of numbers you can use why bother with obsoleted numbers? Beautiful man. Thanks for forcing me to take a second look at that.