Skip to content

REMS VBA Module Documentation

Raizcorp Entrepreneurial Management System

1. Module Overview

Module Size Purpose
Global.bas ~4 KB Core database utilities
Mod Utilities.bas ~11 KB General utility functions
Mod Populate Temp Table.bas ~13 KB Report data preparation
Mod Export Excel.bas 307 KB Excel report generation
basRelink.bas ~24 KB ODBC connection management
ExportModule.bas ~13 KB Database export utility
Module1.bas <1 KB Empty/placeholder

2. Global.bas

Core database utility functions.

2.1 DigiReport()

Function DigiReport(Optional DteFrm As Date, Optional DteTo As Date)

Purpose: Generates digital selection report data

Parameters: - DteFrm (Optional): Start date for report period - DteTo (Optional): End date (defaults to current date)

Behavior: 1. Cleans TempLeadsReport table 2. Calculates default date range if not provided (2 months back) 3. Inserts entrepreneur data from main tables into temp table 4. Includes: Record dates, termination reasons, assessment scores, status info

SQL Operations: - INSERT INTO TempLeadsReport from Entrepreneurs with LEFT JOINs to TerminationReason and ProcessStatus

2.2 SQLRun()

Function SQLRun(SQLString As String, Optional IgnoreError As Boolean = False,
                Optional IsQuery As Boolean = False) As Boolean

Purpose: Safe SQL execution wrapper

Parameters: - SQLString: SQL statement or query name to execute - IgnoreError: Suppress error messages (default: False) - IsQuery: True to open saved query, False to run raw SQL

Returns: Boolean - True if successful

Behavior: - Suppresses Access warnings during execution - Handles errors gracefully - Restores warning state after execution

2.3 CleanTbl()

Function CleanTbl(sTblNme As String, Optional sWhere As String = "") As Boolean

Purpose: Deletes records from a table

Parameters: - sTblNme: Table name to clean - sWhere: Optional WHERE clause filter

Behavior: - Validates table exists - Executes DELETE * with optional WHERE clause - Suppresses confirmation prompts

2.4 IsTable()

Function IsTable(sTablenme As String) As Boolean

Purpose: Checks if a table exists in the database

Parameters: - sTablenme: Table name to check (handles brackets)

Returns: Boolean - True if table exists

3. Mod Utilities.bas

General-purpose utility functions.

3.1 Public Variables

Public ReportName As String      ' Current report name for dialogs
Public ListReportName As String  ' Dashboard report name

3.2 DeleteEntrepreneur()

Sub DeleteEntrepreneur(EPID As Long)

Purpose: Cascading delete of entrepreneur and all related records

Parameters: - EPID: Entrepreneur ID to delete

Cascade Order: 1. Check for linked company 2. If company exists and user confirms: - Delete Entrepreneur Company Principal Projects - Delete Entrepreneur Company Programme Year Status History - Delete Entrepreneur Company Programme Years - Delete Entrepreneur Company Areas of Operation - Delete Entrepreneur Company Commodities - Delete Entrepreneur Company Status History - Delete Entrepreneur Company record 3. Delete Entrepreneur Arize First Meeting 4. Delete Entrepreneur Edit History 5. Delete Entrepreneur Notes 6. Delete Entrepreneur Profile Presented To 7. Delete Entrepreneur Purple Panelists 8. Delete Entrepreneur Status History 9. Delete Entrepreneur Year One Profile 10. Delete Entrepreneurs record

3.3 AllocateSpecificEPs()

Sub AllocateSpecificEPs()

Purpose: Batch allocation of entrepreneurs to programme

Behavior: 1. Opens query of uncontacted/unfollowed entrepreneurs 2. For each entrepreneur: - Creates company if not exists - Updates entrepreneur status to allocated - Generates Student ID (Year-EntrepreneurID) - Assigns Allocation ID - Creates status history entry

3.4 GetXofY()

Function GetXofY(f As Form) As String

Purpose: Returns "X of Y" record position string

Parameters: - f: Form reference

Returns: String like "5 of 100"

3.5 IsLoaded()

Function IsLoaded(ByVal strFormName As String) As Boolean

Purpose: Checks if a form is currently open

Parameters: - strFormName: Name of form to check

Returns: Boolean - True if form is open in Form/Datasheet view

3.6 Password Validation Functions

Function CheckForUpper(CheckString As String) As Boolean
Function CheckForSpecial(CheckString As String) As Boolean
Function CheckForNumber(CheckString As String) As Boolean

