unique invoice numbers

[bradmaunsell]

How can I set up (VBA) a method for assigning unique invoice numbers in a multi-user application?

I currently get the highest number from the table and add one. However, the number is occationally duplicated when more than one user hits the right timing.

Any suggestions?

Thanks

Brad
South Burlington, VT

 

[TomCologne]

Look here:

Why AutoNumber shouldn't be used in MultiUser databases (And How to ge the Unique Number)
faq700-184

TomCologne

 

[bradmaunsell]

I can't use autonumber for this application because this number has more processing. That is, a prefix and suffix are added and it is converted to text.

Also, if an auto number table gets screwed up, it can be a monster to fix. So, we have avoided the auto number here.

We do use autonumber in lots of other routines within this same insurance application.

I am thinking I need some kind of routine that sores a single number in a table. When a system calls for the next "Policy Number", that table gets locked until the form is populated and the record is saved back to the server.

I'm noot sure this will work yet. The two question I haven't figured out are can I lock the table until the save is complete. Second, will "docmd.save" immediately updat the "Policies" table at the server or does that happen when the user instance is closed?

Brad

 

[solo7]

See if this fulfills a need ...

Public Function GetIndex() As String
'---------- D Steadman 2003
On Error GoTo GetIndex_Error

'---------- This code generates a unique ID
'---------- based on the time and date this function was called and 3 randomly selected
'---------- capitalised letters from 'A' through 'Z'.
'
Dim StrIndex, StrRandLett(25) As String
Dim IntN As Integer

'---------- Populate an array with the capitalised letters 'A' through 'Z' by allocating
'---------- the ASCII character value to an array element.
'
For IntN = 0 To 25
    StrRandLett(IntN) = Chr$(IntN + 65)
Next IntN

'---------- Select 3 random numbers between 0 and 25 and select the corresponding array
'---------- element.
'
For IntN = 0 To 2
Randomize
StrIndex = StrRandLett(Int((25 * Rnd) + 0)) & StrIndex
Next IntN

'---------- Generate the current date and time and form it into a string.  tagging the
'---------- preselected random numbers on to the front.
'
GetIndex = StrIndex & "-" & (Format(Date, "ddmmyy") & Format(Time, "hhmmss"))


Exit_GetIndex:
    Exit Function

'---------- Error trap and or reporting
'
GetIndex_Error:
    MsgBox Err.Description
    Resume GetIndex_Error
   
End Function

solo7

 

[bradmaunsell]

Perhaps this over-complicates the question but here is some background info as to what I am trying to do.

Table of about 8,000 records. sampling of numbers we have:

FP61049C
FP61050B
FP61051A
FP61052F
FP61053D
FP61054C
FP61055C

As you can see, the numeric mid portion is equencial counting up. It is important that these numbers count count and that no numbers are missing. Auditors come down from the mother ship to verify this.

The prefix can be different subject to the applicable "insurance program". The suffix is determined based on the number of "policy renewals".

So, the numeric portion functions exactly like an autonumber field.

Since the current table has no autonumbers, perhaps I should develope that idea for this application.

 

[TomCologne]

Brad,

Title of faq is:

Why AutoNumber (shouldn't) should NOT be used in MultiUser databases (And How to ge the Unique Number)

TomCologne