# Business Logic — modReconcile.RunReconcile() [54 LOC]

| Field | Value |
|-------|-------|
| Fully Qualified Name | `modReconcile.RunReconcile` |
| Layer | Utility (Package: `vba-source/modReconcile.bas`) |
| Module | `(unknown)` (Package: `vba-source/modReconcile.bas`) |

## 1. Role

### modReconcile.RunReconcile()

This method implements a **bank statement reconciliation engine**: it compares imported bank transactions (stored in `tblTransactions`) against actual bank statement records (stored in `tblBankStatement`) and determines whether each transaction can be matched to a statement line. The reconciliation follows a two-tier matching strategy — first an exact match on the reference number (`RefNo`), then a fuzzy fallback that matches by amount and date proximity (within +/-1 day). For transactions that are duplicates (same `RefNo` appearing more than once within the same import batch), the method records them as unmatched duplicates rather than attempting reconciliation. After evaluating every transaction, results are written to `tblReconcileResult` along with a variance field (the difference between the transaction amount and the matched bank statement amount, or the full transaction amount if unmatched). The method returns the total count of successfully matched transactions (both exact and fuzzy).

The method serves as a **shared utility entry point** — it does not appear to be called from any other VBA module in the codebase, suggesting it is invoked directly from a form UI button or a batch process. It delegates to its own private helper `InsertResult` for result persistence, and to `modUtil.FormatDateForSql` and `modUtil.FormatNumberForSql` for locale-safe SQL value formatting.

Conditional branches:
- **Duplicate detection**: If a `RefNo` appears more than once in the batch, the transaction is marked `"Duplicate"` with zero variance.
- **Exact match**: If the `RefNo` matches a `BankRefNo` in `tblBankStatement`, the transaction is marked `"Matched"` and the variance is `TransactionAmount - BankStatementAmount`.
- **Fuzzy fallback**: If no exact match is found, a fallback search by `Amount` and `TxnDate +/- 1 day` is attempted; if successful, marked `"Matched (Fuzzy)"`.
- **Unmatched**: If neither exact nor fuzzy match is found, the transaction is marked `"Unmatched"` with variance equal to its full `Amount`.

## 2. Processing Pattern (Detailed Business Logic)

```mermaid
flowchart TD
    START(["RunReconcile batchID"])
    STEP1["Resolve effectiveBatchID: IIf(batchID=-1, gCurrentBatchID, batchID)"]
    STEP2["Set db = CurrentDb()"]
    STEP3["DELETE FROM tblReconcileResult for matching TransIDs"]
    STEP4["Open rsTrans = tblTransactions WHERE ImportBatchID"]
    STEP5["matchedCount = 0"]
    STEP6{"rsTrans.EOF?"}
    STEP7["seenRefNoCount = DCount of RefNo in tblTransactions"]
    STEP8{"seenRefNoCount > 1?"}
    STEP9["InsertResult: Duplicate"]
    STEP10["Open rsBank = tblBankStatement WHERE BankRefNo = RefNo"]
    STEP11{"rsBank.EOF?"}
    STEP12["InsertResult: Matched, variance = Amount - BankAmount"]
    STEP13["matchedCount = matchedCount + 1"]
    STEP14["rsBank.Close"]
    STEP15["Open rsBank = fuzzy search by Amount +/- 1 day"]
    STEP16{"rsBank.EOF?"}
    STEP17["InsertResult: Matched Fuzzy, variance"]
    STEP18["matchedCount = matchedCount + 1"]
    STEP19["rsBank.Close"]
    STEP20["InsertResult: Unmatched, variance = Amount"]
    STEP21["rsTrans.MoveNext"]
    STEP22["rsTrans.Close"]
    STEP23["Set rsTrans = Nothing"]
    STEP24["Set db = Nothing"]
    END(["Return matchedCount"])

    START --> STEP1
    STEP1 --> STEP2
    STEP2 --> STEP3
    STEP3 --> STEP4
    STEP4 --> STEP5
    STEP5 --> STEP6
    STEP6 -->|No| STEP7
    STEP7 --> STEP8
    STEP8 -->|True| STEP9
    STEP9 --> STEP21
    STEP8 -->|False| STEP10
    STEP10 --> STEP11
    STEP11 -->|False| STEP12
    STEP12 --> STEP13
    STEP13 --> STEP14
    STEP11 -->|True| STEP15
    STEP15 --> STEP16
    STEP16 -->|False| STEP17
    STEP17 --> STEP18
    STEP18 --> STEP19
    STEP16 -->|True| STEP20
    STEP20 --> STEP14
    STEP14 --> STEP21
    STEP21 --> STEP6
    STEP6 -->|Yes| STEP22
    STEP22 --> STEP23
    STEP23 --> STEP24
    STEP24 --> END
```