Purpose: Password complexity validation

CheckForUpper: Checks for uppercase letters (A-Z) CheckForSpecial: Checks for special characters (punctuation) CheckForNumber: Checks for numeric digits (0-9)

3.7 MoveWD()

Public Function MoveWD(datThis As Date, intInc As Integer) As Date

Purpose: Adds/subtracts weekdays (excluding weekends)

Parameters: - datThis: Starting date - intInc: Number of weekdays to move (positive or negative)

Returns: Date - Resulting date after moving weekdays

3.8 DeleteImportErrorTables()

Sub DeleteImportErrorTables()

Purpose: Cleans up import error tables created during data imports

Behavior: Deletes all tables with "ImportErrors" in the name

4. Mod Populate Temp Table.bas

Report data preparation functions.

4.1 PopulateAvgDaysStepTable()

Sub PopulateAvgDaysStepTable()

Purpose: Prepares data for pipeline movement reports

Behavior: 1. Cleans Temp Entrepreneur Steps table 2. Queries entrepreneurs modified in date range 3. Populates temp table with: - Entrepreneur ID - Date Record Created - Pipeline ID - Step 8 First Date

4.2 PopulateRaizcorpPossiblesTempTable()

Sub PopulateRaizcorpPossiblesTempTable()

Purpose: Identifies Raizcorp Possibles moved in last week

Behavior: 1. Cleans temp table 2. Queries moved entrepreneurs by company 3. Checks for status changes to Active/Allocated/Exited 4. Populates temp table with qualifying company IDs

4.3 PopulatePurpleMeetingStepTable()

Sub PopulatePurpleMeetingStepTable()

Purpose: Prepares Purple Panel meeting summary

Behavior: 1. Cleans Temp Purple Meeting Status table 2. Queries entrepreneurs by step and project 3. Pivots data into columns: - Step 2 through Step 8 counts - By Recommended Principal Project ID

4.4 PopulateTempStepsSkippedErrors()

Sub PopulateTempStepsSkippedErrors()

Purpose: Identifies entrepreneurs who skipped pipeline steps

Behavior: 1. Queries entrepreneur step history 2. Records last date for each step (0-8) 3. Compares against required pipeline steps 4. Flags "Steps Skipped" if any required step missing

Output Fields: - Step 0-8 Last Date - Steps Skipped (Boolean)

4.5 PopulateStepTableForSummaryReport()

Sub PopulateStepTableForSummaryReport()

Purpose: Prepares entrepreneur summary report data

Behavior: 1. Queries step dates for entrepreneurs in date range 2. Records last date for each step 3. Queries reactivated entrepreneurs 4. Flags reactivated status

5. Mod Export Excel.bas

Excel report generation (307KB - largest module).

5.1 Overview

Contains numerous Excel export functions using COM automation: - Creates new Excel workbook - Formats headers with bold - Populates data from queries - Formats date columns

5.2 Key Functions

ExportDetailedPrincipalToExcel()

Purpose: Exports detailed principal report

Columns: 1. Lead Type 2. Entrepreneur 3. Company Name 4. Industry 5. Brief Description of Business 6. Status 7. Recommended BBBEE Pillar 8. Note Date 9. Notes

ExportPrincipalSupplierListAndMarketingToExcel()

Purpose: Exports supplier list and marketing data

Sections: - Supplier List progress categories - Raizcorp Marketing progress categories - Unqualified summaries with reasons

5.3 Pattern Used

' Typical Excel export pattern
Set ExcelObj = CreateObject("Excel.Application")
ExcelObj.Visible = True
ExcelObj.Application.Workbooks.Add

' Headers
ExcelObj.Application.Worksheets(1).Cells(RowPtr, 1) = "Column Name"
ExcelObj.Application.Worksheets(1).Cells(RowPtr, 1).Font.Bold = True

' Data loop
Do Until RecSet.EOF
    ExcelObj.Application.Worksheets(1).Cells(RowPtr, 1) = RecSet.Fields("FieldName")
    RowPtr = RowPtr + 1
    RecSet.MoveNext
Loop

6. basRelink.bas

ODBC connection management for SQL Server backend.

6.1 RelinkODBC()

Public Function RelinkODBC() As Boolean

Purpose: Relinks all ODBC tables to SQL Server

