Okay what I really need is an answer in simple step-by-step,
impossible-to-screw-up intructions for the following PROBLEM: Where do I
enter the minimum value for an AutoNumber? It starts at 1 which is obviously
inconvenient for a lot of applications.

I need an automatic numbering system that starts at 10000, it doesn't matter
if a number is skipped because someone deletes a record, just as long as the
numbers are sequential and don't use any numbers before the minimum (10000).

Please be patient with me and explain where I go to do the magic and what
tricks I'll need to perform because I'm very new to this.

Thanks,
--
~ Danny ~

Danny Baird wrote:
> Okay what I really need is an answer in simple step-by-step,
> impossible-to-screw-up intructions for the following PROBLEM: Where do I
> enter the minimum value for an AutoNumber? It starts at 1 which is obviously
> inconvenient for a lot of applications.
>
> I need an automatic numbering system that starts at 10000, it doesn't matter
> if a number is skipped because someone deletes a record, just as long as the
> numbers are sequential and don't use any numbers before the minimum (10000).
>
> Please be patient with me and explain where I go to do the magic and what
> tricks I'll need to perform because I'm very new to this.
>
> Thanks,

I think Allen Browne did that here:

http://allenbrowne.com/ser-26.html

James A. Fortune
MPAPoster@FortuneJames.com

Very helpful J.A.F., but like I said I need someone to explain the WHERE to me.

I mean that I really know very little about codes like that and that I have
NO idea WHERE to paste that sub.

If someone could puh-leeeeease give me the step-by-step instructions I would
be so thankful.
--
~ Danny ~


"James A. Fortune" wrote:

> Danny Baird wrote:
> > Okay what I really need is an answer in simple step-by-step,
> > impossible-to-screw-up intructions for the following PROBLEM: Where do I
> > enter the minimum value for an AutoNumber? It starts at 1 which is obviously
> > inconvenient for a lot of applications.
> >
> > I need an automatic numbering system that starts at 10000, it doesn't matter
> > if a number is skipped because someone deletes a record, just as long as the
> > numbers are sequential and don't use any numbers before the minimum (10000).
> >
> > Please be patient with me and explain where I go to do the magic and what
> > tricks I'll need to perform because I'm very new to this.
> >
> > Thanks,
>
> I think Allen Browne did that here:
>
> http://allenbrowne.com/ser-26.html
>
> James A. Fortune
> MPAPoster@FortuneJames.com
>

Danny Baird wrote:
> Very helpful J.A.F., but like I said I need someone to explain the WHERE to me.
>
> I mean that I really know very little about codes like that and that I have
> NO idea WHERE to paste that sub.
>
> If someone could puh-leeeeease give me the step-by-step instructions I would
> be so thankful.

O.K. First you need to put the word "Public" before the Sub:

Public Sub SetAutoNumber(...

Then copy and paste the subroutine into a new module. Note that the
table needs to have an AutoNumber field such as an AutoNumber Primary
Key called perhaps ID. The code should find it automatically. It might
be a good idea to run the subroutine before the table has any records in
it if you don't want original low numbers kept along with the new ones.

Then you can call the subroutine from, say, the On Click event of a
button on a form:

Public Sub cmdSetAutoNumberStart_Click()
Call SetAutoNumber("MyTable", 10000)
MsgBox("Done.")
End Sub

After that, any new ID values will start at 10000. The code also checks
to see if you already have ID values that are >= 10000 and allows you to
take appropriate action.

(for the lurkers):

In reality the values "MyTable" and 10000 would be from a combobox and a
textbox on the form. If I have a table called tblTables with a text
field called TableName I can fill a combobox with the table names of the
local tables starting with "tbl" in the database as follows:

Private Sub Form_Load()
Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim CountRS As DAO.Recordset
Dim tdfLoop As TableDef
Dim lngCount As Long
Dim I As Integer
Dim strSQL As String

'Need to get all the tables for the combo box

Set MyDB = CurrentDb
strSQL = "SELECT * FROM tblTables;"
Set CountRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
CountRS.MoveLast
lngCount = CountRS.RecordCount
CountRS.Close
Set CountRS = Nothing
DoEvents

If lngCount > 0 Then
'First delete the rows from tblTables
strSQL = "DELETE tblTables FROM tblTables"
DoCmd.Hourglass True
MyDB.Execute strSQL, dbFailOnError
DoCmd.Hourglass False
'Wait until the records have been deleted
If lngCount > 1 Then
Do While MyDB.RecordsAffected < 1
DoEvents
Loop
End If
End If

'Now refill tblTables
Set MyRS = MyDB.OpenRecordset("tblTables", dbOpenDynaset)
I = 1
For Each tdfLoop In MyDB.TableDefs
I = I + 1
If Left(tdfLoop.Name, 3) = "tbl" Then
MyRS.AddNew
MyRS("TableName") = tdfLoop.Name
MyRS.Update
End If
Next tdfLoop
Set MyDB = Nothing

'Then set the combobox Rowsource to tblTables
cbxTable.RowSource = "SELECT TableName FROM tblTables ORDER BY TableName;"
End Sub

James A. Fortune
MPAPoster@FortuneJames.com