Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2009
    Posts
    7

    Unanswered: New Record incrementing instead of deleting

    I have a continuous form in Access 2002. Each record is a task to be completed and has an ID field that is an autonumber. I also have a “Date Entered” field that is filled with the current date if it is a new record as follows:
    --------------------------------
    Private Sub Form_Current()

    If Me.NewRecord Then
    [Date Entered] = Date
    End If

    End Sub
    ---------------------------------

    However if someone is entering a new task and decides to delete the record, it increments the ID autonumber and has another new record instead of deleting and going back to previously existing record.

    Any ideas?

    Thanks.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That's how autonumber is supposed to work.

    It sounds like you're looking for some kind of sequence number that has a specific meaning. You'll need to maintain that on your own if that's what you're after.

    In general, your primary keys should have no intrinsic meaning. Otherwise you end up in debacles like this.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Autonumbers are intended to be used for one purpose and only one purpose, to provide a unique identifier for each record. Here's a post I've archived from a gentleman named John Vinson, MVP, explaining how autonumbers work:

    When using Autonumber, do be aware that there will be gaps in the numbering - any record that's deleted will leave a gap; hitting <Esc> after starting a record will leave a gap; adding records using an Append query may leave a gap, often a huge one; replicating the database will make your invoice numbers random.

    In short... it's best NOT to use Autonumbers for human consumption, and particularly not for consumption by accountants and auditors. Invoice sequences like 319, 321, 322, 385, 386, 221841246, -1083225152 make such people get very nervous.
    For the kind of field you're talking about you need to use an auto-incrementing number hack such as one of these. The first code here would be for an IDNumber that is defined in the table as Text datatype.

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord Then
      If RecordsetClone.RecordCount = 0 Then
       Me.IDNumber = "1"
      Else
       Me.IDNumber = DMax("val([IDNumber])", "YourTableName") + 1
      End If
    End If
    End Sub
    If you are using a Numerical datatype field:

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord Then
      If RecordsetClone.RecordCount = 0 Then
       Me.IDNumber = 1
      Else
       Me.IDNumber = DMax("[IDNum]", "YourTableName") + 1
      End If
    End If
    End Sub
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Aug 2009
    Posts
    7
    Thank you for the advice. Yes, I was using ID so that each particular task could be easily referenced.

    I was able to solve it by moving to the On Dirty event instead, and now it performs as desired.

    Thanks again.

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You were able to solve your problem by moving what to the OnDirty event? The code assigning a value to the [Date Entered] field has nothing to do with your autonumber ID field. And moving the code you originally posted to the OnDirty event accomplished nothing; it was fine where you had it originally.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Aug 2009
    Posts
    7
    I moved the following from the On Current event to On Dirty event.

    ---------------------
    If Me.NewRecord Then
    [Date Entered] = Date
    End If
    ----------------------

    I moved the code to On Dirty with the idea that a new record wouldn't be created until a value was selected or typed in another text box or combo box, instead of immediately when the new record got focus. That way if the record being added is deleted, another new record isn't immediately created due to having focus, but rather requires input indicating that a user is indeed wanting to fill in a new record.

    I'm not sure if I'm being clear enough or if my understanding is accurate, maybe you could provide insight as to why the On Current event was giving problems..?

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    A new record is not created, simply because you dump a record but don't save it, and if the user enters a single character in any control and then dumps the record, you'll find that the autonumber field will exhibit the same behavior you said you do not want, there'll be gaps in the numbers.

    In order to prevent this in your ID numbers you'll have to do as Teddy and I have advised you to do, maintain your own IDs using an auto-incrementing procedure.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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