Error Handling

[jsanti1975on]

We need to modify the tenant IDs so that all IDs are represented as two digits. If the tenant ID is a single digit (1-9), it should be prepended with a zero (e.g., 01, 02, ..., 09) to ensure consistent formatting and avoid potential issues during data handling and processing.

[jsanti1975on]

Title

Ensure Tenant IDs are Two Digits

Description

We need to modify the tenant IDs so that all IDs are represented as two digits. If the tenant ID is a single digit (1-9), it should be prepended with a zero (e.g., 01, 02, ..., 09) to ensure consistent formatting and avoid potential issues during data handling and processing.

Steps to Implement

1. Identify tenant IDs that are single digits (1-9).
2. For each identified tenant ID, prepend a zero to make it two digits (e.g., change "1" to "01", "2" to "02", etc.).
3. Update the tenant ID storage or representation to reflect the modified two-digit format.

Expected Outcome

All tenant IDs within the system will be consistently formatted as two-digit numbers, improving data consistency and facilitating data operations.

Additional Notes

• Ensure that the modifications are applied uniformly across all relevant data storage or representation.
• Test the changes thoroughly to verify that the two-digit formatting is correctly implemented and does not introduce any unintended side effects.

[jsanti1975on]

Private Sub CommandButton2_Click() ' Search for TenantID# and display image Dim searchID As String Dim foundRow As Range Dim imgPath As String

' Validate TenantID# input
searchID = Trim(TextBox3.Value)
If searchID = "" Then
    MsgBox "Please enter TenantID# to search.", vbExclamation
    Exit Sub
End If

' Format the TenantID# to ensure it's two digits
If Len(searchID) = 1 And IsNumeric(searchID) Then
    ' Prepend a "0" to the single-digit TenantID#
    searchID = "0" & searchID
End If

' Search for TenantID# in Sheet1 column C
Set foundRow = ThisWorkbook.Sheets("Sheet1").Columns("C").Find(What:=searchID, LookIn:=xlValues, LookAt:=xlWhole)

' Construct image path based on TenantID#
If Not foundRow Is Nothing Then
    imgPath = "Z:\SharesHome\" & searchID & ".jpg"

    ' Check if image file exists
    If Dir(imgPath) <> "" Then
        ' Load and display image in Image1
        Image1.Picture = LoadPicture(imgPath)
    Else
        MsgBox "Image not found for this TenantID#.", vbExclamation

        ' Clear all text boxes
        ClearTextBoxes
    End If
Else
    MsgBox "TenantID# not found.", vbExclamation
End If

End Sub

Private Sub ClearTextBoxes() ' Clear all input text boxes TextBox1.Value = "" TextBox2.Value = "" TextBox3.Value = "" txtFLNumber.Value = "" txtPhone0.Value = "" txtPhone1.Value = "" txtEmail0.Value = "" Image1.Picture = LoadPicture("") ' Clear image End Sub

[jsanti1975on]

Private Sub TextBox3_AfterUpdate() On Error GoTo ErrorHandler

' Retrieve data based on entered TenantID#
Dim searchID As String
Dim foundRow As Range
Dim ws As Worksheet
Dim lastRow As Long

' Validate TenantID# input
searchID = Trim(TextBox3.Value)
If searchID = "" Then
    MsgBox "TenantID# cannot be empty. Please enter a valid TenantID#.", vbExclamation
    Exit Sub
End If

' Set worksheet object
Set ws = ThisWorkbook.Sheets("Sheet1")

' Search for TenantID# in Sheet1 column C
Set foundRow = ws.Columns("C").Find(What:=searchID, LookIn:=xlValues, LookAt:=xlWhole)

' If TenantID# is found, populate other text boxes with corresponding data
If Not foundRow Is Nothing Then
    ' Get row number of the found record
    lastRow = foundRow.Row

    ' Populate text boxes with data from corresponding columns
    TextBox1.Value = ws.Cells(lastRow, "A").Value  ' Name
    TextBox2.Value = ws.Cells(lastRow, "B").Value  ' SlipSize
    txtFLNumber.Value = ws.Cells(lastRow, "E").Value  ' FLNumber
    txtPhone0.Value = ws.Cells(lastRow, "F").Value  ' Phone0
    txtPhone1.Value = ws.Cells(lastRow, "G").Value  ' Phone1
    txtEmail0.Value = ws.Cells(lastRow, "H").Value  ' Email0

    ' Optional: Display image associated with the TenantID# (if available)
    Dim imgPath As String
    imgPath = "C:/Users/santj/ID/" & searchID & ".jpg"
    If Dir(imgPath) <> "" Then
        Image1.Picture = LoadPicture(imgPath)
    Else
        Image1.Picture = LoadPicture("")  ' Clear image if not found
    End If
Else
    ' Clear text boxes if TenantID# is not found
    TextBox1.Value = ""
    TextBox2.Value = ""
    txtFLNumber.Value = ""
    txtPhone0.Value = ""
    txtPhone1.Value = ""
    txtEmail0.Value = ""
    Image1.Picture = LoadPicture("")  ' Clear image
