Part 2 – Building Powerful Admin Dashboard for Expense Tracking

An Admin Dashboard is an important component of an Expense Approval System as it acts as the central control hub for financial operations. The dashboard provides administrators with a comprehensive overview of business spending and expense requests. Moreover, real-time data presented through intuitive visualizations allows administrators to make informed decisions, identify trends, optimize spending patterns, and mitigate financial risks. Ultimately, the Admin Dashboard for Expense Tracking and Approval plays a vital role in promoting accountability, enhancing operational efficiency, and maintaining financial integrity within the organization’s expense management framework.

Overview

A header section with different statistics related to Expenses and Transactions. The main Admin Dashboard will contain a subform based on the Expense table. Each transaction of expense generated by the user can be either Approved or Rejected.

Steps to Building Admin Dashboard

Dashboard Header Section

The header section of the Main Dashboard contains updated figures related to these important statistics:

• Top Expense Amount
• Approved Today
• Number of Expense Transactions
• Month Expense Rejections

For the designing perspective, create 4 rectangles, and apply icons with 4 unbound text boxes.

The “On Current” event of the main form properties, here is the code:

Private Sub Form_Current()
cmonth = Month(Date) 'Variable defined to format the month from the current date

'First box for Top Expense
Me.txttopexpense = DMax("exp_amount", "tbl_expense", "month(exp_date) =" & cmonth)

'Second box for Approved Today
Me.txtapprovedtoday = DSum("exp_amount", "tbl_expense", "exp_date = #" & Date & "#")

'Third box for Transactions
Me.txttransactions = DCount("*", "tbl_expense", "month(exp_date) = " & cmonth)

'Forth box for Month Rejections
Me.txtmonthrejections = DCount("*", "tbl_expense", "month(exp_date) = " & cmonth & "and exp_node= 'Rejected'")

End Sub

Admin Dashboard Subform

The record source of the subform will be based on the Expense form. Build a query with the criteria of “Open” status expenses.

Approval Button Code

Private Sub cmdapproved_Click()
On Error GoTo errhandler
response = MsgBox("Are you sure to approve this Expense?", vbYesNo + vbQuestion, "Approval Confirmation")
If response = vbYes Then
    Me.exp_node = "Approved"
    Me.Form.Requery
        Else
            If response = vbNo Then
                Cancel = True
End If
End If

errhandler:
    Cancel = True
    Exit Sub
End Sub

Reject Button Code

Private Sub cmdreject_Click()
On Error GoTo errhandler
response = MsgBox("Are you sure to Reject this Expense?", vbYesNo + vbQuestion, "Reject Confirmation")
If response = vbYes Then
    Me.exp_node = "Rejected"
    Me.Form.Requery
        Else
            If response = vbNo Then
                Cancel = True
End If
End If

errhandler:
    Cancel = True
    Exit Sub
End Sub