# Ar-reconciliation-vba-access/vba-source

## Overview

This VBA module is a self-contained **Accounts Receivable reconciliation subpackage** that ingests financial transaction data from Excel files, reconciles internal transactions against bank statement records, and produces reconciled report exports. It exists as the core data-processing layer for the AR reconciliation workflow in the Access application — handling the full lifecycle from file import through matching to report output.

## Architecture

The module is organized into five modules that follow a clear separation of concerns:

| Module | Type | Responsibility |
|--------|------|----------------|
| [clsTransaction](ar-reconciliation-vba-access/vba-source/clsTransaction.cls) | Class | Represents a single financial transaction with validation |
| [modImport](ar-reconciliation-vba-access/vba-source/modImport.bas) | Module | Excel file picker, file opening, row-by-row import into Access tables |
| [modReconcile](ar-reconciliation-vba-access/vba-source/modReconcile.bas) | Module | Matches transactions against bank statements using exact and fuzzy strategies |
| [modExport](ar-reconciliation-vba-access/vba-source/modExport.bas) | Module | Generates an Excel report from reconciliation results |
| [modUtil](ar-reconciliation-vba-access/vba-source/modUtil.bas) | Module | Shared utilities: batch ID sequencing, SQL formatting, error logging |

### Component Relationship Diagram

```
flowchart TD
    A["frmImport"] --> B["modImport"]
    B --> C["clsTransaction"]
    B --> D["modUtil"]
    B --> E["tblTransactions"]
    B --> F["tblBankStatement"]
    B --> G["tblImportBatch"]
    B --> H["tblErrorLog"]

    I["modReconcile"] --> C
    I --> D
    I --> E
    I --> F
    I --> J["tblReconcileResult"]

    K["modExport"] --> D
    K --> E
    K --> J

    L["Excel File"] --> B
    K --> M["Excel Report"]
```

## How It Works — The Reconciliation Flow

A typical reconciliation session proceeds through three phases: **import**, **reconcile**, and **export**.

### Phase 1 — Import

1. The user opens [frmImport](ar-reconciliation-vba-access/vba-source/modImport.bas) and clicks **BrowseForFile()**, which launches a file picker filtered to `*.xlsx` files. The selected path is stored in `txtFilePath`.
2. When the user confirms the import, `RunImport()` is called:
   - It reads the file path from the form, detects whether the file is a bank statement (by checking for `"BankStatement"` in the filename).
   - It requests a new batch ID from `modUtil.GetNextBatchNumber()`, which queries `Max(BatchID)` from `tblImportBatch`.
   - It opens Excel via COM automation (`CreateObject("Excel.Application")`). The function implements a retry loop (up to 3 attempts) that handles **Error 70 (Permission Denied)** — this typically occurs when the file is still locked by another process. Between retries it calls `Sleep 2000` to give the lock time to clear.
   - It iterates rows starting at row 2, instantiates a `clsTransaction` for each row, populates fields from the Excel cells, and calls `IsValid()` to validate the row.
   - Valid rows are inserted into either `tblTransactions` (internal transactions) or `tblBankStatement` (bank statement rows), depending on the file type. Invalid rows are logged to `tblErrorLog` via `modUtil.LogError()`.
   - The import row count is committed to `tblImportBatch`, `modUtil.gCurrentBatchID` is set for downstream modules, and a status message is written to `lblStatus` on the form.

**Important implementation note:** Row 2 is used as the anchor for `lastRow` detection because column 1 (Branch) is intentionally blank in bank statement files. Using `xlUp` on column 2 (TxnDate) avoids the edge case where column 1 reports no last-used row.

### Phase 2 — Reconcile

1. `RunReconcile()` is invoked, optionally receiving a specific batch ID (or using the current one via `modUtil.gCurrentBatchID`).
2. It first **clears stale results** — deletes any existing rows in `tblReconcileResult` for the given batch.
3. It then iterates every transaction in `tblTransactions` for that batch and applies a **three-tier matching strategy**:

   | Tier | Strategy | Match criteria |
   |------|----------|----------------|
   | 1 | Duplicate detection | If the same `RefNo` appears more than once in the transaction table, the entry is flagged as `"Duplicate"` |
   | 2 | Exact match | `RefNo` in `tblTransactions` equals `BankRefNo` in `tblBankStatement` |
   | 3 | Fuzzy match | Same `Amount` AND `TxnDate` within +-1 day of the transaction date |

4. For every match (exact or fuzzy), `InsertResult()` writes a `tblReconcileResult` row with status `"Matched"` or `"Matched (Fuzzy)"` and the `Variance` (transaction amount minus bank amount). Unmatched transactions get status `"Unmatched"` with `Variance` set to the full amount. Duplicate transactions are flagged `"Duplicate"` with zero variance.
5. The function returns the count of matched transactions.