End If

Exit Sub ' Exit subroutine if no error occurs

ErrorHandler: MsgBox "An error occurred: " & Err.Description, vbExclamation End Sub

[jsanti1975on]

Above is a template for error handling of the below block of code. ' Validate TenantID# input searchID = Trim(TextBox3.Value) If searchID = "" Then Exit Sub End If

[jsanti1975on]

We could also create a subroutine to handle invalid Tenant IDs. This may involve writing more code but will provide a valuable learning experience.

`Private Sub ShowTenantIDFormatMessage() ' Define the format for the TenantID based on Slip# and Generation# Dim slipNumber As String Dim generationNumber As String Dim tenantIDFormat As String

slipNumber = "05" ' Example Slip Number
generationNumber = "01" ' Example Generation Number

' Construct the TenantID format message
tenantIDFormat = "TenantID Format: (e.g Slip#Generation#)" & vbCrLf & _
                 "We are using Generation " & generationNumber & ", Therefore TenantID example is " & slipNumber & generationNumber

' Display the message box with the TenantID format information
MsgBox tenantIDFormat, vbInformation, "TenantID Format"

End Sub `

[jsanti1975on]

Below is working and can be used a framework for the other text boxes. The Sub id has been omitted slipNumber = "05" ' Example Slip Number generationNumber = "01" ' Example Generation Number

' Construct the TenantID format message tenantIDFormat = "TenantID Format: (e.g Slip#Generation#)" & vbCrLf & _ "We are using Generation " & generationNumber & ", Therefore TenantID example is " & slipNumber & generationNumber

' Display the message box with the TenantID format information MsgBox tenantIDFormat, vbInformation, "TenantID Format"

[jsanti1975on]

Error Handling for Text Boxes 2 and 3

In this code snippet, error handling has been implemented for Text Boxes 2 and 3. The 'TenantID' (Text Box 3) is required to have the same first two digits as Text Box 2. If this condition is not met, incorrect values might be saved. The error is detected and handled, as shown in the accompanying image displaying a message box.

The message box might require rewording for clarity, but the error has been effectively addressed in the code.

Option Explicit

' Function to validate TenantID format and match with SlipNumber Private Function IsValidTenantID(slipNumber As String, tenantID As String) As Boolean ' Check if TenantID matches the specified format IsValidTenantID = False

' Validate length
If Len(tenantID) <> 4 Then Exit Function

' Validate first two digits
Dim firstTwoDigits As String
firstTwoDigits = Left(tenantID, 2)
If Not IsNumeric(firstTwoDigits) Then Exit Function
If Not (firstTwoDigits >= "01" And firstTwoDigits <= "80") Then Exit Function

' Validate that the first two digits of TenantID match the SlipNumber
If firstTwoDigits <> slipNumber Then Exit Function

' Validate last two digits
Dim lastTwoDigits As String
lastTwoDigits = Right(tenantID, 2)
If Not IsNumeric(lastTwoDigits) Then Exit Function
If lastTwoDigits <> "01" Then Exit Function

IsValidTenantID = True

End Function

' Event handler for Save button click Private Sub CommandButton1_Click() ' Save data to worksheet Dim ws As Worksheet Dim lastRow As Long Dim slipNumberValue As String Dim tenantIDValue As String Dim existingRow As Range

' Set worksheet object
Set ws = ThisWorkbook.Sheets("Sheet1")

' Validate required input fields
If Trim(TextBox1.Value) = "" Or Trim(TextBox2.Value) = "" Or Trim(TextBox3.Value) = "" Then
    MsgBox "Please enter Name, Slip Number, and TenantID#.", vbExclamation
    Exit Sub
End If

' Get SlipNumber and TenantID values from text boxes
slipNumberValue = Trim(TextBox2.Value)
tenantIDValue = Trim(TextBox3.Value)

' Validate SlipNumber
If Val(slipNumberValue) > 80 Then
    MsgBox "Error Code 1: This application is based on 80 slips. Please enter a Slip Number between 1 and 80.", vbExclamation
    Exit Sub
End If

' Validate TenantID format and match with SlipNumber
If Not IsValidTenantID(slipNumberValue, tenantIDValue) Then
    MsgBox "TenantID# should be in the format Slip#01 and match the Slip Number.", vbExclamation
    Exit Sub
End If

' Check for duplicate SlipNumber in Column B
Set existingRow = ws.Columns("B").Find(What:=slipNumberValue, LookIn:=xlValues, LookAt:=xlWhole)

' If duplicate SlipNumber is found, display message and exit sub
If Not existingRow Is Nothing Then
    MsgBox "Duplicate Slip Number found. Please enter a different Slip Number.", vbExclamation
    Exit Sub
End If

' Find the next available row in column A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1

