none
Unique Invoice Number?

    Question

  • Hi, I need to create a field with a unique number that will increase by 1 for every new record, I know this is possible with an auto number, however I want to start the number from an existing number.. How would I go about accomplishing this task? Kind Regards Michael
    Saturday, July 09, 2011 1:35 PM

Answers

  • Hi Michael,

    Imb is right about using a Form. Tables do not enable you to do this without reseeding an Autonumber. Reports are for reading data, not entering it. Forms exist to enable us to enter data such as the incremental ID number you want to use.

    John Viescas, Author and Access MVP, in addition to some that have answered you here, has used this technique for years so it is proven to be safe and effective.

    To use a Form you have to use a Form's Event. On your Form's Properties sheet click on the Events tab and click to the right of the Before Update Event. Next select Code when the dialog pops up. The Visual Basic Editor will open. Then between Private Sub Before_Update() and End Sub paste or enter the follwing code:

    'This code uses a control on a Form
    
    'This table does not use AutoNumber
    
    'Check the ID control to see if we need to supply an ID
    
    If Len(Me.txtMyID & vbNullstring) = 0 Then
    
     'Use Nz to avoid a null
    
     'Get the highed ID number in the Table using DMax and add 1
    
     Me.txtMyID = Nz(DMax("MyID", "tblMyTable"), YOUR STARTING NUMBER HERE) + 1
    
    End If
    

    Replace YOUR STARTING NUMBER HERE with whatever number you want to start with, for example, 1000.

    Rename txtMyID in the code to the name of the control on your Form that will have your ID Number.

    In your Table replace MyID in the code with the name of your ID field. Set the Field to Number not Autonumber.

    If there is anything here you do not understand please let us know.


    Patrick Wood Gaining Access http://gainingaccess.net
    • Marked as answer by Bruce Song Tuesday, August 09, 2011 6:25 PM
    Monday, July 11, 2011 1:13 PM

