Whenever I create an invoice, I need to assign it a unique number. There are several ways to do this (I’ll discuss other ways in future posts), but the way that I describe here uses a text file. I’ve placed a text file in a shared directory so I can use this from any computer. The text file holds the last invoice number that I’ve used. When I create another invoice, it writes the new invoice number (old invoice number + 1) back to the text file.
Dim FName As String
Dim FNum As Long
Dim LstInv As String
FName = sMYPATH & “LastInv.txt”
FNum = FreeFile
Open FName For Input As FNum
Input #FNum, LstInv
Range(“B18?).Value = CLng(LstInv) + 1
Open FName For Output As FNum
Print #FNum, CLng(LstInv) + 1
This example is pretty bare-bones. There’s no error checking to determine if someone has deleted the file, for instance. It also lacks the ability to maintain a proper sequence. I can get a new invoice number, but if I don’t save that invoice, I’ll have skipped a number.
For my purposes, the text file is deep in a directory (security by obscurity) and I don’t really care if my invoices are a little out of sequence, just that they’re unique and reasonably identifiable (that is, they’re not crazy random numbers).
I suppose to ensure the sequence is maintained, the BeforeSave event could be used to write back to the text file.