**CRITICAL -- Constant Resolution:**

No domain-specific constants are used in this method. Control flow is driven entirely by runtime data (recordset counts, database lookups). The sole conditional constant-like pattern is the sentinel value `-1` for `batchID`, which signals "use the current active batch" and is resolved to `modUtil.gCurrentBatchID` at runtime.

**Requirements:**

- Every if/else branch is represented as a diamond decision node.
- The main loop is a `Do While Not rsTrans.EOF` which iterates over every transaction in the batch.
- Within each iteration, there are three tiers of matching logic: duplicate detection, exact match, and fuzzy fallback.
- The `rsBank` recordset is explicitly closed before any subsequent query on the same variable to avoid recordset leaks.
- Cleanup is explicit: `rsTrans.Close`, `Set rsTrans = Nothing`, `Set db = Nothing`.

## 3. Parameter Analysis

| No | Parameter Name | Type | Business Description |
|----|---------------|------|---------------------|
| 1 | `batchID` | Long (Optional, default -1) | Import batch identifier — identifies which set of imported transactions to reconcile. When `-1` (the default), the method uses the global active batch ID (`modUtil.gCurrentBatchID`), meaning it reconciles against the most recently imported batch. A specific batch ID restricts reconciliation to that batch's transactions only. |

**External state / instance fields read by the method:**

| Source | Field | Business Description |
|--------|-------|---------------------|
| `modUtil.gCurrentBatchID` | Global variable | The currently active import batch ID — used when `batchID` is omitted or set to the sentinel `-1`. Set by the import process (see `modImport.bas` / `GetNextBatchNumber`) before reconciliation is triggered. |
| `Environ("USERNAME")` | OS environment variable | The Windows login username — recorded in `ReconciledBy` field of results for audit trail. |

## 4. CRUD Operations / Called Services

### Pre-computed evidence from code analysis graph:

| CRUD | SC / CBS | SC Code | Entity / DB | Operation Description |
|------|----------|---------|-------------|----------------------|
| D | `modReconcile.RunReconcile` | - | `tblReconcileResult` | Deletes existing reconcile results for the given batch before recomputing. |
| R | `modReconcile.RunReconcile` | - | `tblTransactions` | Reads all transactions belonging to the batch; also performs a `DCount` subquery for duplicate detection. |
| R | `modReconcile.RunReconcile` | - | `tblBankStatement` | Looks up bank statement records by `BankRefNo` (exact match) and by `Amount` + `TxnDate` (fuzzy match). |
| C | `modReconcile.InsertResult` | modReconcile | `tblReconcileResult` | Inserts a reconciliation result row for each transaction (one per transaction in the batch). |
| - | `modUtil.FormatDateForSql` | modUtil | - | Formats a Date as a locale-safe SQL string (`#mm/dd/yyyy#`). |
| - | `modUtil.FormatNumberForSql` | modUtil | - | Formats a Currency as a locale-safe SQL string using `Str()` with `Trim`. |

### Database Tables (code-to-DB map, from the code graph)

| Access Table | Foreign-key reference -> |
|---|---|
| tblImportBatch | (referenced via ImportBatchID, not owned by this method) |
| tblTransactions | ImportBatchID -> tblImportBatch |
| tblBankStatement | StmtID (primary key, used as reference in reconcile results) |
| tblReconcileResult | TransID -> tblTransactions; StmtID -> tblBankStatement |

### Detailed method call classification:

| CRUD | SC / CBS | SC Code | Entity / DB | Operation Description |
|------|----------|---------|-------------|----------------------|
| D | `modReconcile.RunReconcile` (self-deleted) | - | `tblReconcileResult` | Deletes stale reconciliation results for the current batch before re-running, ensuring idempotent re-reconciliation. |
| R | `modReconcile.RunReconcile` (recordset open) | - | `tblTransactions` | Loads all transactions for the batch into a recordset for iteration. |
| R | `modReconcile.RunReconcile` (DCount) | - | `tblTransactions` | Counts occurrences of each `RefNo` within the batch to detect duplicates. |
| R | `modReconcile.RunReconcile` (exact match) | - | `tblBankStatement` | Looks up a bank statement record by exact `BankRefNo` match against the transaction's `RefNo`. |
| R | `modReconcile.RunReconcile` (fuzzy match) | - | `tblBankStatement` | Falls back to matching by amount and date proximity (TxnDate +/- 1 day) when exact match fails. |
| C | `modReconcile.InsertResult` | modReconcile | `tblReconcileResult` | Inserts a reconciliation outcome row: the transaction ID, matched statement ID (or NULL), match status label, monetary variance, reconciling user, and timestamp. |

## 5. Dependency Trace

| # | Caller (Screen/Batch) | Call Chain (Full Path to this Method) | Terminal (SC / CRUD / Entity) |
|---|----------------------|--------------------------------------|-------------------------------|
| 1 | (no callers found) | N/A | `InsertResult [C] tblReconcileResult` |
| 2 | N/A | N/A | `DELETE FROM tblReconcileResult [D] tblReconcileResult` |
| 3 | N/A | N/A | `SELECT tblTransactions [R] tblTransactions` |
| 4 | N/A | N/A | `SELECT tblBankStatement [R] tblBankStatement` |

**Notes on caller analysis:**
- No callers were found across all `.bas`, `.cls`, or `.frm` files in the codebase. This method appears to be a **top-level entry point** invoked directly from a form button click event or an external automation trigger (e.g., a macro or Access form event handler not stored in the scanned VBA modules).
- The method is self-contained within `modReconcile.bas` and calls only its own private helper `InsertResult` plus utility formatters from `modUtil`.
- No SC codes, CBS layers, or enterprise service components are involved — this is a pure Access/VBA local-data reconciliation routine.

## 6. Per-Branch Detail Blocks

**Block 1** — [SET] `effectiveBatchID = IIf(batchID = -1, modUtil.gCurrentBatchID, batchID)` (L4)

> Resolve the effective batch ID. The sentinel value `-1` means "use the current active batch."

| # | Type | Code | Business Description |
|---|------|------|---------------------|
| 1 | SET | `effectiveBatchID = IIf(batchID = -1, modUtil.gCurrentBatchID, batchID)` | Resolves batch ID; `-1` is the sentinel for "use active batch" [-> sentinel `-1` = "use gCurrentBatchID"] |
| 2 | SET | `Set db = CurrentDb()` | Opens the current Access database connection |
| 3 | EXEC | `db.Execute "DELETE FROM tblReconcileResult WHERE TransID IN (SELECT TransID FROM tblTransactions WHERE ImportBatchID = effectiveBatchID)"` | Clears stale reconcile results for this batch (ensures idempotent re-reconciliation) |
| 4 | SET | `Set rsTrans = db.OpenRecordset("SELECT * FROM tblTransactions WHERE ImportBatchID = effectiveBatchID")` | Opens the transaction recordset for the batch |
| 5 | SET | `matchedCount = 0` | Initializes the match counter |
| 6 | EXEC | `Do While Not rsTrans.EOF` ... `Loop` | Main iteration over all transactions |

**Block 6** — [WHILE-LOOP] `Not rsTrans.EOF` (L16)

> Iterates over every transaction in the batch. Each iteration performs duplicate detection, then matching.

| # | Type | Code | Business Description |
|---|------|------|---------------------|
| 1 | SET | `seenRefNoCount = DCount("*", "tblTransactions", "RefNo = '" & Replace(rsTrans!RefNo, "'", "''") & "' AND ImportBatchID = effectiveBatchID")` | Counts occurrences of this transaction's RefNo within the batch [SQL injection mitigation: single quotes in RefNo are escaped via Replace] |

**Block 6.1** — [IF] `seenRefNoCount > 1` (L18)

> If the same reference number appears more than once in the batch, the transaction cannot be confidently matched. It is recorded as a duplicate.

| # | Type | Code | Business Description |
|---|------|------|---------------------|
| 1 | CALL | `InsertResult db, rsTrans!TransID, Null, "Duplicate", 0` | Records the transaction as a duplicate with null statement ID and zero variance |

**Block 6.2** — [ELSE] `seenRefNoCount <= 1` (L20)

> The reference number is unique in this batch. Attempt to match against bank statement records.

**Block 6.2.1** — [SET] Exact match lookup (L21)

| # | Type | Code | Business Description |
|---|------|------|---------------------|
| 1 | SET | `Set rsBank = db.OpenRecordset("SELECT * FROM tblBankStatement WHERE BankRefNo = '" & Replace(rsTrans!RefNo, "'", "''") & "'")` | Opens bank statement recordset searching by exact BankRefNo match. SQL injection mitigation via Replace. |

**Block 6.2.2** — [IF] `Not rsBank.EOF` (exact match found) (L22)

> An exact reference number match was found in the bank statement.

| # | Type | Code | Business Description |
|---|------|------|---------------------|
| 1 | CALL | `InsertResult db, rsTrans!TransID, rsBank!StmtID, "Matched", rsTrans!Amount - rsBank!Amount` | Records an exact match; variance = transaction amount minus bank statement amount |
| 2 | SET | `matchedCount = matchedCount + 1` | Increments the successful match counter |
| 3 | EXEC | `rsBank.Close` | Closes the exact-match recordset |

**Block 6.2.3** — [ELSE] `rsBank.EOF` (no exact match) (L26)

> No exact reference number match found. Attempt fuzzy fallback.

| # | Type | Code | Business Description |
|---|------|------|---------------------|
| 1 | EXEC | `rsBank.Close` | Closes the empty exact-match recordset before reassigning |
| 2 | SET | `Set rsBank = db.OpenRecordset("SELECT * FROM tblBankStatement WHERE Amount = " & modUtil.FormatNumberForSql(rsTrans!Amount) & " AND TxnDate BETWEEN " & modUtil.FormatDateForSql(rsTrans!TxnDate - 1) & " AND " & modUtil.FormatDateForSql(rsTrans!TxnDate + 1))` | Fuzzy match: same amount and transaction date within +/- 1 day. Uses locale-safe formatters. |

**Block 6.2.4** — [IF] `Not rsBank.EOF` (fuzzy match found) (L29)

> A fuzzy match was found by amount and date proximity.

| # | Type | Code | Business Description |
|---|------|------|---------------------|
| 1 | CALL | `InsertResult db, rsTrans!TransID, rsBank!StmtID, "Matched (Fuzzy)", rsTrans!Amount - rsBank!Amount` | Records a fuzzy match with variance = transaction amount minus bank statement amount |
| 2 | SET | `matchedCount = matchedCount + 1` | Increments the successful match counter |
| 3 | EXEC | `rsBank.Close` | Closes the fuzzy-match recordset |

**Block 6.2.5** — [ELSE] `rsBank.EOF` (no fuzzy match) (L33)

> Neither exact nor fuzzy match found. The transaction is unmatched.

| # | Type | Code | Business Description |
|---|------|------|---------------------|
| 1 | CALL | `InsertResult db, rsTrans!TransID, Null, "Unmatched", rsTrans!Amount` | Records the transaction as unmatched; variance = full transaction amount (no offset) |
| 2 | EXEC | `rsBank.Close` | Closes the empty fuzzy-match recordset |

**Block 6.3** — [LOOP CONTINUATION] `rsTrans.MoveNext` (L37)

> Advances to the next transaction in the batch recordset.

**Block 7** — [CLEANUP] End of function (L39)

| # | Type | Code | Business Description |
|---|------|------|---------------------|
| 1 | EXEC | `rsTrans.Close` | Closes the transaction recordset |
| 2 | SET | `Set rsTrans = Nothing` | Releases the transaction recordset object |
| 3 | SET | `Set db = Nothing` | Releases the database connection object |
| 4 | RETURN | `RunReconcile = matchedCount` | Returns the total count of successfully matched transactions |

## 7. Glossary

| Term | Type | Business Meaning |
|------|------|------------------|
| `batchID` | Parameter | Import batch identifier — a numeric key that groups a set of imported bank transactions together for reconciliation. `-1` is a sentinel meaning "use the current active batch." |
| `effectiveBatchID` | Field | The resolved batch ID used internally; equals `batchID` unless `batchID = -1`, in which case it equals `modUtil.gCurrentBatchID`. |
| `gCurrentBatchID` | Global | The most recently created import batch ID, set by the import process (`modUtil.GetNextBatchNumber`). Acts as the default target for reconciliation. |
| `RefNo` | Field | Reference number — a transaction-level identifier (e.g., check number, transfer reference) used to match imported transactions against bank statement records. |
| `BankRefNo` | Field | Bank-provided reference number — the corresponding identifier on the bank statement side, matched against `RefNo`. |
| `StmtID` | Field | Statement ID — the primary key of a record in `tblBankStatement`, representing a specific line item on a bank statement. |
| `TransID` | Field | Transaction ID — the primary key of a record in `tblTransactions`, uniquely identifying an imported transaction. |
| `MatchStatus` | Field | A text label in `tblReconcileResult` indicating the reconciliation outcome: "Duplicate", "Matched", "Matched (Fuzzy)", or "Unmatched". |
| `Variance` | Field | The monetary difference between the transaction amount and the matched bank statement amount. Zero for duplicates; full transaction amount for unmatched. |
| `ReconciledBy` | Field | The Windows username of the user who ran the reconciliation, captured via `Environ("USERNAME")`. |
| `ReconciledDate` | Field | The timestamp (server date via `Now()`) when the reconciliation was performed. |
| `tblTransactions` | Table | Imported bank transactions — holds records of bank activity imported into Access from external sources (e.g., CSV, bank file upload). |
| `tblBankStatement` | Table | Source of truth bank statement records — the authoritative bank data that transactions are reconciled against. |
| `tblReconcileResult` | Table | Reconciliation output — stores the result of each reconciliation run: which transactions matched which statement lines, the match type, and the monetary variance. |
| `tblImportBatch` | Table | Import batch metadata — tracks batches of imported data; referenced via `ImportBatchID` to group related transactions. |
| `DCount` | Function | Access built-in domain aggregate function — counts the number of records in a table matching a criterion. Used here to detect duplicate `RefNo` values within a batch. |
| `CurrentDb()` | Function | Access DAO function — returns a reference to the currently open Access database. |
| `FormatDateForSql` | Utility | `modUtil` function that formats a `Date` as a SQL-compatible string in US format: `#mm/dd/yyyy#`. Required for correct SQL date literals in Access. |
| `FormatNumberForSql` | Utility | `modUtil` function that formats a `Currency` as a locale-safe SQL string using `Str()` (which always uses `.` as decimal separator) plus `Trim()`. Prevents SQL errors on non-English Windows locales. |
| `Do While Not rsTrans.EOF` | Pattern | VBA DAO recordset iteration pattern — loops through all records until End-Of-File is reached. |
| SQL injection mitigation | Pattern | `Replace(rsTrans!RefNo, "'", "''")` doubles any single quotes in the RefNo value before embedding it in a SQL string, preventing malformed SQL and injection attacks. |