Part 3 – Take Budget Control with Expense Management

Numbers never lie, and the data in your MS Access budget tracking system is full of valuable information! In Part 3 of our “Budget and Expense Tracking” series, we will explore the power of expense management. You can take budget control with expense management, analyze your financial data, categorize your spending patterns, and gain actionable insights. By utilizing the reports and features available in MS Access, you will be able to identify areas where you can optimize your spending, set realistic goals, and attain true financial freedom.

Overview

The expense form will be upgraded with these advanced features for better visibility of spending:

The header section of the form will present:

• Current month duration
• The allocated budget amount for the month
• Total expenses made
• The remaining balance from the current month’s budget

Adding expenses to the subform from the unbound text boxes and combo boxes structure.

Current status of every selected expense from the drop-down with the percentage of spending.

The subform will contain an Edit or Delete function.

Upgrading Expense Management Entry Form

The Record Source of the form should be based on the Budget table.

The Duration and Budget Amount text boxes will be bound with the table fields.

Update the “Total Expenses” text box using the “DSUM” function with this code in the Form’s Current Event.

Private Sub Form_Current()
    Me.txt_total = DSum("exp_amount", "tbl_expense", "bid=" & Me.bid)

End Sub

The “Remaining Balance” text box will simply update from the Expression Builder.

=[bamount]-[txt_total]

The Expense Selection Dropdown will update 4 other text boxes:

• The Percentage of Expese quota
• The total allocated quota of the Expense
• The total spending of the expense
• The Remaining balance from the expense quota

The Expense selection dropdown “After Update” event code:

Private Sub txt_expense_select_AfterUpdate()
expsel = Me.txt_expense_select

exppercentage = DLookup("ac_exp_percentage", "tbl_accounts", "bid=" & Me.bid)
    Me.txt_expense_percentage = exppercentage 'First text box
    
expqttl = Me.bamount * exppercentage 'Second text box
Me.txt_expense_qtotal = expqttl

expttl = DSum("exp_amount", "tbl_expense", "bid=" & Me.bid & "and ac_exp_id =" & expsel)
    Me.txt_expense_total = expttl 'Third text box
    
Me.txt_expense_balance = expqttl - expttl 'Balance

End Sub

The “Add Expense” button code is based on unbound text boxes and a combo box:

Private Sub cmd_add_expense_Click()
If IsNull(Me.txt_expense_date) = True Or IsNull(Me.txt_expense_select) = True Or IsNull(Me.txt_expense_amount) = True Then
    MsgBox "Mandatory fields must be filled before adding record...", vbExclamation, "| Incomplete Data |"
    Exit Sub
        Else
            If Me.txt_expense_amount > Me.txt_expense_balance Then
                MsgBox "Expense Quota over, Please adjust from the account", vbExclamation, "| Budget Over |"
                Exit Sub
            Else
            Me.bbalance = Me.bbalance - Me.txt_expense_amount 'Deduction from the Main Budget
            Me.frm_expenseSub.Form.AllowAdditions = True 'Subform Record Addition
            Me.frm_expenseSub.SetFocus
            DoCmd.GoToRecord , , acNewRec 'Adding New Record
                Me.frm_expenseSub!exp_dt = Me.txt_expense_date
                Me.frm_expenseSub!ac_exp_id = Me.txt_expense_select
                Me.frm_expenseSub!exp_detail = Me.txt_expense_detail
                Me.frm_expenseSub!exp_amount = Me.txt_expense_amount
            Me.frm_expenseSub.Form.Requery
            Me.frm_expenseSub.Form.AllowAdditions = False
            
            Me.txt_expense_date = Date
            Me.txt_expense_select = Null
            Me.txt_expense_detail = Null
            Me.txt_expense_amount = Null
            
            Me.txt_expense_percentage = Null
            Me.txt_expense_qtotal = Null
            Me.txt_expense_total = Null
            Me.txt_expense_balance = Null
            
            Me.Requery
            Me.bbalance = Me.txt_balance
            Me.txt_expense_select.SetFocus
End If
End If
End Sub

Expense Deletion

After adding the transaction to the subform, here is the code for the Delete button to remove the record and add the amount of deleted record into the balance amount field of the parent form as revert.

The Delete Button Code:

Private Sub Command27_Click()
response = MsgBox("Delete Confirm?", vbYesNo + vbQuestion, "Delete Confirmation")
    If response = vbYes Then
        
        pbbalance = Me.Parent.bbalance
        Me.Parent.bbalance = pbbalance + Me.exp_amount
        
        DoCmd.SetWarnings False
        DoCmd.RunCommand acCmdDeleteRecord
        DoCmd.SetWarnings True
        
        Me.Requery
        Me.Parent.Requery
        
    ElseIf response = vbNo Then
        Cancel = True
    
    End If
End Sub

It is better to have a popup form to Edit an expense transaction.

After creating the form based on the expense form, here is the code to open the form with the expense ID as the criteria:

The Edit button on click event code:

Private Sub Command22_Click()
    DoCmd.OpenForm "frm_expense_edit", , , "exp_id=" & Me.exp_id, acFormEdit, acDialog
End Sub

Expense Edit Form

While opening the Edit form, the amount should be captured so the difference can be updated in other relevant tables after the amount change.

The form’s “On Click” event will be used for this purpose which will update the hidden unbound text box:

Private Sub Form_Load()
    Me.txt_exp_before = Me.exp_amount
End Sub

The Save button of the Expense Edit form will act with all the calculations and then update the record in the Main Expense Data Entry form. Here is the Code:

Private Sub cmd_exp_edit_save_Click()
chn_exp = Me.txt_exp_before - Me.exp_amount 'Calculating Changed in amount
chn_exp_main = Forms!frm_expense.Form.bbalance 'Accessing Main Expense form

Forms!frm_expense.Form.bbalance = chn_exp_main + chn_exp 'Differance of both amounts
Forms!frm_expense.Form.Requery

DoCmd.Close acForm, "frm_expense_edit", acSaveYes ' Closing form

End Sub