' Write data to worksheet
With ws
    .Cells(lastRow, "A").Value = Trim(TextBox1.Value)     ' Name
    .Cells(lastRow, "B").Value = slipNumberValue          ' SlipNumber
    .Cells(lastRow, "C").Value = tenantIDValue            ' TenantID#
    .Cells(lastRow, "E").Value = Trim(txtFLNumber.Value)  ' FLNumber
    .Cells(lastRow, "F").Value = Trim(txtPhone0.Value)    ' Phone0
    .Cells(lastRow, "G").Value = Trim(txtPhone1.Value)    ' Phone1
    .Cells(lastRow, "H").Value = Trim(txtEmail0.Value)    ' Email0
End With

' Clear input controls after saving
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
txtFLNumber.Value = ""
txtPhone0.Value = ""
txtPhone1.Value = ""
txtEmail0.Value = ""

' Clear image in Image1
Image1.Picture = LoadPicture("")

' Display success message
MsgBox "Data saved successfully.", vbInformation

End Sub

Private Sub CommandButton2_Click() ' Search for TenantID# and display image Dim searchID As String Dim foundRow As Range Dim imgPath As String

' Validate TenantID# input
searchID = Trim(TextBox3.Value)
If searchID = "" Then
    MsgBox "Please enter TenantID# to search.", vbExclamation
    Exit Sub
End If

' Search for TenantID# in Sheet1 column C
Set foundRow = ThisWorkbook.Sheets("Sheet1").Columns("C").Find(What:=searchID, LookIn:=xlValues, LookAt:=xlWhole)

' Construct image path based on TenantID#
If Not foundRow Is Nothing Then
    imgPath = "C:/Users/santj/ID/" & searchID & ".jpg"

    ' Check if image file exists
    If Dir(imgPath) <> "" Then
        ' Load and display image in Image1
        Image1.Picture = LoadPicture(imgPath)
    Else
        MsgBox "Image not found for this TenantID#.", vbExclamation

        ' Clear all text boxes
        TextBox1.Value = ""
        TextBox2.Value = ""
        TextBox3.Value = ""
        txtFLNumber.Value = ""
        txtPhone0.Value = ""
        txtPhone1.Value = ""
        txtEmail0.Value = ""
    End If
Else
    MsgBox "TenantID# not found.", vbExclamation
End If

End Sub

Private Sub CommandButton3_Click() ' Browse and load image into Image1 Dim imgPath As Variant

' Set the initial directory path
Dim initialPath As String
initialPath = "C:/Users/santj/ID/"

' Open file dialog to select an image file
imgPath = Application.GetOpenFilename("Images(*.jpg; *.jpeg;), *.jpg; *.jpeg; ", , "Select an Image", initialPath)

' Load and display selected image in Image1 if a file is selected
If imgPath <> False Then
    Image1.Picture = LoadPicture(imgPath)
End If

End Sub

Private Sub TextBox3_AfterUpdate() ' Retrieve data based on entered TenantID# Dim searchID As String Dim foundRow As Range Dim ws As Worksheet Dim lastRow As Long

' Validate TenantID# input
searchID = Trim(TextBox3.Value)
If searchID = "" Then
    Exit Sub
End If

' Check if searchID is valid
If Not IsValidTenantID(Left(searchID, 2), searchID) Then
    Call ShowTenantIDFormatMessage
    Exit Sub
End If

' Set worksheet object
Set ws = ThisWorkbook.Sheets("Sheet1")

' Search for TenantID# in Sheet1 column C
Set foundRow = ws.Columns("C").Find(What:=searchID, LookIn:=xlValues, LookAt:=xlWhole)

' If TenantID# is found, populate other text boxes with corresponding data
If Not foundRow Is Nothing Then
    ' Get row number of the found record
    lastRow = foundRow.Row

    ' Populate text boxes with data from corresponding columns
    TextBox1.Value = ws.Cells(lastRow, "A").Value  ' Name
    TextBox2.Value = ws.Cells(lastRow, "B").Value  ' SlipSize
    txtFLNumber.Value = ws.Cells(lastRow, "E").Value  ' FLNumber
    txtPhone0.Value = ws.Cells(lastRow, "F").Value  ' Phone0
    txtPhone1.Value = ws.Cells(lastRow, "G").Value  ' Phone1
    txtEmail0.Value = ws.Cells(lastRow, "H").Value  ' Email0

    ' Optional: Display image associated with the TenantID# (if available)
    Dim imgPath As String
    imgPath = "C:/Users/santj/ID/" & searchID & ".jpg"

    On Error Resume Next
    If Dir(imgPath) <> "" Then
        Image1.Picture = LoadPicture(imgPath)
    Else
        Image1.Picture = LoadPicture("")  ' Clear image if not found
    End If
    On Error GoTo 0
Else
    ' Clear text boxes if TenantID# is not found
    TextBox1.Value = ""
    TextBox2.Value = ""
    txtFLNumber.Value = ""
    txtPhone0.Value = ""
    txtPhone1.Value = ""
    txtEmail0.Value = ""
    Image1.Picture = LoadPicture("")  ' Clear image
End If

End Sub

Private Sub ShowTenantIDFormatMessage() ' Define the message to display when the TenantID format is invalid MsgBox "Invalid TenantID format. TenantID should be in the format Slip#01 and match the Slip Number.", vbExclamation, "Invalid TenantID" End Sub