restrict autonumber values

[Laurie]

I've been asked to create an autonumber field that will restrict values to
increments of 60 records (0-59,100-159,200-259, etc.). No values with last 2
digits of 60-99.

Is there any way to do this?

 

[Jeff Boyce]

Laurie

If you (and your requester) have Access Autonumber data type in mind,
probably not. The Access Autonumber is automatically generated and is
intended to be used as a unique row/record ID ... so what value it is really
doesn't matter. Autonumbers are generally unfit for human consumption.

But you could certainly create a procedure (function) that returns the
"next" available number, given the restrictions you outlined. Plan on
spending some time muckin' about in the code...

(that said, you could probably come up with a way to add a validation test
on an Autonumber-defined field to get the numbers constrained as you
described, but all things considered, don't!)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I've been asked to create an autonumber field that will restrict values to
increments of 60 records (0-59,100-159,200-259, etc.). No values with last 2
digits of 60-99.

Is there any way to do this?

No, not with an Autonumber.

You can use a Long Integer field and assign its value in VBA code, say in the
form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iNext As Integer
iNext = DMax("[ID]", "[yourtable]")
If iNext MOD 100 <= 60 Then
Me!txtID = iNext + 1
Else
Me!txtID = 100* (iNext \ 100) + 100
End If
End Sub

This assumes that there's at least one record in the table already; that the
field in question is named ID, and that there is a textbox on the form named
txtID bound to that field.

 

[Clifford Bass]

Hi Laurie,

Nice question!! You can do it with an autonumber field. It will need
to be done in the form's before insert event. Here is the code for a table
named "tblSkippingSomeAutonumbers" with an autonumber field named "RecordID"
and that has a required field named "OtherField".

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim boolChangeNextRecordID As Boolean
Dim boolInTransaction As Boolean
Dim lngNextRecordID As Long
Dim varLastRecordID As Variant

On Error GoTo Handle_Error

varLastRecordID = DMax("RecordID", "tblSkippingSomeAutonumbers")
If IsNull(varLastRecordID) Then
' No records, cause the next RecordID to be 0
lngNextRecordID = -1
boolChangeNextRecordID = True
Else
If varLastRecordID Mod 100 >= 59 Then
' Last RecordID's value is n59, cause the next one to start at
[n+1]00
lngNextRecordID = (varLastRecordID \ 100) * 100 + 99
boolChangeNextRecordID = True
Else
boolChangeNextRecordID = False
End If
End If
If boolChangeNextRecordID Then
With CurrentProject.Connection
.BeginTrans
boolInTransaction = True
.Execute _
"insert into tblSkippingSomeAutoNumbers " & _
"(RecordID, OtherField) " & _
"values " & _
"(" & lngNextRecordID & ", ""X"")"
.Execute _
"delete from tblSkippingSomeAutoNumbers " & _
"where RecordID = " & lngNextRecordID
.CommitTrans
boolInTransaction = False
End With
End If

Exit_Sub:
Exit Sub

Handle_Error:
MsgBox Err.Number & ": " & Err.Description
If boolInTransaction Then
CurrentProject.Connection.RollbackTrans
End If
Cancel = True
Resume Exit_Sub

End Sub

Note that you specified that it start with 0, which is not normal for
autonumbering, so the code takes that into consideration. Also, note that
you will have to adapt the code to your specific table and that table's
fields, including all fields that are required. I am not sure what would
happen if two people are trying to add a record at the same time when the
last number used ended in 59. You will need to test that.

Clifford Bass