Results 1 to 8 of 8
  1. #1
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73

    Stop Counter Increment When Aborting Record Entry

    Hi all,

    When a record entry is aborted (pressing Esc), the Group_Count still stores the next increment number in the Group_Counts table. This is basically creating the same problem as using auto number where it misses out the next number after cancelling an entry.

    Is there something I need to write into the code to prevent this from happening? I'm not an expert with using code by any means, I stumbled across this useful module when searching online for examples and have adapted it to suit what I need.

    Could someone please possibly help and explain what I need to do to fix this issue? (Bearing in mind I'm a complete rookie at this stuff!!)

    Many thanks

    Option Compare Database


    Option Explicit


    Code:
    Function NewPart_Number(pGroup_ID) As String
    
    
        Dim db             As Database
        Dim LSQL           As String
        Dim LUpdate        As String
        Dim LInsert        As String
        Dim Lrs            As DAO.Recordset
        Dim LNewPart_Number   As String
        
        On Error GoTo Err_Execute
        
        Set db = CurrentDb()
        
        'Retrieve last number assigned for Group Type
        LSQL = "Select Group_Count from tbl_Group_Counts"
        LSQL = LSQL & " where Group_Prefix = '" & pGroup_ID & "'"
        
        Set Lrs = db.OpenRecordset(LSQL)
        
        'If no records were found, create a new Group Type in the Group_Counts table
        'and set initial value to 1
        If Lrs.EOF = True Then
        
            LInsert = "Insert into tbl_Group_Counts (Group_Prefix, Group_Count)"
            LInsert = LInsert & " values "
            LInsert = LInsert & "('" & pGroup_ID & "', 1)"
            
            db.Execute LInsert, dbFailOnError
            
            'New Part_Number is formatted as "SP-0001", for example
            LNewPart_Number = pGroup_ID & "-" & Format(1, "0000")
            
        Else
            'Determine new Part_Number
            'New Part_Number is formatted as "SP-0001", for example
            LNewPart_Number = pGroup_ID & "-" & Format(Lrs("Group_Count") + 1, "0000")
            
            'Increment counter in Group_Counts table by 1
            LUpdate = "Update tbl_Group_Counts"
            LUpdate = LUpdate & " set Group_Count = " & Lrs("Group_Count") + 1
            LUpdate = LUpdate & " where Group_Prefix = '" & pGroup_ID & "'"
            
            db.Execute LUpdate, dbFailOnError
            
        End If
        
        Lrs.Close
        Set Lrs = Nothing
        Set db = Nothing
        
        NewPart_Number = LNewPart_Number
        
        Exit Function
        
    Err_Execute:
        'An error occurred, return blank string
        NewPart_Number = ""
        MsgBox "An error occurred while trying to determine the next Part_Number to assign."
        
    End Function

  2. #2
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    Can you provide more specifics regarding where and how you're calling the NewPart_Number function? Something along the lines of "When the user is entering data on form A, the function is called in the After update event of control x". Provide as many details as possible if they may be relevant to the problem.

    One possible solution that comes to mind (without really knowing much about your scenario) would be to disable the Escape key and add your own Delete button where you can trap for invalid Group Count increments.

  3. #3
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73
    I'm using a continuous form where the user can select a Group Type from a combo box which calls the function in its "On Lost Focus" event. I'm not sure if the Close Form command is relevant here.

    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub cmdClose_Click()
    
    
        'Close form
        DoCmd.Close
        
    End Sub
    
    
    Private Sub cbo_Group_Select_LostFocus()
    
    
    'Assign Part_Number
        If IsNull(Part_Number) = True Then
            'A Group Type must be selected
            If IsNull(cbo_Group_Select.Value) = False Then
                Part_Number = NewPart_Number(cbo_Group_Select.Value)
            End If
        End If
    
    
    End Sub
    The Row Source for the combo box is SELECT [tbl_Group].[Group_ID], [tbl_Group].[Group_Desc] FROM tbl_Group ORDER BY [tbl_Group].[Group_Desc]; and it's Control Source is Group_ID.

  4. #4
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    Well I definitely wouldn't use the LostFocus event for this. That's going to fire every time a user tabs through that control regardless of the circumstances (i.e. whether it's a new or existing record and even if they don't update anything).

    My suggestion would be to use the BeforeUpdate event of the Form (this won't fire if the user hits the Esc key), and I would probably trap for a new record as well. Example;

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
        If Me.NewRecord Then
            'call your NewPart_Number function here
        End If
    
    End Sub

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

  6. #6
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73
    Quote Originally Posted by Beetle View Post
    Well I definitely wouldn't use the LostFocus event for this. That's going to fire every time a user tabs through that control regardless of the circumstances (i.e. whether it's a new or existing record and even if they don't update anything).

    My suggestion would be to use the BeforeUpdate event of the Form (this won't fire if the user hits the Esc key), and I would probably trap for a new record as well. Example;

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
        If Me.NewRecord Then
            'call your NewPart_Number function here
        End If
    
    End Sub
    Hi Beetle,

    I've switched the events around as you suggested above and this has solved the issue! Could you explain though what you mean by "trap for a new record" as I am not familiar with the terminology.

    Many thanks!

  7. #7
    Beanie_d83 is offline Advanced Beginner
    Windows 8 Access 2013 64bit
    Join Date
    May 2016
    Posts
    73
    Quote Originally Posted by orange View Post
    @Beanie_d83

    Here is info on autonumbers.

    Hi Orange,

    thanks for the link on autonumbers, some valuable information there that I will keep in mind for future reference!

    Luckily, the code I managed to find didn't make use of autonumbers. What I need to do is find a way to reverse the group count when an entry is aborted as the next part number is generated from this count so as soon as a record entry was started, the count was triggered to increment the group type causing the part numbers to be missed....if that makes sense?!

  8. #8
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    Quote Originally Posted by Beanie_d83 View Post
    Hi Beetle,

    I've switched the events around as you suggested above and this has solved the issue! Could you explain though what you mean by "trap for a new record" as I am not familiar with the terminology.

    Many thanks!
    My assumption is that you would not want the group count to increment if the user was simply updating an existing record in the form, so this line -

    If Me.NewRecord Then

    - means the code will only fire if the user has entered a new record.

    The phrase to "trap" for something basically just means that you are going to write your code based on the expectation of something happening (or perhaps not happening). In this example, code that is written in the Before Update event of a form is going to fire whenever there is any update to any record on that form unless we include code to control that behavior, in this case the NewRecord line.

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

Similar Threads

  1. Replies: 5
    Last Post: 08-30-2017, 09:24 PM
  2. Replies: 27
    Last Post: 12-02-2015, 06:09 PM
  3. Record counter.
    By Homegrownandy in forum Access
    Replies: 3
    Last Post: 10-21-2015, 07:02 AM
  4. Replies: 8
    Last Post: 07-06-2013, 05:13 PM
  5. Stop Duplicate name entry
    By bharatdubole in forum Access
    Replies: 1
    Last Post: 12-03-2012, 06:11 AM

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