Invoice Numbering

[Lez]

I am trying to get my system to +1 to the last number for invoice purpose. I
have tried:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.txtinv = Nz(DMax("[invoice#]", "[tblorders]")) + 1
End Sub

But it is not adding 1 to my next number.....has anyone got a better
suggestion?

TIA

Lez

 

[Rick Brandt]

I am trying to get my system to +1 to the last number for invoice
purpose. I have tried:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.txtinv = Nz(DMax("[invoice#]", "[tblorders]")) + 1
End Sub

But it is not adding 1 to my next number.....has anyone got a better
suggestion?

Is [invoice#] a numeric field or text? If it's text then DMax() will return the
maximum value alphabetically, not numerically and you will get "stuck" on the
"10".

This is because Access will coerce the "9" to 9 because of your plus sign
resulting in "10", but alphabetically "9" is still greater than "10" so all
records afterwards want to be "10".

I would also suggest that BeforeInsert is not a great event for this if you want
multiple users to be able to enter new records. It should work fine for a
singel user though. For multiple users the BeforeUpdate event is the best
because it is the only one where the record is saved at the end of the event.

 

[Luiz Cláudio C. V. Rocha]

If you cannot use an autonumber field (which would be the easiest solution),
you can try a code like this:

Function NextNum(YourTable As String, YourField As String)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb()
strSQL = "SELECT TOP 1 [" & YourField & _
"] FROM " & YourTable & " ORDER BY [" & YourField & _
"] DESC"
Set rst = db.OpenRecordset(strSQL)
With rst
If .RecordCount > 0 Then
.MoveFirst
NextNum = .Fields(YourField) + 1
Else
NextNum = 1
End If
.Close
End With

Set rst = Nothing
Set db = Nothing

End Function


Save it in a standard module, and call the function from your form:

Me.YourField = NextNumTbl("tblOrders","invoice#")

--
Luiz Cláudio C. V. Rocha
Coordenador de Projetos FórumAccess
São Paulo - Brasil
MVP Office
http://www.msmvps.com/officedev

 

[LJG]

Hi Rick, thanks for that, got it working now. However, I have also now got
another problem, which I wanted to avoid by not using autonumber, that is,
if the users cancels the operation, cmdClose then it still saves the number,
which means invoice numbers will not be sequential?

Any other suggestions?

Thanks

I am trying to get my system to +1 to the last number for invoice
purpose. I have tried:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.txtinv = Nz(DMax("[invoice#]", "[tblorders]")) + 1
End Sub

But it is not adding 1 to my next number.....has anyone got a better
suggestion?

Is [invoice#] a numeric field or text? If it's text then DMax() will
return the maximum value alphabetically, not numerically and you will get
"stuck" on the "10".

This is because Access will coerce the "9" to 9 because of your plus sign
resulting in "10", but alphabetically "9" is still greater than "10" so
all records afterwards want to be "10".

I would also suggest that BeforeInsert is not a great event for this if
you want multiple users to be able to enter new records. It should work
fine for a singel user though. For multiple users the BeforeUpdate event
is the best because it is the only one where the record is saved at the
end of the event.

 

[Rick Brandt]

Hi Rick, thanks for that, got it working now. However, I have also
now got another problem, which I wanted to avoid by not using
autonumber, that is, if the users cancels the operation, cmdClose
then it still saves the number, which means invoice numbers will not
be sequential?
Any other suggestions?

I don't understand. How can the number be saved if the user cancels the record?