Behavior: 1. Validates single database selected in zstblODBC_db 2. Removes existing ODBC links 3. Reads table configuration from qryLinkODBC 4. Creates new links with appropriate authentication: - Windows Authentication (Trusted_Connection) - SQL Server Authentication (UID/PWD) 5. Executes AfterDDL scripts if specified 6. Sets hidden attribute if configured

Configuration Tables: - zstblODBC_db: Database connection settings - zstblODBC_tables: Tables to link

6.2 AttachDSNLessTable()

Public Function AttachDSNLessTable(
    ByRef pstrTableNameLocal As String,
    ByRef pstrTableNameRemote As String,
    ByRef pstrServerName As String,
    ByRef pstrDatabaseName As String,
    Optional ByRef pstrUserName As String,
    Optional ByRef pstrPassword As String
) As Boolean

Purpose: Creates DSN-less ODBC connection to single table

Parameters: - Local table name (in Access) - Remote table name (on SQL Server) - Server name - Database name - Username (optional - uses Windows auth if blank) - Password (optional)

6.3 TableExists()

Public Function TableExists(ByVal pstrTableName As String) As Boolean

Purpose: Checks if table exists in current database

6.4 PopulateTableList()

Public Sub PopulateTableList()

Purpose: Populates zstblODBC_tables from current TableDefs

7. ExportModule.bas

Database object export utility.

7.1 ExportAllCode()

Public Sub ExportAllCode()

Purpose: Interactive export of all database objects

Behavior: 1. Prompts for output folder 2. Creates subfolders: Modules, Forms, Reports, Macros, Queries, Tables 3. Asks about query inclusion 4. Exports all objects 5. Reports statistics

7.2 ExportAllCodeToFolder()

Public Sub ExportAllCodeToFolder(outputFolder As String,
                                  Optional includeQueries As Boolean = True)

Purpose: Automated export (no prompts)

7.3 Export Functions

Function Object Type File Extension
ExportModules() VBA Modules .bas
ExportForms() Forms .txt
ExportReports() Reports .txt
ExportMacros() Macros .txt
ExportQueries() Queries .txt
ExportTableSchemas() Table Structures .txt

7.4 Helper Functions

Private Function GetFieldTypeName(fieldType As Integer) As String
Private Function SanitizeFileName(fileName As String) As String
Private Sub CreateFolderIfNotExists(folderPath As String)
Private Function BrowseForFolder(prompt As String) As String

8. Error Handling Patterns

Standard Pattern

Sub ExampleProcedure()
On Error GoTo Err_ExampleProcedure

    ' Main code here

Exit_ExampleProcedure:
    Exit Sub

Err_ExampleProcedure:
    MsgBox Err.Description
    Resume Exit_ExampleProcedure
End Sub

'TVCodeTools ErrorEnablerStart
On Error GoTo PROC_ERR
'TVCodeTools ErrorEnablerEnd

' Code here

'TVCodeTools ErrorHandlerStart
PROC_EXIT:
    Exit Function

PROC_ERR:
    MsgBox Err.Description, vbCritical, "ModuleName.ProcedureName"
    Resume PROC_EXIT
'TVCodeTools ErrorHandlerEnd

9. Common Patterns

9.1 DAO Recordset Operations

Dim Actdatabase As DAO.Database
Dim RecSet As DAO.Recordset

Set Actdatabase = CurrentDb
Set RecSet = Actdatabase.OpenRecordset("TableName", dbOpenDynaset, dbSeeChanges)

Do Until RecSet.EOF
    ' Process record
    RecSet.MoveNext
Loop

RecSet.Close

9.2 Parameter Query Execution

Dim QryDef As DAO.QueryDef
Dim Prm As Parameter

Set QryDef = Actdatabase.QueryDefs("QueryName")

For Each Prm In QryDef.Parameters
    Prm.Value = Eval(Prm.Name)  ' Evaluates form references
Next Prm

Set RecSet = QryDef.OpenRecordset(dbOpenDynaset, dbSeeChanges)

9.3 Suppress Warnings

Application.SetOption "Confirm Action Queries", False
DoCmd.RunSQL "DELETE * FROM [TableName]"
Application.SetOption "Confirm Action Queries", True

' Or using DoCmd
DoCmd.SetWarnings False
DoCmd.RunSQL SQLString
DoCmd.SetWarnings True

10. Dependencies

Required References

  • Microsoft DAO 3.6 Object Library
  • Microsoft Office Excel Object Library (for exports)
  • Microsoft Scripting Runtime (for FileSystemObject)
  • Microsoft ADO (for PopulateTableList)

Document generated by BMAD Document Project Workflow