Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Invoice Numbering

Discussion in 'Information Technology' started by Lez, Jul 28, 2006.

  1. Lez

    Lez
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Shabad Numbering System New to Sikhism Dec 1, 2015

  3. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    Lez wrote:
    > 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, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     
  4. Luiz Cláudio C. V. Rocha

    Luiz Cláudio C. V. Rocha
    Expand Collapse
    Guest

    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


    "Lez" wrote:

    > 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
    >
    >
    >
     
  5. LJG

    LJG
    Expand Collapse
    Guest

    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
    "Rick Brandt" <rickbrandt2@hotmail.com> wrote in message
    news:I3qug.173096$F_3.163628@newssvr29.news.prodigy.net...
    > Lez wrote:
    >> 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, Microsoft Access MVP
    > Email (as appropriate) to...
    > RBrandt at Hunter dot com
    >
    >
    >
    >
    >
     
  6. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    LJG wrote:
    > 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?

    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     

Share This Page