Results 1 to 10 of 10
  1. #1
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84

    Error trapping problem in subform

    A portion of my database has a subform where the user can fill in a series of fields recording services provided. Some of the types of services provided have dollar amounts associated with the provision of the service so the expense associated with the service is tracked. Other services have no money associated with providing the service so the user is prevented from entering a dollar amount for that line of service. The subform is a dataentry sheet. Afterr the user picks the service provided, code runs to allow them to fill in the remainder of the fields as appropriate. Specifically, if the service provided has no money associated with it, the user can't enter a dollar amount in the amount field. Here is the code:



    Code:
    Private Sub CmboService_AfterUpdate()
    
    On Error GoTo Err_CmboService_AfterUpdate
    'create a recordset values that corresponds to the services that have tracked expenses.  Only enable the amount field if this is true.
    Dim strsql As String
    Dim db As Database
    Dim qdf As DAO.QueryDef
    Dim rs As Recordset
    
    
    Set db = CurrentDb
    strsql = "SELECT tblservices.svc_ID, tblservices.svc_Name, tblservices.TrackExp from tblservices WHERE tblservices.trackexp=true;"
    Set qdf = db.CreateQueryDef("TrackedExpenses", strsql)
    Set rs = db.OpenRecordset("TrackedExpenses")
      rs.MoveLast
      rs.MoveFirst
      rs.FindFirst "[Svc_Name] = """ & Me.CmboService.Column(1) & """"
    
    
      If Not rs.NoMatch Then
        Me.Amount.Enabled = True
        Me.Amount.Locked = False
        Me.CmboDebitType.Enabled = True
        Me.CmboDebitType.Locked = False
        Me.CmboFund.Enabled = True
        Me.CmboFund.Locked = False
        Me.txtVendor.Enabled = True
        Me.txtVendor.Locked = False
      Else:
        If Me.Amount > 0 Then
            Msgbox "You can't enter this type service with an amount because expenses are't tracked for this service."
            GoTo SubClose
        End If
        Me.Amount.Enabled = False
        Me.Amount.Locked = True
        Me.CmboDebitType.Enabled = False
        Me.CmboDebitType.Locked = True
        Me.CmboFund.Enabled = False
        Me.CmboFund.Locked = True
        Me.txtVendor.Enabled = False
        Me.txtVendor.Locked = True
      End If
    SubClose:
       DoCmd.Close acQuery, "TrackedExpenses", acSaveNo
       db.QueryDefs.Delete "TrackedExpenses"
    
    
      strsql = ""
    
    
    Set db = Nothing
    Set rs = Nothing
    Exit_Err_CmboService_AfterUpdate:
      Exit Sub
    
    
    Err_CmboService_AfterUpdate:
    Msgbox Err.Description
    Resume Exit_Err_CmboService_AfterUpdate
    
    
    End Sub
    Somehow, a user managed to exit this code without the line
    Code:
    db.QueryDefs.Delete "TrackedExpenses"
    executing. Now almost any time the user clicks anything, Access kicks up a prompt telling them that the object "TrackedExpenses" already exists. Of course, the user that had the issue never told anyone about it, so the error has been occurring for a while. They don't remember what they were doing when it happened.

    I'm not sure how to modify my code so that this query always gets deleted, even if there is an error. Can I place the query delete code line in the "Exit_Err_CmboService_AfterUpdate:" subroutine? Is there a better place to trap the error? I'm not sure how to even recreate the error so testing might be an issue.

    Thanks for the advice.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    For starters, you don't appear to be changing anything in the query, so why bother with the QueryDef? That said, I wouldn't use it anyway, I'd just use the SQL directly:

    Set rs = db.OpenRecordset(strsql)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Oh, and if you still want to use it, just delete it at the start of your code (after setting db):

    On Error Resume Next
    db.QueryDefs.Delete "TrackedExpenses"
    On Error GoTo Err_CmboService_AfterUpdate

    the first line will handle the query not existing at that point.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84
    This entire database was created as a learning exercise for me so I could get better at coding and building a database. It was fortuitous that it reached the point where others could use it! At any rate, a I recall, I read somewhere that dynamic queries were "good" so I learned how to create them and used them in some places in my database, especially as the size of it grew larger. I will use the code you suggested to prevent the error. That in itself is something new for me to use. Thanks.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This entire database was created as a learning exercise for me so I could get better at coding
    Then some tips, perhaps...
    - make sure you have Option Explicit at the top of every module. This is a vb editor option.
    - never move on a recordset without ensuring there are records, otherwise you will generate an error.
    - personally, I never bother to move last before move first unless I need the record count. I don't see the point of it.
    - always test every scenario you can think of. I might be incorrect, but I'd say if you have no match, you won't process the 8 lines of control code that comes after GoTo
    - it's considered poor practice to use GoTo's to control flow beyond the error handler use. I probably have if I could not find another way, but I don't recall a time
    - again on flow - if you get an error, you resume to the point after setting objects to nothing, which means that gets missed

    Hope some of that helps.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84

    Post

    - make sure you have Option Explicit at the top of every module. This is a vb editor option.
    I already do this.

    - never move on a recordset without ensuring there are records, otherwise you will generate an error.
    I should add the line if not (rs.BOF and rs.EOF) to trap that potential error.
    - personally, I never bother to move last before move first unless I need the record count. I don't see the point of it.
    OK
    - always test every scenario you can think of. I might be incorrect, but I'd say if you have no match, you won't process the 8 lines of control code that comes after GoTo
    I think you are correct. I don't think in this particular instance that will ever happen but I will correct it.
    - it's considered poor practice to use GoTo's to control flow beyond the error handler use. I probably have if I could not find another way, but I don't recall a time
    good to know!
    - again on flow - if you get an error, you resume to the point after setting objects to nothing, which means that gets missed
    I think that is what happened here; just not sure how the error was created.

    Thanks again!

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't think in this particular instance that will ever happen but I will correct it.
    Maybe not, but all it takes is for the sql or query to return no records for any one of several possible reasons - connection lost to back end, criteria that all of a sudden fails on even just 1 record, a query or table mod whose effect on the recordset is not foreseen, record locking & conflicts, etc. Part of good coding is anticipating where things could go wrong - as much as possible and practical. Definitely practical to anticipate this as a possibility.
    Good luck with your db.

  9. #9
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84
    [QUOTE
    - always test every scenario you can think of. I might be incorrect, but I'd say if you have no match, you won't process the 8 lines of control code that comes after GoTo
    [/QUOTE]
    You were absolutely correct. Not only did my code not account for no records returned in the sql string but it also didn't account for the situation of No matches. I fixed both of those and got rid of the "GOTO" statement. I tested both the no records scenario and the no match scenario and everything works. Here is the final code:

    Code:
    Private Sub CmboService_AfterUpdate()
    
    Dim strsql As String
    Dim db As Database
    Dim qdf As dao.QueryDef
    Dim rs As Recordset
    
    
    On Error GoTo Err_CmboService_AfterUpdate
    'create a recordset values that corresponds to the services that have tracked expenses.  Only enable the amount field if this is true.
    
    
    Set db = CurrentDb
    strsql = "SELECT tblservices.svc_ID, tblservices.svc_Name, tblservices.TrackExp from tblservices WHERE tblservices.trackexp=true;"
    Set qdf = db.CreateQueryDef("TrackedExpenses", strsql)
    Set rs = db.OpenRecordset("TrackedExpenses")
    If Not (rs.BOF And rs.EOF) Then
        rs.FindFirst "[Svc_Name] = """ & Me.CmboService.Column(1) & """"
        If Not rs.NoMatch Then
            Me.Amount.Enabled = True
            Me.Amount.Locked = False
            Me.CmboDebitType.Enabled = True
            Me.CmboDebitType.Locked = False
            Me.CmboFund.Enabled = True
            Me.CmboFund.Locked = False
            Me.txtVendor.Enabled = True
            Me.txtVendor.Locked = False
        Else:
            If Me.Amount > 0 Then
                Msgbox "You can't enter this type service with an amount because expenses are't tracked for this service."
            End If
            Me.Amount.Enabled = False
            Me.Amount.Locked = True
            Me.CmboDebitType.Enabled = False
            Me.CmboDebitType.Locked = True
            Me.CmboFund.Enabled = False
            Me.CmboFund.Locked = True
            Me.txtVendor.Enabled = False
            Me.txtVendor.Locked = True
        End If
        db.QueryDefs.Delete "TrackedExpenses"
    Else
        db.QueryDefs.Delete "TrackedExpenses"
        Msgbox " After entering 'OK' you will be able to enter services but since there are no services currently selected to have their expenses tracked you will not be able to enter any monetary information about the service. Go to Database administration and select a service if you want to track its expense."
        Me.Amount.Enabled = False
        Me.Amount.Locked = True
    
    
        Me.CmboDebitType.Enabled = False
        Me.CmboDebitType.Locked = True
        Me.CmboFund.Enabled = False
        Me.CmboFund.Locked = True
        Me.txtVendor.Enabled = False
        Me.txtVendor.Locked = True
    End If
    
    
    strsql = ""
    Set db = Nothing
    Set rs = Nothing
    Exit_Err_CmboService_AfterUpdate:
      Exit Sub
    
    
    Err_CmboService_AfterUpdate:
    Msgbox Err.Description
    Resume Exit_Err_CmboService_AfterUpdate
    
    
    End Sub
    I will get rid of the querydefs completely in the next iteration but for now this works.
    I found 2 other spots in my dB where I used a querydef and I don't have all the scenarios planned for so now I'm off to fix them.

    Thanks for the help.

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You're still not destroying the objects if you err out. I would move that between the exit label and the Exit Sub line so that
    - no error, you Set to nothing & hit exit line
    - you error, resume after line label, set to nothing & hit Exit Sub line
    All my error traps are like this if exiting sub is the right action to take where objects were Set.
    It doesn't hurt to make strings "" but the variables are gone from memory as soon as the procedure is out of scope, unless maybe you declared it at the form level.
    Good luck.

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

Similar Threads

  1. error trapping on a subform
    By dgmdvm in forum Forms
    Replies: 4
    Last Post: 08-24-2018, 02:52 PM
  2. Replies: 7
    Last Post: 10-20-2016, 03:58 AM
  3. Append query error trapping
    By tpcervelo in forum Programming
    Replies: 4
    Last Post: 12-22-2011, 10:57 AM
  4. Proper Error Trapping
    By SemiAuto40 in forum Programming
    Replies: 6
    Last Post: 08-11-2011, 10:22 AM
  5. Form and Subform error trapping
    By usmcgrunt in forum Forms
    Replies: 8
    Last Post: 09-12-2010, 11:54 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