All replies

  • I got this code from MVP John Viescas and modified it by using Nz. If used with an ID control on a form I normally I set the ID control's Locked property to True so the user cannot change it. It can also be used with a Recordset or Query
     
    'This code uses a control on a Form
    'This table does not use AutoNumber
    'Check the ID control to see if we need to supply an ID
    If Len(Me.txtMyID & vbNullstring) = 0 Then
     'Use Nz to avoid a null
     'Get the highed ID number in the Table using DMax and add 1
      Me.txtMyID = Nz(DMax("MyID", "tblMyTable"), 0) + 1
    End If
    
    'If you are setting the initial value
    Me.txtMyID = 1000 'Or whatever value you want
    
    'If you are using a value in a query or recordset you can use a variable
    Dim lngID as Long
    lngID = Nz(DMax("MyID", "tblMyTable"), 0) + 1
    
     

    Patrick Wood Gaining Access http://gainingaccess.net
    Saturday, July 09, 2011 3:14 PM
  • Roger Carlson has a simple solution for this, catering for both single and multi-user environments, at:

    http://www.rogersaccesslibrary.com/forum/topic395.html

    and there's a more complex one of mine, which allows for the next number to be used to be 'seeded' (as you want to do) at any time at:

    http://community.netscape.com/n/pfx/forum.aspx?nav=libraryMessages&tsn=1&tid=23839&webtag=ws-msdevapps

    I've also amended Roger's to allow numbers to be seeded, by changing his GetProductID() function in the product_2 form's module as follows:

    Private Function GetProductID()

        Dim lngProductID As Long
        Dim lngSeedID As Long
       
        lngProductID = Nz(Dmax("ProductID", "Product"), 0) + 1
        lngSeedID = Nz(Dlookup("Seed", "Seeds"), 0)
       
        If lngSeedID <= lngProductID Then
            GetProductID = lngProductID
        Else
            GetProductID = lngSeedID
        End If
       
    End Function

    This requires the addition of a one-row table, Seeds, to the database, with a single column, Seed, of long integer number data type.  Enter a row with a zero value to start with.  The number you wish to 'seed' is entered into this table, for which a simple form can be designed, setting its AllowAdditions and AllowDeletions properties to False (No) so that only the one existing row can be edited.   To open this form I've added a button to the Product2 form with the following code in its Click event procedure:

        DoCmd.OpenForm "frmSeeds", WindowMode:=acDialog
        Me.ProductID.DefaultValue = """" & GetProductID & """"

    This amendment to Roger's solution makes mine pretty much redundant.  There could be some situations where it is more bullet-proof, principally that the user will see the new number when they begin to insert the new record and it will not change, whereas with Roger's, if there is a conflict the number will change for any user who is not the first to save the record with the new number; but by and large Roger's solution, amended as above to allow seeding, will be fine and is a lot simpler to implement.

    Ken Sheridan, Stafford, England
    Saturday, July 09, 2011 5:37 PM
  • I neglected to mention that you use the code for the control, Me.txtMyID, in the Form's BeforeUpdate Event.

     


    Patrick Wood Gaining Access http://gainingaccess.net
    Saturday, July 09, 2011 6:03 PM
  • Hi,

    For small tables I mostly use the same technique with DMax to retrieve the highest used number.

    For large(r) tables I tend to use a different approach. In all the applications I have a general purpose tblSettings, containing two-field records: Setting and Value. This table could have a record with Setting = "Last_invoice", and Value = 1234.

    Two specialized functions work on this table to read or write the appropriate values:

    last_value = Get_setting("Last_invoice")

    Set_setting "Last_invoice", new_value

     

    Imb.

    Saturday, July 09, 2011 6:08 PM
  • Hi,

    thanks for all your input.. alas i am still a little confused.

    lets say i have a table called 'table1', the field i wish to have the incremental unique number in is called 'inc num'.

    -where would i put the code for this? i.e. table or report design?

    -could someone create code based on this and highlight the area's which would need to be adjusted i.e. object and field names so that i can work it out against my work.

    thankyou :)

    Kind Regards

    Michael

    Monday, July 11, 2011 9:26 AM
  • Hi Michael,

    Your Table1 should be filled (or modified) through a Form.

    In the OnCurrent event of the form you can check the NewRecord property. If True, then set the Invoicenumber, incremented by 1.

    Please read your Help to understand how OnCurrent and NewRecord work.

     

    Imb.

     

    Monday, July 11, 2011 11:11 AM
  • Hi Michael,

    Imb is right about using a Form. Tables do not enable you to do this without reseeding an Autonumber. Reports are for reading data, not entering it. Forms exist to enable us to enter data such as the incremental ID number you want to use.

    John Viescas, Author and Access MVP, in addition to some that have answered you here, has used this technique for years so it is proven to be safe and effective.

    To use a Form you have to use a Form's Event. On your Form's Properties sheet click on the Events tab and click to the right of the Before Update Event. Next select Code when the dialog pops up. The Visual Basic Editor will open. Then between Private Sub Before_Update() and End Sub paste or enter the follwing code:

    'This code uses a control on a Form
    
    'This table does not use AutoNumber
    
    'Check the ID control to see if we need to supply an ID
    
    If Len(Me.txtMyID & vbNullstring) = 0 Then
    
     'Use Nz to avoid a null
    
     'Get the highed ID number in the Table using DMax and add 1
    
     Me.txtMyID = Nz(DMax("MyID", "tblMyTable"), YOUR STARTING NUMBER HERE) + 1
    
    End If
    

    Replace YOUR STARTING NUMBER HERE with whatever number you want to start with, for example, 1000.

    Rename txtMyID in the code to the name of the control on your Form that will have your ID Number.

    In your Table replace MyID in the code with the name of your ID field. Set the Field to Number not Autonumber.

    If there is anything here you do not understand please let us know.


    Patrick Wood Gaining Access http://gainingaccess.net
    • Marked as answer by Bruce Song Tuesday, August 09, 2011 6:25 PM
    Monday, July 11, 2011 1:13 PM