INCREMENTAL NUMBERING SYSTEM

INCREMENTAL NUMBERING SYSTEM

SCENARIO: A certain table called API Temp Data needs to have an incremented numbering on ItemNo field. If we use autonumbering we cannot have multiple users entering at the same time in a given table. One item entered may contain number 1 by one user and the other user my have it’s entered item numbered as 2. In order to facilitate the automated numbering system starting with 1 with each user without using the autonumber of MS Acces is to create a procedure that will do the trick for you.

PROCEDURE:

The following is a portion of the procedure in the submit Record click event.

’***START Of INS Procedure

’***Programmer: Noel Jerome Q. Noel

‘***Contact: noeljltd@hotmail.com

‘1. Count the number of Records based on the ControlNo that is in the Form

RecCount = Nz(DCount("[ControlNo]", "Temp APIData", "[ControlNo]='" & Me.TxtControlNumber & "'"), 0)

‘2. If Record <> to zero then proceed to CallLoop

If RecCount <> 0 Then

GoTo CallLoop

Else

Me.TxtMultiTicketCounter.Value = 0

GoTo ExitingBasketProcedure

End If

CallLoop:

‘3. Set a Variable for the RecordSet you need to update

Set APITempDB = CurrentDb.OpenRecordset("SELECT * FROM [Temp APIData] WHERE ControlNo = '" & Me.TxtControlNumber & "'")

‘4. Initialize a counter called A

A = 1

‘5. Create a Loop Statement that will cycle through the recordset until the end of File

Do Until APITempDB.EOF

With APITempDB

.Edit

‘6. Each cycle updates ItemNo with counter A

!ItemNo = A

.Update

.MoveNext

End With

‘7. Counter A is incremented by 1

A = A + 1

Loop

‘8. Close the Recordset

APITempDB.Close

Me.TxtMultiTicketCounter.Value = RecCount

’***END Of INS Procedure