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.