Part 3: Chart of Accounts (COA) and Modern Alert Pop-ups

Master Business Accounting Project Part 3

Part 3 is the pivotal step where the Chart of Accounts (COA), the backbone of the financial system, is built, along with an innovative custom pop-up alert system.

Watch the Complete Video Tutorial 3

Explanation with timeline

1. The Chart of Accounts Database Structure:

  • Parent Table (tbl_ChartOfAccounts): Defines the five primary types: Assets, Liabilities, Equity, Revenue, and Expenses.
  • Child Table (tbl_ChartOfAccountsSUB): Contains specific accounts (Account Number, Name, Balance) and includes a Foreign Key (AccID) linking it to the Parent table.
  • A One-to-Many Relationship is established between the two tables.

2. Building the ‘Chart of Accounts’ Form:

  • The frm_ChartOfAccounts to be build in Continuous Subform bound to the tbl_ChartOfAccounts.

3. Building the ‘Add Group Account’ Form:

  • The frm_AddGroupAccount is set to open in Dialog Mode.
  • Account Number Generation: VBA code on the Primary Type List Box’s On Click event uses the DMax function to find the largest existing account number under the selected type and adds 1 for the new unique number.
  • Duplicate Check: Code on the Account Name’s After Update event checks for existing duplicate names under the same primary type and provides instant visual feedback via icons.
  • Submit Validation: The Submit button checks mandatory fields and validates that the duplicate-check icon is not visible before insertion.

4. Creating the Modern Pop-up Alert System:

  • A custom form (Alert_frm) is designed with a Label for the message and a set of 20 hidden boxes for the animation.
  • The form’s On Timer event drives the animation by sequentially making the 20 boxes visible, pausing, and then closing the form. This custom form is used to replace all standard Access message boxes.
Chart of Accounts

Primary Accounts in the Chart of Accounts

Assets: These are things you own that have value, like cash, property, or equipment.

Liabilities: These are debts or obligations you owe, such as loans or bills you haven’t paid yet.

Equity: This represents that value of what you own after subtracting what you owe. It’s like the net worth of your business or personal finances.

Revenue/Income: This includes money you earn from selling goods or services, like sales revenue or fees.

Expenses: These are the costs you incur to run the business or manage your personal finance, like rent, salaries, or utilities.

Detailed Step-by-Step Guide

This part establishes the Chart of Accounts (COA) and an innovative, universal custom pop-up alert system for professional user feedback.

1. The Chart of Accounts Database Structure

Step 1: The Parent Table (tbl_ChartOfAccounts)

