Sequential numbers

[Sam]

I have a table/query/form with a field containing a number, i.e. 2006-111. I
need this number to automatically go the the next sequential number with each
entry to the form. How is the best way to accomplish this?
Thanks!

 

[Don Ireland]

Sam,

I had a similar issue with a database that was based on creating new job
numbers. I have a button I use to signal the "add new item" event. The
click code for the button is as follows:

Private Sub AddRecord_Click()
On Error GoTo Err_AddRecord_Click
Dim rs As Object
Dim NewNumber As Integer
Dim CurrentNumber As Integer

DoCmd.GoToRecord , , acNewRec

Set rs = Me.Recordset.Clone
If JustDeleted Then
JustDeleted = False
'reset flag and then do nothing
Else
If rs.EOF Or Not Me.NewRecord Then
'still don't do anything
Else
With rs
.MoveLast
CurrentNumber = .Fields("jobnumber")
NewNumber = CurrentNumber + 1
Me![JobNum] = NewNumber
End With
End If
End If

JobName.SetFocus

Exit_AddRecord_Click:
Exit Sub

Err_AddRecord_Click:
MsgBox Err.Description
Resume Exit_AddRecord_Click

End Sub

The key is the RS.recordsetclone area. It copies the information, moves to
the last record, looks at the number and increments it by some number (1).
The new value is then set into the field for the new record and the form
moves to the next field. The job number field is locked to prevent people
from changing numbers (if the PC assigns it, no need to change it right???).

Anyway, with a little modification, the above code should work for you.

 

[BruceM]

I assume that the first four digits are supposed to change to 2007 next
year, and the numbering sequence to start over at 001. This code is another
approach.
In the form's Current event:

If Me.NewRecord Then

Dim strWhere, strDate As String
Dim varResult As Variant

strDate = Format(Date, "yyyy")
strWhere = "DateCode Like """ & strDate & "*"""
varResult = DMax("DateCode", "tblYourTable", strWhere)

If IsNull(varResult) Then
Me.txtDateCode = strDate & "-001"
Else
Me.txtDateCode = Left(varResult, 5) &
Format(Val(Right(varResult, 3)) + 1, "000")
End If
End If

DateCode is the table field containing 2006-001, 2006-002, etc. txtDateCode
is the text box bound to that field.

Note that DateCode is the PK (or otherwise does not allow duplicates),
assigning the number in the form's Current event could create conflicts in a
multi-user environment if another user starts a record at about the same
time. There are ways of handling this, but I will wait to hear if it is a
multi-user environment.