### Phase 3 — Export

1. `RunExport()` is called, optionally with a batch ID.
2. It joins `tblReconcileResult` with `tblTransactions` to produce a result set containing `RefNo`, `Branch`, `TxnDate`, `Amount`, `MatchStatus`, and `Variance`.
3. It creates an Excel workbook via COM automation, populates a header row, then writes each record. Rows with non-zero `Variance` are highlighted in light red (`RGB(255, 199, 199)`).
4. The workbook is auto-fitted and saved to the current database directory as `ReconcileReport_yyyymm.xlsx`.
5. The output file path is returned to the caller.

## Key Classes and Interfaces

### clsTransaction — Transaction Data Model

[clsTransaction](ar-reconciliation-vba-access/vba-source/clsTransaction.cls) is a VBA class module that holds the fields of a single financial transaction record:

- **TransID** (`Long`) — database primary key. Always set to `0` during import; the database auto-assigns the actual key.
- **Branch** (`String`) — branch code/identifier. Set to `"BANK"` for bank statement rows.
- **TxnDate** (`Date`) — transaction date.
- **Amount** (`Currency`) — monetary value. Uses VBA `Currency` type (fixed-point, 4 decimal places).
- **RefNo** (`String`) — transaction reference number. Used as the primary matching key.
- **IsValid()** — validation function that returns `True` when all of the following hold: `Amount > 0`, `RefNo` is non-blank after trimming, `Branch` is non-empty, and `TxnDate` is after `2000-01-01`. This function is the gatekeeper during import — rows that fail validation are rejected and logged as errors.

The class is instantiated once per row during import and discarded after the database insert. It has no constructor or persistent state beyond its public fields.

### modImport — File Ingestion

[modImport](ar-reconciliation-vba-access/vba-source/modImport.bas) contains the import logic.

- **RunImport()** — the primary import function. Returns the batch ID. It reads the file path from `Forms!frmImport!txtFilePath`, opens the Excel file via late-bound COM automation, and processes each row. It supports two file types:
  - **Bank statement files** (filename contains `"BankStatement"`): inserts into `tblBankStatement` with Branch forced to `"BANK"`. Reads from columns 2 (TxnDate), 3 (Amount), and 4 (BankRefNo).
  - **Internal transaction files**: inserts into `tblTransactions`. Reads from columns 1 (Branch), 2 (TxnDate), 3 (Amount), and 4 (RefNo).

  Error handling uses `On Error Resume Next` within the retry loop for file locking, and `modUtil.LogError()` for individual row failures.

- **BrowseForFile()** — opens the Windows file picker (`msoFileDialogFilePicker`) filtered to Excel files. Writes the selected path to `Forms!frmImport!txtFilePath`.

### modReconcile — Matching Engine

[modReconcile](ar-reconciliation-vba-access/vba-source/modReconcile.bas) contains the core reconciliation logic.

- **RunReconcile()** — orchestrates the matching process. Takes an optional `batchID` (defaults to `modUtil.gCurrentBatchID`). Returns the count of matched records. The function processes transactions sequentially, opening a recordset on `tblBankStatement` for each transaction. For exact matches it reuses the recordset from the exact-match query; for fuzzy matches it opens a new one. Note that `rsBank.Close` is called explicitly before the fuzzy match attempt, and again after it — the recordset may already be closed at the second call for matched records, which VBA tolerates.

- **InsertResult()** — private helper that builds and executes the `INSERT` statement for `tblReconcileResult`. Handles `NULL` values for `transID` and `stmtID`, captures the current Windows username via `Environ("USERNAME")`, and timestamps the reconciliation.

### modExport — Report Generation

[modExport](ar-reconciliation-vba-access/vba-source/modExport.bas) generates the reconciliation report.

- **RunExport()** — joins `tblReconcileResult` with `tblTransactions` on `TransID`, creating a result set for a specific batch. Builds an Excel workbook via late-bound COM automation, writes six columns (RefNo, Branch, TxnDate, Amount, MatchStatus, Variance), and highlights any row where `Variance <> 0` in light red. The file is saved as `ReconcileReport_yyyymm.xlsx` in the Access database directory. Returns the output path.

### modUtil — Shared Utilities

[modUtil](ar-reconciliation-vba-access/vba-source/modUtil.bas) provides three helper functions and one module-level state variable.

