Part 1 – Setting Up Your Expense Approval System

An efficient expense approval system is crucial for any organization to maintain financial accountability and streamline the reimbursement process. By establishing clear guidelines and procedures, businesses can ensure that expenses are incurred responsibly according to company policies. In this guide, we’ll explore the key steps and considerations involved in setting up your expense approval system that meets the needs of your organization.

Benefits of Expense Approval System

This project automates expense management with an approval system, eliminating manual data entry and providing additional benefits.

After applying the integration of the expense approval system any business can gain:

Financial Control: Prevent unauthorized spending and reduce the risk of fraud.

Standardize: Benchmark the cost of different types of expenses.

Cost Visibility: Gain real-time insight into spending patterns and optimize budgets.

Efficiency and Productivity: Streamline the approval process and reduce administrative burden.

Enhanced Accountability: Foster a culture of responsibility and transparency, leading to better financial stewardship.

Overview

MS Access provides a powerful platform for building and managing expense approval systems. With its intuitive interface, customizable features, and seamless integration with other Microsoft Office applications, MS Access offers a cost-effective solution for organizations of all sizes.

We will use the Ultimate Login System for the user login. There will be 2 types of separate structures built, one is for employees where expenses can be generated for approval, and the other will be affiliated with admin rights which contain all the expense requests generated.

Steps to Setting Up Expense Approval System

In short, we are required to build, a table related to Expense data entry, Admin main dashboard form, and User main dashboard form, both forms will contain subforms based on Expense Table, a form related to adding new expenses.

Each expense data can be either approved or rejected by the admin.

Tables for Setting Up Expense Approval System

Expense Table

Create a table related to Expense data entry with these fields:

The table contains a unique ID field with the data type of AutoNumber and the User ID data type is Number for a relationship purpose with the user table.

For a better approach, create an expense category field, it is crucial for grouping the relevant expenses. (You can determine a list of expenses by selecting “Lookup Wizard” but hence, we will use unbound text boxes in the form, there is no need at this stage)

Next, create fields for expense detail and amount. The Node field will be used to decide the status of the expense whether it is open, approved, or rejected.

Tables Relationship

Both User and Expense must be linked with the ID and “One to Many” relationship like this:

Setting Up the Forms

Main Form

Create a simple form by selecting “Form Design” from the Forms menu and the subform based on the expense table for the list of generated expenses.

Here we need an “Add Expense” button to open the form to add new expenses.

Admin and User dashboards will be based on this main form which will be created later. Make sure to intact the User Table as the Record Source of the form and link the Master and Child field of the subform with the User ID.
In this way, multi-user login can be operated.

Add New Expenses Form

Create a new form with 1 unbound Combo box, 2 unbound text boxes, and a button to Submit the record. The row source type of combo box will be a value list like Office, Repairing, Stationary, Civil Work, Utility Bills, Salary, Incentives, Allowance, Meal, etc. (You can create another table containing expense categories and update the table as a row source).

Start the VBA code environment and put this code into the “On Click” event:

Private Sub cmdexpsubmit_Click()
'Check if mandatory fields filled
If IsNull(Me.txtexpcat) = True Or IsNull(Me.txtexpdetail) = True Or IsNull(Me.txtexpamount) = True Then
    MsgBox "Mandatory fields must be filled...", , "Empty Fields"
        Else
            'Inserting data into table
            Set rst = CurrentDb.OpenRecordset("tbl_expense", dbOpenDynaset, dbSeeChanges)
                With rst
                    .AddNew
                    .Fields("uid") = Forms!MainUser.Form.uid
                    .Fields("exp_cat") = Me.txtexpcat
                    .Fields("exp_detail") = Me.txtexpdetail
                    .Fields("exp_amount") = Me.txtexpamount
                    .Fields("exp_node") = "Open"
                    .Update
                    
                End With
                Set rst = Nothing
    'Closing form after Inserting data
    DoCmd.Close acForm, "Add_Expense", acSaveYes
    
    'Refreshing Main form
    Forms!MainUser.Form.Requery
End If
End Sub

Customize Login Form

Due to multiple user interfaces, there should be some modification required in the code of the Login button in the Login form so here is put this code into VBA:

Private Sub btn_login_Click()
If DCount("uusername", "usertable", "uusername= '" & txt_username & "' and upassword= '" & txt_password & "'") = 0 Then
    Me.Lbl_incorrect.Visible = True
        Else
            If Me.txt_username = "admin" Then
            DoCmd.OpenForm "AdminDashboard"
            DoCmd.Close acForm, "LoginForm"
                Else
                    'lookup id from the user table
                    ui = DLookup("uid", "UserTable", "uusername= '" & Me.txt_username & "'")
                    
                    DoCmd.Close acForm, "LoginForm"
                    
                    'Open form based on the user id
                    DoCmd.OpenForm "MainUser", , , "uid=" & ui
End If
End If
End Sub