Sequential number problem

[mjake13]

I am trying to generate an invoice number using the following code. It gets the number just fine, but I want to check the number generated against the table to make sure there are no duplicates, and if there is I want the number to increase by 1.

Private Function NextNumber() As String
Dim numRecs As Long
Dim Serial As String
Dim Report As String
Dim Sequential As String
Dim HowMany As Long

numRecs = DCount("*", "customers")
HowMany = numRecs + 1
Sequential = Left$(String(3, "0"), 4 - Len(Str(HowMany))) & Right$(Str(HowMany), Len(Str(HowMany)) - 1)
Report = DatePart("yyyy", Date)
Serial = Report & "1" & Sequential
NextNumber = Serial

End Function
____________________________
Private Sub btnInvoiceNo_Click()
Me.InvoiceNo = NextNumber
End Sub
_____________________

I would like the number to get checked when I click on the button for the invoice number, but I'm stumped (long day already). Any suggestions?

 

[rjoubert]

Look at the DLookup function.

http://www.techonthenet.com/access/functions/domain/dlookup.php

 

[mjake13]

I finally got it figured out. I put this code on the button to generate the new invoice number:

Private Sub btnInvoiceNo_Click()
Dim vartemp As String

Me.InvoiceNo = NextNumber

vartemp = Nz(DLookup("[InvoiceNo]", "Customers", "[InvoiceNo] = Forms![frmCustomers]![InvoiceNo]"))

If vartemp = NextNumber Then
Me.InvoiceNo = NextNumber + 1
Else
Me.InvoiceNo = NextNumber
End If
End Sub

 

[mjake13]

Spoke to soon. Ran into a problem with possible duplicate numbers. I found a bit of help and now use this code.

Private Sub btnInvoiceNo_Click()
Dim vartemp As String
Dim vartempa As String

Me.InvoiceNo = NextNumber
vartemp = Nz(DLookup("[InvoiceNo]", "Customers", "[InvoiceNo] = Forms![frmCustomers]![InvoiceNo]"))
vartempa = Nz(DMax("[InvoiceNo]", "Customers"), 0) + 1

If NextNumber = vartemp Then
Me.InvoiceNo = vartempa
Else
Me.InvoiceNo = NextNumber
End If
End Sub

Works by filling in missing invoice numbers if they were deleted and skips over any real invoice numbers if they exist.

 

[MichaelRed]

should check the faqs here. there are serious issues with generating values in these manners. In particular, hte use of any 'lookup' approach on the target recordset in a multiuers environment is almost certainly goiong to introduce duplicates as Ms. A (a.k.a Jet) does not have the locking mechanisim to avoid concurrent access to the individual record,




MichaelRed

 

[mjake13]

Unfortunately, I'm aware of that issue with a multi-user environment. Fortunately, this is a single user database someone needs. Thanks for the headsup though.

 

[TheAceMan1]

How are ya mjake13 . . .

If you didn't have to have the [blue]leading zero's[/blue] your invoice numbers could be numeric and you could simply use:

[blue]   DMax("[InvoiceNo]", "Customers") + 1[/blue]

Be aware: like a primary key an invoice number doesn't have to be the result of a mile long formula . . . [blue]it just has to be unique![/blue] I say make things easier on yurself and go numeric . . .

[blue]Your Thoughts! . . .[/blue]

[green]BTW: Welcome to tek-Tips![/green]

See Ya! . . . . . .

 

[mjake13]

I actually couldn't agree more with you Ace! But it was done at the customer request. He wanted his invoices to have a higher starting number. I guess he wanted to look a bit more professional. Thanks for the input.

 

[MichaelRed]

look into just formatting the results. It is easy to "present" the value with the 'real' number padded with any thing you want (including leading character strings of an arbitrary length). Several therads here (in Tek-Tips) discuss the approach and there is (at least should be) a routine (or several) showing ways to generate the "pads",




MichaelRed

 

[TheAceMan1]

mjake13 . . .

This post by me is [blue]all about the power of the programmer![/blue]

Over 137 DB's to date`, I've had to go thru the rigors of convincing cutsomers to see the light! . . . just to make it easy on myself!

Albeit customers know what they want, but they nevere see the light! . . . So many times I've hads to explain to them the difference!

Most come around, but its those few that took all you could give . . .

My point!: . . . talk to them . . . get them to see what you really can do1

I could elaborate on this more . . . but it depends on where you are! . . .

[blue]Your Thoughts![/blue]

See Ya! . . . . . .