- **gCurrentBatchID** (`Long`, Public) — global variable that holds the most recently imported batch ID. Set by `RunImport()`, consumed by `RunReconcile()` and `RunExport()` when no explicit batch ID is passed. This is a module-level global variable in a VBA standard module.

- **GetNextBatchNumber()** — queries `Max(BatchID)` from `tblImportBatch` and returns `1` if no batches exist, or `max + 1` otherwise. Provides atomic batch ID generation.

- **LogError()** — inserts an error record into `tblErrorLog` with the batch ID, row number, error message (single-quotes escaped), and current timestamp.

- **FormatDateForSql()** — formats a VBA `Date` as `#mm/dd/yyyy#` for inclusion in SQL strings. Uses the `#` delimiter because Access/Jet SQL expects date literals in this format.

- **FormatNumberForSql()** — formats a `Currency` value for SQL inclusion. Uses `Str()` + `Trim()` to ensure a dot (`.`) decimal separator regardless of Windows locale. This is critical because VBA's default string concatenation uses the system locale's decimal separator (e.g., comma on Vietnamese Windows), which would break SQL statement parsing.

## Data Model

The module operates on five Access tables. This appears to be the schema:

| Table | Key Fields | Purpose |
|-------|-----------|---------|
| `tblTransactions` | `TransID` (PK), `Branch`, `TxnDate`, `Amount`, `RefNo`, `ImportBatchID` (FK) | Internal transactions loaded from Excel |
| `tblBankStatement` | `StmtID` (PK), `TxnDate`, `Amount`, `BankRefNo`, `ImportBatchID` (FK) | Bank statement transactions loaded from Excel |
| `tblReconcileResult` | `TransID` (FK), `StmtID` (FK), `MatchStatus`, `Variance`, `ReconciledBy`, `ReconciledDate` | Reconciliation outcome — links transactions to bank records with a status and variance |
| `tblImportBatch` | `BatchID` (PK), `FileName`, `ImportDate`, `RowCount`, `Status` | Tracks each import session |
| `tblErrorLog` | `BatchID`, `RowNo`, `ErrorMsg`, `Timestamp` | Logs validation failures per row |

## Notes for Developers

### VBA-specific conventions and gotchas

- **Late-bound COM automation**: Excel is opened via `CreateObject("Excel.Application")` rather than a reference to the Excel object library. This means you cannot use named constants like `xlUp` or `xlOpenXMLWorkbook` — instead you must use their numeric values (`-4162` and `51`). The code does this inline.

- **Locale-safe SQL formatting**: `modUtil.FormatNumberForSql()` uses `Str()` + `Trim()` specifically because `Currency` values concatenated as strings pick up the Windows locale decimal separator. This is a well-known VBA pitfall — always use these helpers when embedding numeric values in SQL strings.

- **Module-level globals**: `modUtil.gCurrentBatchID` is the only shared state between modules. If the application ever runs multiple concurrent imports, this global would become a race condition.

- **SQL injection risk**: String concatenation is used for all SQL statements throughout the module. Reference numbers are escaped via `Replace(…, "'", "''")`, but there is no parameterized query usage. If `Branch` or `RefNo` fields could contain malicious input (single quotes beyond the existing escape, or SQL keywords), this is a risk vector.

- **Recordset cleanup**: In `modReconcile.RunReconcile()`, the `rsBank` recordset is opened conditionally. The `rsBank.Close` calls after the exact match and after the fuzzy match ensure cleanup, but the recordset may already be closed at the second call for matched transactions. VBA tolerates this, but it is fragile.

- **Error handling during import**: `On Error Resume Next` is scoped to the file-opening retry loop only. The row-by-row insert uses implicit error handling — if `db.Execute` fails on a single row, the entire import crashes. Consider wrapping the inner `db.Execute` in `On Error Resume Next` to allow partial imports.

- **File locking retry**: The `Sleep 2000` between retry attempts is a simple but effective approach. For larger files or busy systems, consider increasing the delay or implementing exponential backoff.

### Extension points

- **Fuzzy matching**: The current fuzzy match only checks amount equality and a +-1 day date window. Additional fuzzy strategies (e.g., fuzzy string matching on `RefNo`, tolerance on amount) could be added to `modReconcile` for better match rates.

- **Export format**: `RunExport` hard-codes the output path pattern. Consider making it configurable or prompting the user for a save location.

- **Validation rules**: The date threshold in `clsTransaction.IsValid()` (`2000-01-01`) is a hardcoded magic date. Consider making this configurable.

- **Batch ID strategy**: `GetNextBatchNumber` uses a simple max-plus-one approach. This could generate gaps if imports are deleted, or duplicate IDs if two imports start simultaneously. Consider using an AutoNumber field in `tblImportBatch` instead.