Physical Address

304 North Cardinal St.
Dorchester Center, MA 02124

Upgrading Functions of Budgeting System

Part 4 – Upgrading the Functions of Budgeting System

Part 4 of our “Budget and Expense Tracking” series dives deeper, exploring the advanced features and customization options available. By upgrading the functions of budgeting We’ll unlock the full potential of your budgeting system. This will not only save you time and effort but also tailor your tracker to your specific financial needs and goals.

Watch the Video

Subscription to Download Project

These 3 forms will be made functional:

  • Expense Accounts Form
  • Budget Form
  • Main Dashboard From

Expense Accounts Form

Progress Bar

First, we will make the progress bar percentage text box functional with this Expression builder.

=Sum([ac_exp_percentage])

The form record should not be updated until the total expense percentage is 100%. The Save button On Click Event Code:

Private Sub Command27_Click()
If Me.txt_ac_total > 1 Or Me.txt_ac_total < 1 Then
    MsgBox "Budget allocation must be within 100%", vbCritical, "| Exceed Limit |"
    Else
        DoCmd.Close acForm, "frm_accounts", acSaveYes
End If
End Sub

The progress bar should increase or decrease the colorful boxes as the percentage varies. For this purpose here is the complete code to apply On Form’s Current Event.

Private Sub Form_Current()
If Me.txt_ac_total > 1 Then
Me.Bx0.BackColor = vbRed
Me.bx01.Visible = True
Me.bx02.Visible = True
Me.bx03.Visible = True
Me.bx04.Visible = True
Me.bx05.Visible = True
Me.bx06.Visible = True
Me.bx07.Visible = True
Me.bx08.Visible = True
Me.bx09.Visible = True
Me.bx10.Visible = True
    Else
    If Me.txt_ac_total = 1 Then
Me.Bx0.BackColor = vbWhite
Me.bx01.Visible = True
Me.bx02.Visible = True
Me.bx03.Visible = True
Me.bx04.Visible = True
Me.bx05.Visible = True
Me.bx06.Visible = True
Me.bx07.Visible = True
Me.bx08.Visible = True
Me.bx09.Visible = True
Me.bx10.Visible = True
    Else
    If Me.txt_ac_total > 0.91 Then
Me.Bx0.BackColor = vbWhite
Me.bx01.Visible = True
Me.bx02.Visible = True
Me.bx03.Visible = True
Me.bx04.Visible = True
Me.bx05.Visible = True
Me.bx06.Visible = True
Me.bx07.Visible = True
Me.bx08.Visible = True
Me.bx09.Visible = True
Me.bx10.Visible = False
    Else
        If Me.txt_ac_total > 0.81 Then
Me.Bx0.BackColor = vbWhite
Me.bx01.Visible = True
Me.bx02.Visible = True
Me.bx03.Visible = True
Me.bx04.Visible = True
Me.bx05.Visible = True
Me.bx06.Visible = True
Me.bx07.Visible = True
Me.bx08.Visible = True
Me.bx09.Visible = False
Me.bx10.Visible = False
    Else
            If Me.txt_ac_total > 0.71 Then
Me.Bx0.BackColor = vbWhite
Me.bx01.Visible = True
Me.bx02.Visible = True
Me.bx03.Visible = True
Me.bx04.Visible = True
Me.bx05.Visible = True
Me.bx06.Visible = True
Me.bx07.Visible = True
Me.bx08.Visible = False
Me.bx09.Visible = False
Me.bx10.Visible = False
    Else
            If Me.txt_ac_total > 0.61 Then
Me.Bx0.BackColor = vbWhite
Me.bx01.Visible = True
Me.bx02.Visible = True
Me.bx03.Visible = True
Me.bx04.Visible = True
Me.bx05.Visible = True
Me.bx06.Visible = True
Me.bx07.Visible = False
Me.bx08.Visible = False
Me.bx09.Visible = False
Me.bx10.Visible = False
    Else
                If Me.txt_ac_total > 0.51 Then
Me.Bx0.BackColor = vbWhite
Me.bx01.Visible = True
Me.bx02.Visible = True
Me.bx03.Visible = True
Me.bx04.Visible = True
Me.bx05.Visible = True
Me.bx06.Visible = False
Me.bx07.Visible = False
Me.bx08.Visible = False
Me.bx09.Visible = False
Me.bx10.Visible = False
    Else
                    If Me.txt_ac_total > 0.41 Then
Me.Bx0.BackColor = vbWhite
Me.bx01.Visible = True
Me.bx02.Visible = True
Me.bx03.Visible = True
Me.bx04.Visible = True
Me.bx05.Visible = False
Me.bx06.Visible = False
Me.bx07.Visible = False
Me.bx08.Visible = False
Me.bx09.Visible = False
Me.bx10.Visible = False
    Else
                    If Me.txt_ac_total > 0.31 Then
Me.Bx0.BackColor = vbWhite
Me.bx01.Visible = True
Me.bx02.Visible = True
Me.bx03.Visible = True
Me.bx04.Visible = False
Me.bx05.Visible = False
Me.bx06.Visible = False
Me.bx07.Visible = False
Me.bx08.Visible = False
Me.bx09.Visible = False
Me.bx10.Visible = False
    Else
                    If Me.txt_ac_total > 0.21 Then
Me.Bx0.BackColor = vbWhite
Me.bx01.Visible = True
Me.bx02.Visible = True
Me.bx03.Visible = False
Me.bx04.Visible = False
Me.bx05.Visible = False
Me.bx06.Visible = False
Me.bx07.Visible = False
Me.bx08.Visible = False
Me.bx09.Visible = False
Me.bx10.Visible = False
    Else
                    If Me.txt_ac_total > 0.11 Then
