Results 1 to 7 of 7
  1. #1
    gwboolean is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    65

    How to deal with auto incremental number fields

    I am working using Visual Basic (12) to work my database. I have one particular table that is used for change requests (you don't need to know what that is, but if you are interested I am happy to tell you what that is). For this table I use an autonumber field. The first record would be 1, with each record being set to increment by 1.

    So, when I create a new record, in the VB code I create the record like so:

    'Create new change request number
    Try
    Me.LnkChangeRequestTableAdapter.FillByLastCRNum(Me ._MasterBase3_0ItemMasterDataSet.lnkChangeRequest)
    glbintCRNum = _MasterBase3_0ItemMasterDataSet.lnkChangeRequest(0 ).intChangeID
    glbintCRNum += 1
    Catch ex As Exception
    glbintCRNum = 1
    End Try

    The query being used (FillByLastCRNum), goes like this

    SELECT TOP 1 intChangeID
    FROM lnkChangeRequest
    ORDER BY intChangeID DESC



    This works fine, as long as there have never been any records deleted. In normal circumstances, for this table, no record should ever be deleted. But sometimes crap happens whether you want it to or not and a record, or records, or even the entire set of records can be deleted. Additionally, I do not believe this to be a very good approach even under perfect circumstance, since the query merely uses intChangeID from the last created and uses that to provide a value for glbintCRNum.

    It occurs to me that perhaps I should not be using an auto number field for this type of activity. However, it is also my thought that this is exactly the type of activity that an auto number field should be used.

    So could someone explain to me how to query a table using an auto number field so that it can be worked to ensure continuity should there be missing records (I believe the answer is in the query.)? Or offer a different approach than using an auto numbered field?

    If my description is lacking in content or the explanation is not coherent please let me know and I will try to do a better job of explaining.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If you need the value to increase sequentially without gaps, then autonumber cannot be relied on. Programmatically generating unique sequential identifier is a common topic.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    gwboolean is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    65
    I found it part of it (for the case of using an autonumber fields). All that is needed is to setup a scaler query. The same SQL parameters would be used for the Query as above:

    SELECT TOP 1 intChangeID
    FROM lnkChangeRequest
    ORDER BY intChangeID DESC

    And in the VB code the query would be called something like:

    VariableName = CInt(Me.LnkChangeRequestTableAdapter.qryNewCRNum)

    However, The try/Catch, as it is above, would still not deal with the situation where there had been a record set and they had all been deleted, because it is obvious that a conflict would occur.

  4. #4
    gwboolean is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    65
    June,

    I have not had that experience with autonumber. It has always provided me with the next incremental number. Whether it is reliable or not, the question for me now, if I use an auto increment, is how to add a new record and get the next incremental value into the new record if all the records have accidentally been deleted.
    I have run some tests and I know that if you delete all existing records, and you create a new record the next incremental number will be assigned. However, the problem arises when this new record is displayed in the form I am working with, as the control for the new record will display 0 instead of the incremented number that I know already exists.
    I have thought about just not using an autonumber field, but if you lose all of your records you would never know it.
    I still have a couple of tricks up my sleeve that I am going to try, but any ideas you might have would be appreciated.

  5. #5
    gwboolean is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    65
    OK, I have come up with the solution for using the autonumber field where all records have been lost. So here is the situation. You need to start with a try/Catch

    Try
    'Create new id number
    glbintCRNum = CInt(Me.LnkChangeRequestTableAdapter.qryNewCRNum)
    glbintCRNum += 1
    'Populate form controls for new item change request
    Me.LnkChangeRequestBindingSource.AddNew()
    lblCRNum.Text = CStr(glbintCRNum)
    Catch
    Me.LnkChangeRequestBindingSource.AddNew()
    Me.LnkChangeRequestBindingSource.EndEdit()
    Me.LnkChangeRequestTableAdapter.Update(Me._MasterB ase3_0ItemMasterDataSet)
    glbintCRNum = CInt(Me.LnkChangeRequestTableAdapter.qryNewCRNum)
    lblCRNum.Text = CStr(glbintCRNum)
    End Try

    Kind of clunky I admit, but what you get in the form is the next increment of the autonumber field even if all of the records in the table have been lost. This would be important for me, since record integrity is very important for this table and it would obvious to me that all of the previous records have been deleted. However, this would not help in spotting anything less than a complete record deletion.
    Come to think about it, all of this could be accomplished just as easily without an autonumber field, so perhaps I really should reconsider whether that is what I want to use. This does solve the problem though.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #7
    gwboolean is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    65
    I looked at the info your provided and between you and June, I am convinced and will go another route. I will explain what that is, but I bet to differ with the two of you on one thing.

    I have used autonumbers quite a bit and have ran lots of tests on them. While you can lose numbers through the loss of records, the sequencing (incrementing) of the numbers have been, in my experience flawless. I have routinely wiped all the records or some of the records from a table while developing an application and there has not been a single time that the next number, when a new record was created, was not the correct number. Nuff of the beg to differ.

    In the case of this table, which is a table full of change request records, all I need is a unique number for the ID of each change request and the ID field must also be the Primary key. It need not be sequential, but I like it like that and it makes things much cleaner if it is. However, because of some of the weaknesses of using an autonumber, and the fact that Visual Basic does not often respect any property settings done in Access, I am going to use the same methodology that I use for ID numbers for parts.

    What I have done with that is I have a ID number table that contains a single number field and a single record. I manually set the record with a starting number (it can be anything I want). So the current number in the record is extracted and used for the ID number (in this case it is placed into the ID field of my change request record) and then the this ID number is incremented by 1 (or whatever I choose) and is placed back into the ID number table. This has worked very well for me and I believe it is the best approach for my change requests as well. Anyway, thanks for the input and the information on autonumbers. It was very useful.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 28
    Last Post: 08-08-2017, 03:27 PM
  2. Replies: 3
    Last Post: 06-08-2017, 08:03 PM
  3. auto number fields
    By gwboolean in forum Access
    Replies: 8
    Last Post: 09-17-2016, 02:12 PM
  4. Replies: 11
    Last Post: 11-11-2014, 06:04 PM
  5. Incremental Number in a Query
    By jmauldin01 in forum Access
    Replies: 5
    Last Post: 01-01-2014, 01:23 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums