# Business Logic — modReconcile.InsertResult() [8 LOC]

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

## 1. Role

### modReconcile.InsertResult()

`InsertResult` is a private utility subroutine that records a single reconciliation outcome into the `tblReconcileResult` Microsoft Access table. It is called by `modReconcile.RunReconcile()` after the main reconciliation logic (comparison of transaction lists) has determined a match status — such as matched, unmatched, or variance — for a given pair of records. The method constructs a parameter-free SQL `INSERT` statement and executes it directly against the local Access database via `db.Execute`. It captures the transaction identifier, statement identifier, match status string, monetary variance amount, the current Windows user (`Environ("USERNAME")`), and the current timestamp (`Now()`). This serves as the persistence layer for reconciliation results, enabling downstream reporting and audit trail generation. The method implements a simple builder pattern — it assembles SQL as a concatenated string and delegates execution to the `DAO.Database.Execute` method.

## 2. Processing Pattern (Detailed Business Logic)

```mermaid
flowchart TD
    START(["InsertResult(db, transID, stmtID, status, variance)"])
    STEP_BUILD_SQL["Build SQL string with VALUES clause"]
    STEP_FORMAT["Handle null transID and stmtID with IIfIsNull"]
    STEP_ENIRON["Enrich with EnvironUSERNAME and Now"]
    STEP_EXECUTE["db.Execute sql"]
    STEP_RETURN["Return / Next statement"]

    START --> STEP_BUILD_SQL
    STEP_BUILD_SQL --> STEP_FORMAT
    STEP_FORMAT --> STEP_ENIRON
    STEP_ENIRON --> STEP_EXECUTE
    STEP_EXECUTE --> STEP_RETURN
```

**Processing description:**

1. **Build SQL string** — Constructs an `INSERT INTO tblReconcileResult` statement with six columns: `TransID`, `StmtID`, `MatchStatus`, `Variance`, `ReconciledBy`, and `ReconciledDate`.
2. **Handle null IDs** — Uses `IIf(IsNull(transID), "NULL", transID)` to safely convert `Null` variants into the SQL `NULL` literal, preventing SQL syntax errors. The same logic applies to `stmtID`.
3. **Enrich with runtime context** — Embeds the current Windows username via `Environ("USERNAME")` for audit trail purposes, and the current date/time via `Now()` for the reconciliation timestamp.
4. **Execute** — Passes the assembled SQL string to `db.Execute(sql)` for direct execution against the Access database.
5. **Return** — No return value; subroutine exits and control returns to the caller (`RunReconcile`).

## 3. Parameter Analysis

| No | Parameter Name | Type | Business Description |
|----|---------------|------|---------------------|
| 1 | `db` | `DAO.Database` | The active Access database connection object used to execute the INSERT statement. |
| 2 | `transID` | `Variant` | The transaction identifier from the reconciliation source (e.g., bank statement or internal ledger). May be `Null` if the transaction lacks an ID. |
| 3 | `stmtID` | `Variant` | The statement identifier from the reconciliation source. May be `Null` if the statement lacks an ID. |
| 4 | `status` | `String` | The match status determined by `RunReconcile`, describing whether the record pair was matched, unmatched, or had a variance. |
| 5 | `variance` | `Currency` | The monetary difference (in local currency) between the reconciled record pair. `0` indicates a perfect match. |

**External state accessed:**

| Source | Description |
|--------|-------------|
| `Environ("USERNAME")` | Retrieves the current Windows login username for audit trail purposes. |
| `Now()` | Returns the current system date/time to stamp the reconciliation result record. |

## 4. CRUD Operations / Called Services

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

| CRUD | SC / CBS | SC Code | Entity / DB | Operation Description |
|------|----------|---------|-------------|----------------------|
| - | `modUtil.FormatNumberForSql` | mod | - | Calls `FormatNumberForSql` in `modUtil` to format the Currency variance for SQL insertion |

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

| Access Table | Foreign-key reference → |
|---|---|
| tblReconcileResult | — |

### Full CRUD classification

| CRUD | SC / CBS | SC Code | Entity / DB | Operation Description |
|------|----------|---------|-------------|----------------------|
| C | `modReconcile.InsertResult` | — | `tblReconcileResult` | Inserts a new reconciliation result record with transaction ID, statement ID, match status, variance, auditor username, and timestamp |

## 5. Dependency Trace

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

No screen/batch entry points found within 8 hops. Direct callers found: 1 methods.
Terminal operations from this method: `FormatNumberForSql` [-]

| # | Caller (Screen/Batch) | Call Chain (Full Path to this Method) | Terminal (SC / CRUD / Entity) |
|---|----------------------|--------------------------------------|-------------------------------|
| 1 | `modReconcile.RunReconcile` | `RunReconcile` → `InsertResult` | `FormatNumberForSql [-]` |

## 6. Per-Branch Detail Blocks

**Block 1** — [EXEC] `(SQL assembly with conditional null handling)` (L62–L66)

> Builds the complete SQL INSERT statement. Uses `IIf` expressions to guard against `Null` values in `transID` and `stmtID`, converting them to the SQL literal `"NULL"` rather than embedding a VBA `Null` which would break SQL syntax.

| # | Type | Code |
|---|------|------|
| 1 | SET | `sql = "INSERT INTO tblReconcileResult (TransID, StmtID, MatchStatus, Variance, ReconciledBy, ReconciledDate) VALUES ("` // Begin SQL string |
| 2 | EXEC | `IIf(IsNull(transID), "NULL", transID)` // If transID is Null, insert SQL literal NULL; otherwise use the value |
| 3 | EXEC | `IIf(IsNull(stmtID), "NULL", stmtID)` // If stmtID is Null, insert SQL literal NULL; otherwise use the value |
| 4 | EXEC | `status` // Raw string value for MatchStatus column |
| 5 | CALL | `modUtil.FormatNumberForSql(variance)` // Format Currency for SQL — prevents locale-related decimal separator issues |
| 6 | EXEC | `Environ("USERNAME")` // Windows login name for audit trail |
| 7 | EXEC | `Now()` // Current system date/time for ReconciledDate |
| 8 | SET | `sql = "...")"` // Close SQL string and assign to local variable |

**Block 2** — [EXEC] `(SQL execution)` (L67)

> Executes the assembled SQL statement against the Access database.

| # | Type | Code |
|---|------|------|
| 1 | CALL | `db.Execute sql` // Runs the INSERT against the DAO database connection |

## 7. Glossary

| Term | Type | Business Meaning |
|------|------|------------------|
| `tblReconcileResult` | Table | Reconciliation Result — Access table storing per-record reconciliation outcomes including match status, variance, and audit information |
| `TransID` | Field | Transaction ID — identifier for a financial transaction being reconciled |
| `StmtID` | Field | Statement ID — identifier for the source statement (e.g., bank statement line) |
| `MatchStatus` | Field | Match Status — textual classification of the reconciliation outcome (e.g., matched, unmatched, partial) |
| `Variance` | Field | Variance — monetary difference between the reconciled record pair, stored as Currency type |
| `ReconciledBy` | Field | Reconciled By — Windows username of the user who performed the reconciliation, captured via `Environ("USERNAME")` |
| `ReconciledDate` | Field | Reconciled Date — timestamp of when the reconciliation result was recorded, captured via `Now()` |
| `DAO.Database` | Type | Data Access Objects Database — Microsoft Access library object for executing SQL against a .mdb/.accdb database file |
| `db.Execute` | Method | DAO method that executes a SQL statement against the database connection |
| `IIf` | Function | Immediate If — VBA ternary-style function `IIf(condition, truePart, falsePart)` |
| `Environ` | Function | VBA function that reads Windows environment variables; `Environ("USERNAME")` returns the current user's login name |
| `Now` | Function | VBA function returning the current system date and time |
| `FormatNumberForSql` | Method | Utility function in `modUtil` that formats a Currency value as a SQL-safe numeric string, handling locale-specific decimal separators |
| `Null` (Variant) | Value | VBA `Null` variant indicating the absence of a value; distinct from empty string `""` or zero |