Me.Bx0.BackColor = vbWhite
Me.bx01.Visible = True
Me.bx02.Visible = False
Me.bx03.Visible = False
Me.bx04.Visible = False
Me.bx05.Visible = False
Me.bx06.Visible = False
Me.bx07.Visible = False
Me.bx08.Visible = False
Me.bx09.Visible = False
Me.bx10.Visible = False
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

End Sub

Changing the status of the Expense Account

It is a good practice to disable unused options instead of removing them, so they can be enabled later. For this purpose, we have to add a field related to the status of the expense in the expense account table.

Each expense account transaction will contain a “Change” button. It will be applicable only when the percentage is zero. Here is the “On Click” event code to disable or activate the account.

Private Sub Command21_Click()
chk_b = DLookup("bid", "tbl_budget", "bstatus='Open'") 'Open Budget Lookup
chk_exp = DCount("*", "tbl_expense", "bid=" & chk_b & "and ac_exp_id =" & Me.ac_exp_id) 'Count if any expense exists in table

If chk_exp > 0 Or Me.ac_exp_percentage > 0 Then 'Both conditions
    MsgBox "Percentage and expense of this month should be zero before proceeding...", vbExclamation, "| Not Allowed |"
    Exit Sub
    
Else
    '(
    If Me.ac_exp_status = "Active" Then
        Me.ac_exp_status = "Disable"
    Else
        Me.ac_exp_status = "Active"
    End If
    ')
        Me.Requery

End If
End Sub

To switch between Active and Disabled records, we will use the Frame, the form’s Record Source should be updated accordingly. Here is the Frame On Click function:

Private Sub frameAc_Click()
If Me.frameAc = 1 Then
    Me.Form.RecordSource = "SELECT tbl_accounts.* FROM tbl_accounts WHERE (((tbl_accounts.ac_exp_status)='Active'));"
    Me.Form.AllowAdditions = True

Else
    Me.Form.RecordSource = "SELECT tbl_accounts.* FROM tbl_accounts WHERE (((tbl_accounts.ac_exp_status)='Disable'));"
    Me.Form.AllowAdditions = False

End If
End Sub

To set Active expense accounts as the default record source of the form, use “Active” as the criteria for the form’s Row Source.

Dashboard Form

Header Section

We have already designed the header section of the Dashboard form in Part 2. Before proceeding further, all the unbound text boxes should be cleared from any Control Source properties except the “Budget Amount”.

Private Sub Form_Current()
m_err = DCount("bid", "tbl_budget", "bstatus='Open'")
If m_err = 0 Then
    MsgBox "Please allocate the budget and open a month", vbExclamation, "| No Open Budget |"
    Exit Sub
    Else
        main_total = DSum("exp_amount", "tbl_expense", "bid=" & Me.bid)
        Me.txt_main_total = main_total 'Total Expense
        
        Me.txt_main_balance = Me.bamount - main_total 'Remaining Balance
        
        Me.txt_main_percentage = main_total / Me.bamount 'Percentage
        
        Me.txt_main_today = DSum("exp_amount", "tbl_expense", "exp_dt= #" & Date & "#") 'Today Expenses
        
 End If
End Sub

All header values should be updated like this:

Budget Dashboard Header

Add Expense & Budget Allocation

Both the “Add Expense” and “Budget Allocation” forms will work to open the relevant forms.

Here are both button codes:

Private Sub Command21_Click()
DoCmd.OpenForm "frm_budget"
End Sub

Private Sub Command22_Click()
DoCmd.OpenForm "frm_expense"
End Sub

Budget Allocation Form

As we have already created unbound text boxes and converted year and month text boxes into Combo Boxes in Part 1, now we need to make the button functional to Add Record.

Private Sub Command50_Click()
If IsNull(Me.txt_y) = True Or IsNull(Me.txt_m) = True Or Me.txt_a <= 0 Or IsNull(Me.txt_a) = True Then
    MsgBox "Please fill mandatory data...", vbExclamation, "| Date Required |"
    Exit Sub
    Else
        chk_m = DCount("bstatus", "tbl_budget", "bstatus='Open'")
        If chk_m > 0 Then
            MsgBox "MOnth should be closed before proceeding..", vbExclamation, "| Month Status Error |"
            Exit Sub
        Else
            Me.Form.AllowAdditions = True
            DoCmd.GoToRecord , , acNewRec
                Me.bdt = Date
                Me.byear = Me.txt_y
                Me.bmonth = Me.txt_m
                Me.bamount = Me.txt_a
                Me.bdetail = Me.txt_n
                Me.bbalance = Me.txt_a
            Me.Requery
            Me.AllowAdditions = False
            
            MsgBox "New Budget month has been added...", vbInformation, "| Budget Added |"
        End If
End If
End Sub

For the month closing form the subform, we need to make the status button functional:

Private Sub Command37_Click()
If Me.bstatus = "Close" Then
    MsgBox "Month has already been closed...", vbInformation, "| Month Closed |"
    Exit Sub
Else
    response = MsgBox("Confirm Month Close?", vbYesNo + vbQuestion, "Confirmation")
    If response = vbYes Then
    
        Me.bstatus = "Close"
        Me.Requery
        Me.txt_y.SetFocus
        
    ElseIf response = vbNo Then
        Cancel = True
    End If
End If
End Sub

After updating and making the form objects functional, the final look will be like this:

Budget Form Updated

To set the month as “Open” status as the default record source of the Main Dashboard form, use “Open” as the criteria for the form’s Row Source.

<<Part 3 – Take Budget Control with Expense Management

Part 5 – Analyze Your Budget with Charts and Reports>>

Leave a Reply

Your email address will not be published. Required fields are marked *