Autoincrement Invoice Number? 2

[Dinho]

I am setting up an invoice manage system with access.

I have a table for nominatives; one for the each invoice related to his name through id_nom.

But i have a problem; it is necessary that every single invoice has a single assigned progressive number (not the autoincrement number supported by access, which sometimes forget numbers!!!).

Is it possible to use a new table with a single row?
How can refer to it, write and read from it through code?

Bye,
Dinho

 

[jimmythegeek]

Yes, you can use a "Counter" table, I have done it several times myself.

Create a table named "tblCounter"
Make one long integer field named "Counter"

Now copy the following code into a new module, close and save the module as anything you want:

==========
Public Function GetCounter(Optional increment As Boolean = True) As Long
Dim lngCounter As Long

lngCounter = DLookup("Counter", "tblCounter&quot

If increment Then
Set db = CurrentDb
db.Execute "UPDATE tblCounter SET Counter = " & lngCounter + 1
db.Close
End If
GetCounter = lngCounter
End Function
==========

This function serves two purposes:
1) It allows you to retrieve the counter
2) It allows you to retrieve the counter AND increment it.

The function will automatically increment the number every time you use it unless you pass a "false" argument, then it will only retrieve the number.

Let's say you want to set an invoice number equal to the counter, and increment it. You simply to the following:

MyField = GetCounter()

If you just want to know what the counter is at right now, you do this:

GetCounter(False)

Clarification: The AutoNumber in Access will never forget a number. What happens is when a record gets deleted, that number will never be used again, thus it seems to "dissapear" from the table. Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[braindead2]

Jimmy Great function! may be what I was looking for as well.
How well does this perform in multi-user environment?

The way I set it up was in the fields default value I call the function with a false value (number displays) then on the after update I call the function with a true value(it saves the number). I found if I do it on the insert event then if I undo a record the counter increments and skips a number. My concern is in a multiuser environment the number that was displayed when I inserted the record may change when I save the record.
It is important that a number is not skipped as well as have the number display on the form as the rest of the record is created.
I would be interested in how it's handled this. Thanks

 

[jimmythegeek]

As far as the Multiuser situation goes, it could be a problem if you have a very high number of transactions. Two people would have to hit the function exactly at the same time, although unlikely, it is possible. I suppose you could run tests on it, having multiple folks trying to update something simultaneously, and see what error (if any) you will get. If you can determine an error, let me know, and we may be able to code around it. Jim Lunde
compugeeks@hotmail.com
Custom Application Development