Page 1 of 2 12 LastLast
Results 1 to 15 of 26

Thread: Auto Numbers

  1. #1
    Join Date
    Apr 2008
    Posts
    24

    Unanswered: Auto Numbers

    I read somewhere that you could create an automatic number feature that didn't drop some numbers.

    This was not in reality autonumber but code that you used a number field that would regulary increase by one number for each new record.

    Any suggestions?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What do you mean by "drop some numbers"?

    If you are trying to give an arbitrary integer (surrogate key) meaning; you're doing something wrong
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there are techniques to generate an autonumber equivalent.

    it involves using record locks or table locks
    it requires a fair bit of understanding about VBA, locking and so on
    perhaps the best example Ive seen was in THE Sybex Acess Developer (Enterprise) edition
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2008
    Posts
    24
    I have a database the uses the autonumber for recording Invoice numbers.
    At time the next number will be dropped, or lost. I am not sure of the reasons. But from what I have read this does happen from time to time.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    It's normal. It happens when an invoice is deleted or the changes to a new invoice are abandoned.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The pont is you dont use autonumber fields for things that have meaning in the outside world IF the outside world demands that they must be in a specific order.

    if you have that requirement then you must devise an numbering system that you control. Autonumber in all DB's that Ive come across is not appropriate for this requirement. precisely because you will get gaps, you may get records out of sequence.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2008
    Posts
    24
    I realized that when I expierenced the problems encontered in the Invoicing db.

    What I would like to know is what is the best alternative?

    What vb code can I use? And does anyone have example of this?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by sandrao
    I realized that when I expierenced the problems encontered in the Invoicing db.

    What I would like to know is what is the best alternative?

    What vb code can I use? And does anyone have example of this?
    there may well be an example in the VB code bank in this forum
    there is an example in the Sybex developer book, but Im nto going to copy and paste it here as in my books its their work, their copyright, they deserve some credit (read cash) for their work
    there are almost certainly examples ion the web elsewhere, probably culled formt he same book, but thats their choice
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You can change the AutoNumber field to an Number field and then in your forms you can automatically set the primary key by doing a Max + 1 calculation in the form's Before Insert event procedure.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    You can use code for numbering, each time you add new record it counts the total number of records and increase it by 1 which is the new record number.
    You should think of when you delete a record, next time you add new record you will have duplicate Number so your code shall cycle trough your previous codes and see if it will be duplicate or not? Or you can add a prefix to your code which makes it unique say for example a mixture of date and time + record count (0505081645-1 which is 05 may 08 16:45 PM Number 1)

  11. #11
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    Here is a sample code:
    Code:
    Public Function FactorCoding() As String
    On Error GoTo Err_Handler
    Dim objConn As ADODB.Connection
    Dim objRST As ADODB.Recordset
    Dim SQL1 As String
    Dim i As Integer
    Dim Fdate As Date
    Fdate = Date
    
    Set objConn = CurrentProject.Connection
    Set objRST = New ADODB.Recordset
    
    SQL1 = "SELECT tbl_Sold.[Factor Code], tbl_Sold.[Sold Date] " _
        & "FROM tbl_Sold " _
        & "GROUP BY tbl_Sold.[Factor Code], tbl_Sold.[Sold Date] " _
        & "HAVING (((tbl_Sold.[Sold Date])=Date()));"
    
    objRST.Open SQL1, objConn, adOpenStatic, adLockOptimistic
    
    With objRST
        If (.BOF And .EOF) Then
            FactorCoding = Format(Fdate, "ddmmyy") & "-1"
        Else
            .MoveLast
            .MoveFirst
            i = .RecordCount
            FactorCoding = Format(Fdate, "ddmmyy") & "-" & (i + 1)
        End If
    End With
    
    objRST.Close
    Set objConn = Nothing
    Set objRST = Nothing
    
    Function_exit:
        Exit Function
       
    Err_Handler:
      MsgBox "An error has occurred in this application. " _
    & "Please contact your technical support person and " _
    & "tell them this information:" _
    & vbCrLf & vbCrLf & "Error Number " & err.Number & ", " _
    & err.Description, _
    Buttons:=vbCritical
        Resume Function_exit:
    
    End Function

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Aran1
    You can use code for numbering, each time you add new record it counts the total number of records and increase it by 1 which is the new record number.
    You should think of when you delete a record, next time you add new record you will have duplicate Number so your code shall cycle trough your previous codes and see if it will be duplicate or not? Or you can add a prefix to your code which makes it unique say for example a mixture of date and time + record count (0505081645-1 which is 05 may 08 16:45 PM Number 1)
    why would you iterate through a recordset to get the mximum number when you coudl use the MAX function

    iteratign through the recordset and countiung is especially dangerous as all you are doijng is countign the number of rows in the set...

    say if on ewas deleted then the count (with 100 rows would return 100), but if number 5 was delted then you would get 2 100's, or 101's dependign how uou implement the number
    there are no record locks in the algolrhytm.. so its perfectly possible for more than thread to get the same answer, because they were both trying to secure the next value at the same time.

    The solution I favour, for performance reasons is to stuff the next available (or last used) number in a support table (the one that has all the config crap, the other stuff that isn't actually required in a relational db but is a useful glory hole for all those one off data storage requirements (eg date report suite X last run, date oif last main data import.. y'get the picture

    get a lock on that record, get your number, increment the counter then release the record lock. You only get locking contentions when users want to get a new number.. access to the main table isn't affected by people trying to get new numbers locking others from using the main data store.
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ..of course you could just grab the last used number, add one to it, and put in an error trap when writign back.... if the update fails because of a primary key failure,m then add one to the primary key until you no longer get the error

    hardly an elegant solution, its brutish, it does force the data store to do a bit more but it is quick and nasty and it works
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    Brilliant, Thanx healdem, I will use your technic, good point , but I think we can't use MAX in unbounded forms, can we?
    using error trap is also very smart trick, thanx again.
    <<Never Walk on the Traveled Path, Because it only Leads you to where the Others have been.>>
    Graham Bell

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Aran1
    Brilliant, Thanx healdem, I will use your technic, good point , but I think we can't use MAX in unbounded forms, can we?
    using error trap is also very smart trick, thanx again.
    what makes you thik you can't use MAX

    it returns the maximum value for the specified criteria
    you can use that value anywhere, and in any way you wish

    the error trapping route isn't the smartest trick.. you'd be far better of writing a routine which designs out the need for the error trapping in the first place. you are imposing more load server and workstation(s)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •