Numbering sequence for a report 1

[Gazonice]

Hello,

I am using the module (below) to add a unique serial number to Invoices being produced in batch print runs.

The code enables the report to print one page of an invoice with number 0001, then the next with 0002 and so on...

********************
Function FncInvoiceNo()

Dim rs As ADODB.Recordset
Dim NextCounter As Long

Set rs = New ADODB.Recordset

'Open the ADO recordset.
rs.Open "tblInvoiceNo", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

'Get the next counter.
NextCounter = rs!invoice_no

rs!invoice_no = NextCounter + 1

NextCounter = rs!invoice_no

rs.Update

Set rs = Nothing

FncInvoiceNo = NextCounter

End Function

********************

Can anyone tell me how I could alter it so that it increments at intervals, so that it enables the report to print the invoices 001, 001, 002, 002 or more..

Thank you

Regards,

Garry

 

[JimYellow]

add another feild to the table
store an integer 1,2,3,4,5...

then add one to this number to track the increment

and only add the next main increment when you hit your max value and reset the new field back to 1 or 0

 

[Gazonice]

I couldn’t see the wood for the trees.

A simple solution and quite elegant, my Thanks!

In case other users want to use a routine like this:

I added a line on the print command button that resets the second field to zero to synchronise the print numbers so they print:

1-1, 2-2, 3-3

and not:

1-2, 2-3, 3-4.

Regards,

Garry