Field NameData TypePurpose
AccIDAutoNumber (Primary Key)Unique ID for the log entry.
AccNumberNumberAccount Number
AccNameTextName of the primary type (e.g., ‘Assets’, ‘Liabilities’).
AccGroupTextGroup or Category of the Accounts
AccDescriptionTextA description of the Account.
AccTypeTextType of Account (e.g., ‘Builtin’ for default accounts
AccBalanceCurrencyOpening Balance amount of the account

Step 2: The Child Table (tbl_ChartOfAccountsSUB)

Field NameData TypePurpose
AccSubIDAutoNumber (Primary Key)Unique ID for the log entry.
AccIDNumber (Foreign Key)Links to the parent account ‘tbl_ChartOfAccounts’
AccCommonIDNumberName of the primary type (e.g., ‘Assets’, ‘Liabilities’).
AccSubIDTextGroup or Category of the Accounts
AccSubNmTextA description of the Account.
AccSubDetailTextType of Account (e.g., ‘Builtin’ for default accounts
AccSubBalanceCurrencyOpening Balance amount of the account

2. Building the ‘Chart of Accounts’ Form (frm_ChartOfAccounts)

Step 3: Creating the Chart of Accounts Form (frm_ChartOfAccounts)

  1. A new form (frm_ChartOfAccounts) is created as a Continuous Subform bound to the tbl_ChartOfAccounts.
  2. The form’s records are explicitly Disabled to prevent accidental editing, making it a read-only list.
  3. This form is integrated as a Subform into the Accounts tab.

3. Building the ‘Add Group Account’ Form (frm_AddGroupAccount)

Step 4: VBA Logic for Auto-Incrementing Account Number

  1. Event: Code is triggered by the On Click event of the Primary Account List Box.
  2. Function: It uses the DMax function to find the largest existing account number (Number field) associated with the selected Account_ID.
  3. Calculation: It adds 1 to the maximum value found to generate the new, unique account number.

On Click event Code of the Primary Account List Box

Private Sub ListSelectPrimary_Click()
    'Actually we need to add Primary account number
    Me.txtGroupNumber = DMax("AccNumber", "tbl_ChartOfAccounts", "AccName= '" & Me.ListSelectPrimary & "'") + 1
End Sub

Step 5: Checking for Duplicate Account Names

  1. Event: The code is triggered by the After Update event of the Account Group Name text box.
  2. Function: A check is performed for an existing record matching both the entered Name and the selected Account_ID.
  3. Visual Feedback: An icon is instantly displayed (e.g., a Cross Icon) if a duplicate is found, which is linked to the submission validation.

After Update event Code of the Account Group Name text box

Private Sub txtAccGroup_AfterUpdate()
'Define a variable to store DCOUNT value
AG = DCount("AccGroup", "tbl_ChartOfAccounts", "AccGroup = '" & Me.txtAccGroup & "'")

If AG = 0 Then
    Me.CmdGnok.Visible = False
    Me.CmdGok.Visible = True
        ElseIf AG > 0 Then
            Me.CmdGnok.Visible = True
            Me.CmdGok.Visible = False
End If
End Sub

Step 6: Submit Button Validation and Insertion

The Submit button’s VBA code ensures data is clean: checks mandatory fields, blocks submission if the duplicate icon is visible, inserts the data, and then refreshes the main COA display form.

Submit Button to Add Account Code

Private Sub cmdAddGroupSubmit_Click()
'Madatory fields check to be filled
If IsNull(Me.ListSelectPrimary) = True Or IsNull(Me.txtAccGroup) = True Then
    MsgBox "Mandatory fields must be filled.", vbExclamation, "|Empty Fields|"
    Exit Sub
        Else
            If Me.CmdGnok.Visible = True Then
                MsgBox "The Group name already exists.", vbCritical, "|Not Allowed|"
                Exit Sub
                    Else
                        'Proceeding to insert data into the table
                        Set rst = CurrentDb.OpenRecordset("tbl_ChartOfAccounts", dbOpenDynaset, dbSeeChanges)
                        With rst
                            .AddNew
                            .Fields("AccNumber") = Me.txtGroupNumber
                            .Fields("AccName") = Me.ListSelectPrimary
                            .Fields("AccGroup") = Me.txtAccGroup
                            .Fields("AccDescription") = Me.txtAccDescription
                            'Let the Type empty other than Builtin
                            .Update
                        End With
                        Set rst = Nothing
                        
                        'MsgBox "The Account has been created successfully.", vbInformation, "|Account Created|"
                        DoCmd.Close acForm, "frm_AddGroupAccount"
                        
                        '\\\MsgPopup
                        DoCmd.OpenForm "frm_msgpopup"
                        Forms!frm_msgpopup.Form.LblMsgPopup.Caption = "Account has been created..."
                        
                        'Refresh the data in the Main form
                        Forms!MainDashboard!NavigationSubform.Form.Requery
            End If
End If
End Sub

The Pop-up Alert (MsgPopup) is in the next section

3. Creating the Modern Pop-up Alert System 🔔

Step 7: Designing the Pop-up Form (Alert_frm)

  1. Create a small, Unbound Form designed to open as a dedicated pop-up.
  2. Animation Elements: The key is placing a large number (e.g., 20) of identically sized, small controls (boxes) around the central message area. These are initially hidden.
Pop-up Alert Message
Pop-up Alert Message Preview

Step 8: The Timer Event VBA Logic (The Animation)

  1. Timer Counter: A hidden variable or control tracks the animation progress.
  2. Show/Hide Logic: The code on the form’s On Timer event sequentially makes the 20 hidden boxes visible to create a fade-in effect.
  3. Pause and Close: The counter continues for a set duration to pause the alert on screen before the code finally closes the pop-up form. This form can be called universally for all successful submissions.

Submit Button to Add Account Code

Private Sub Form_Timer()
If Me.txtMsgCount = 50 Then
    DoCmd.Close acForm, "frm_msgPopup"
    Else
        Me.txtMsgCount = Me.txtMsgCount + 1
    If Me.txtMsgCount = 2 Then
        Me.BxP1.Visible = True
        
    ElseIf Me.txtMsgCount = 3 Then
        Me.BxP2.Visible = True
        
    ElseIf Me.txtMsgCount = 4 Then
        Me.BxP3.Visible = True
        
    ElseIf Me.txtMsgCount = 5 Then
        Me.BxP4.Visible = True
        
    ElseIf Me.txtMsgCount = 6 Then
        Me.BxP5.Visible = True
        
    ElseIf Me.txtMsgCount = 7 Then
        Me.BxP6.Visible = True
        
    ElseIf Me.txtMsgCount = 8 Then
        Me.BxP7.Visible = True
        
    ElseIf Me.txtMsgCount = 9 Then
        Me.BxP8.Visible = True
        
    ElseIf Me.txtMsgCount = 10 Then
        Me.BxP9.Visible = True
        
    ElseIf Me.txtMsgCount = 11 Then
        Me.BxP10.Visible = True
        
    ElseIf Me.txtMsgCount = 12 Then
        Me.BxP11.Visible = True
        
    ElseIf Me.txtMsgCount = 13 Then
        Me.BxP12.Visible = True
        
    ElseIf Me.txtMsgCount = 14 Then
        Me.BxP13.Visible = True
        
    ElseIf Me.txtMsgCount = 15 Then
        Me.BxP14.Visible = True
        
    ElseIf Me.txtMsgCount = 16 Then
        Me.BxP15.Visible = True
        
    ElseIf Me.txtMsgCount = 17 Then
        Me.BxP16.Visible = True
        
    ElseIf Me.txtMsgCount = 18 Then
        Me.BxP17.Visible = True
        
    ElseIf Me.txtMsgCount = 19 Then
        Me.BxP18.Visible = True
        
    ElseIf Me.txtMsgCount = 20 Then
        Me.BxP19.Visible = True

    ElseIf Me.txtMsgCount = 21 Then
        Me.BxP20.Visible = True
End If
End If
End Sub

Related Posts