Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2014
    Posts
    42
    Provided Answers: 1

    Unanswered: VBA undo not resetting primary key

    Hi all,

    I am having an issue in a db I am building and recreated the same issue in the following simple example database (happy to upload if you guys can tell me how) that only has a table called "tblUsers" with "UserID,UserName,FirstName,LastName" as the fields (UserID is primary field" and form called "frmUsers"

    On the form, I only have a textbox for each field and two buttons-- OK and Cancel.

    In every other db I have ever built, I have entered the below code on the on click event for the cancel button and the form has cancelled just fine. In this example, if I enter data for a new record, say in the UserName field, and press cancel the entry will be undone, preventing a new record from being created, but my primary key will not stay at its current value. For example, if I have one record in tblUsers, go to enter a new record with the form, the new UserID will be 2. When I press cancel, I still have 1 record, but when I go to enter a new record my primary key is now 3.

    Any ideas on what is happening? Please let me know if I can clarify anything. Thanks

    Code:
    Option Compare Database
    Private Const conMod = "frmUsers"
    
    Private Sub cmdCancel_Click()
    On Error GoTo Err_Handler
        'Purpose:   Undo and close
        
        If Me.Dirty Then
            Me.Undo
        End If
        
        DoCmd.Close acForm, Me.Name
        
    Exit_Handler:
        Exit Sub
        
    Err_Handler:
        Call LogError(Err.Number, Err.Description, conMod & ".cmdCancel_Click")
        Resume Exit_Handler
    End Sub
    
    Private Sub cmdOK_Click()
    On Error GoTo Err_Handler
        'Purpose:   Save and close.
        
        DoCmd.Close acForm, Me.Name
        
    Exit_Handler:
        Exit Sub
        
    Err_Handler:
        Call LogError(Err.Number, Err.Description, conMod & ".cmdOk_Click")
        Resume Exit_Handler
    End Sub

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by pdevito3 View Post
    Any ideas on what is happening?
    Is the UserID an Autonumber? If so, this is normal behavior, and shouldn't really matter! Autonumbers are intended to be used for one purpose and only one purpose, to provide a unique identifier for each record.

    If you want to your 'UserID' to be consecutive, without missing numbers, you'll have to redefine the Datatype of the Field to Text or Number/Integer (despite the "numbers" it contains, these types of Fields are frequently defined as Text) and develop a hack for generating an auto incrementing 'UserID'.

    Note that these hacks cannot be used with Data Entry set to Yes! In that case the RecordCount is always 0 when a new session is started, regardless of the number of Records in the Table.

    Here's a typical Auto-Incrementing Number hack. 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

    Here's the same code for an IDNumber defined as a Number:

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

    The above has been used safely for Multi-User environments, in my experience, because it doesn't assign the ID number until the very last second before the record is saved. This is necessary to decrease the chance of two users getting the same ID number. The only drawback is that the user cannot see the ID number until the record is saved.

    In a Single-User environment, the code can be moved to the Form_Current event and the ID number will appear as soon as a new record is started.


    Linq ;0)>
    Last edited by Missinglinq; 11-11-15 at 23:36.
    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
  •