'---------------------------------------------------------------------------------------
' Procedure : printAllFormNames
' Author : Wyatt Castaneda
' Date : 10/19/2018
' Purpose : This function is called to debug all form names
' Params : none
' Returns : none
' Test : none
'---------------------------------------------------------------------------------------
Public Function printAllFormNames()
Dim db As Object
Set db = Application.CurrentProject
Dim frm As AccessObject
For Each frm In db.AllForms
Debug.Print frm.name
Next frm
End Function
'---------------------------------------------------------------------------------------
' Procedure : closeForm
' Author : Wyatt Castaneda
' Date : 10/19/2018
' Purpose : This function is called to close a form
' Params : none
' Returns : none
' Test : none
'---------------------------------------------------------------------------------------
Public Function closeForm(formName As String)
DoCmd.Close acForm, formName
End Function
'---------------------------------------------------------------------------------------
' Procedure : openForm
' Author : Wyatt Castaneda
' Date : 10/19/2018
' Purpose : This function is called to open a form
' Params : formName as string
' : openArgs as string - OPTIONAL
' : formFilter as string - OPTIONAL
' Returns : none
' Test : none
'---------------------------------------------------------------------------------------
Public Function openForm(formName As String, Optional openArgs As String = "", Optional formFilter As String = "")
If formFilter = "" Then
DoCmd.openForm formName, acNormal, , , , , openArgs
Else
DoCmd.openForm formName, acNormal, , formFilter, , , openArgs
End If
End Function
'---------------------------------------------------------------------------------------
' Procedure : setFormOrderBy
' Author : Wyatt Castaneda
' Date : 10/19/2018
' Purpose : This function is sets a forms order by property and toggles between DESC and ASC
' Params : formName as Form object
' : orderByField As string
' Returns : none
' Test : none
'---------------------------------------------------------------------------------------
Public Function setFormOrderBy(formName As Form, orderByField As String)
If [formName].OrderBy = [orderByField] & " ASC" Then
[formName].OrderBy = [orderByField] & " DESC"
[formName].OrderByOn = True
ElseIf ([formName].OrderBy = [orderByField] & " DESC") Then
[formName].OrderBy = [orderByField] & " ASC"
[formName].OrderByOn = True
Else
[formName].OrderBy = [orderByField] & " DESC"
[formName].OrderByOn = True
End If
End Function
Option Compare Database
Option Explicit
'---------------------------------------------------------------------------------------
' Procedure : properCase
' Author : Wyatt Castaneda
' Date : 10/19/2018
' Purpose : This function proper cases a string
' Params : toFix as string
' Returns : string
' Test : none
'---------------------------------------------------------------------------------------
Public Function properCase(toFix As String) As String
On Error GoTo failGracefully
properCase = StrConv(toFix, vbProperCase)
Exit Function
failGracefully:
properCase = toFix
Exit Function
End Function
'---------------------------------------------------------------------------------------
' Procedure : upperCase
' Author : Wyatt Castaneda
' Date : 10/19/2018
' Purpose : This function uppercases a string
' Params : toFix as string
' Returns : string
' Test : none
'---------------------------------------------------------------------------------------
Public Function upperCase(toFix As String) As String
On Error GoTo failGracefully
upperCase = StrConv(toFix, vbUpperCase)
Exit Function
failGracefully:
upperCase = toFix
Exit Function
End Function
'---------------------------------------------------------------------------------------
' Procedure : lowerCase
' Author : Wyatt Castaneda
' Date : 10/19/2018
' Purpose : This function lowercases a string
' Params : toFix as string
' Returns : string
' Test : none
'---------------------------------------------------------------------------------------
Public Function lowerCase(toFix As String) As String
On Error GoTo failGracefully
lowerCase = StrConv(toFix, vbLowerCase)
Exit Function
failGracefully:
lowerCase = toFix
Exit Function
End Function
'---------------------------------------------------------------------------------------
' Procedure : ucFirst
' Author : Wyatt Castaneda
' Date : 10/19/2018
' Purpose : This function uppercases the first letter of a string
' Params : toFix as string
' Returns : string
' Test : none
'---------------------------------------------------------------------------------------
Public Function ucFirst(toFix As String) As String
On Error GoTo failGracefully
ucFirst = StrConv(Left(toFix, 1), vbUpperCase) & Right(toFix, Len(toFix) - 1)
Exit Function
failGracefully:
ucFirst = toFix
Exit Function
End Function
'---------------------------------------------------------------------------------------
' Procedure : lcFirst
' Author : Wyatt Castaneda
' Date : 10/19/2018
' Purpose : This function lowercases the first letter of a string
' Params : toFix as string
' Returns : string
' Test : none
'---------------------------------------------------------------------------------------
Public Function lcFirst(toFix As String) As String
On Error GoTo failGracefully
lcFirst = StrConv(Left(toFix, 1), vbLowerCase) & Right(toFix, Len(toFix) - 1)
Exit Function
failGracefully:
lcFirst = toFix
Exit Function
End Function
'---------------------------------------------------------------------------------------
' Procedure : escapeSQL
' Author : Wyatt Castaneda
' Date : 10/19/2018
' Purpose : This function escapes a string for use in an SQL statement
' Params : originalString as string
' : deleteSingleQuotes as boolean - OPTIONAL
' Returns : string
' Test : none
'---------------------------------------------------------------------------------------
Public Function escapeSQL(originalString As String, Optional deleteSingleQuotes As Boolean = True) As String
On Error GoTo failGracefully
If IsNull(originalString) Then
escapeSQL = ""
Exit Function
End If
If deleteSingleQuotes = True Then
originalString = Replace(originalString, "'", "")
originalString = Replace(originalString, ";", "")
escapeSQL = originalString
Exit Function
Else
originalString = Replace(originalString, "'", "''")
originalString = Replace(originalString, ";", "")
escapeSQL = originalString
Exit Function
End If
failGracefully:
escapeSQL = originalString
Exit Function
End Function
'---------------------------------------------------------------------------------------
' Procedure : stripPhoneNumber
' Author : Wyatt Castaneda
' Date : 10/19/2018
' Purpose : This function strips the extra characters from a phone number
' Params : phoneNumber as string
' Returns : string
' Test : none
'---------------------------------------------------------------------------------------
Public Function stripPhoneNumber(phoneNumber As String) As String
On Error GoTo failGracefully
If (IsNull(phoneNumber) Or IsEmpty(phoneNumber)) Then
stripPhoneNumber = ""
Exit Function
End If
phoneNumber = Replace(phoneNumber, "(", "")
phoneNumber = Replace(phoneNumber, ")", "")
phoneNumber = Replace(phoneNumber, "-", "")
phoneNumber = Replace(phoneNumber, " ", "")
stripPhoneNumber = phoneNumber
Exit Function
failGracefully:
stripPhoneNumber = phoneNumber
Exit Function
End Function
'---------------------------------------------------------------------------------------
' Procedure : formatPhoneNumber
' Author : Wyatt Castaneda
' Date : 10/19/2018
' Purpose : This function adds the proper formatting to a plain phone number
' Params : phoneNumber as string
' Returns : string
' Test : none
'---------------------------------------------------------------------------------------
Public Function formatPhoneNumber(phoneNumber As String) As String
On Error GoTo failGracefully
phoneNumber = stripPhoneNumber(phoneNumber)
formatPhoneNumber = "(" & Left(phoneNumber, 3) & ") " & Right(Left(phoneNumber, 6), 3) & "-" & Right(phoneNumber, 4)
Exit Function
failGracefully:
phoneNumber = phoneNumber
Exit Function
End Function
'---------------------------------------------------------------------------------------
' Procedure : stripSSN
' Author : Wyatt Castaneda
' Date : 10/19/2018
' Purpose : This function strips the dashes from SSN strings
' Params : toFix as string
' Returns : string
' Test : none
'---------------------------------------------------------------------------------------
Public Function stripSSN(toFix As String) As String
On Error GoTo failGracefully
toFix = Replace(toFix, "-", "")
toFix = Replace(toFix, " ", "")
stripSSN = toFix
Exit Function
failGracefully:
stripSSN = toFix
Exit Function
End Function
'---------------------------------------------------------------------------------------
' Procedure : formatSSN
' Author : Wyatt Castaneda
' Date : 10/19/2018
' Purpose : This function adds dashes to a plain SSN
' Params : SSN as string
' Returns : string
' Test : none
'---------------------------------------------------------------------------------------
Public Function formatSSN(SSN As String) As String
On Error GoTo failGracefully
SSN = stripSSN(SSN)
formatSSN = Left(SSN, 3) & "-" & Right(Left(SSN, 5), 2) & "-" & Right(SSN, 4)
Exit Function
failGracefully:
formatSSN = SSN
Exit Function
End Function
'---------------------------------------------------------------------------------------
' Procedure : notNull
' Author : Wyatt Castaneda
' Date : 10/19/2018
' Purpose : This function checks if a value is null
' Params : value as variant
' Returns : bool
' Test : none
'---------------------------------------------------------------------------------------
Public Function notNull(value As Variant) As Boolean
On Error GoTo failGracefully
If (Not IsNull(value) And value <> "") Then
notNull = True
Else
notNull = False
End If
Exit Function
failGracefully:
notNull = False
Exit Function
End Function
'---------------------------------------------------------------------------------------
' Procedure : contains
' Author : Wyatt Castaneda
' Date : 10/19/2018
' Purpose : This function checks if given strings occurs in a base string
' Params : toCheck as string
' : searchTerms as paramArray
' Returns : bool
' Test : none
'---------------------------------------------------------------------------------------
Public Function contains(toCheck As String, ParamArray searchTerms()) As Boolean
Dim term As Variant
contains = False
For Each term In searchTerms
If InStr(toCheck, term) <> 0 Then
GoTo doesContainString
End If
Next
Exit Function
doesContainString:
contains = True
Exit Function
End Function
'---------------------------------------------------------------------------------------
' Procedure : interpolate
' Author : Wyatt Castaneda
' Date : 10/19/2018
' Purpose : Interpolates given values into a given string based on indentifiers.
' The indentifiers is a increasing interger wrapped in colons, example :1: or :2:
' Params : base as string
' : terms as paramArray
' Returns : string
' Test : none
'---------------------------------------------------------------------------------------
Public Function interpolate(base As String, ParamArray terms()) As String
Dim term, currentNumber, currentIdentifier, returnString As Variant
currentNumber = 1
returnString = base
For Each term In terms
currentIdentifier = ":" & CStr(currentNumber) & ":"
returnString = Replace(returnString, currentIdentifier, term)
currentNumber = currentNumber + 1
Next
interpolate = returnString
End Function
'---------------------------------------------------------------------------------------
' Procedure : truncate
' Author : Wyatt Castaneda
' Date : 10/19/2018
' Purpose : Truncates a string to the desired length, and optionally adds a padding
' : string at the end. Note padding string is not included in length
' Params : originalStr as string
' : length as integer
' : paddingString as string - OPTIONAL
' Returns : string
' Test : none
'---------------------------------------------------------------------------------------
Public Function truncate(originalStr As String, length As Integer, Optional paddingString = "") As String
truncate = Trim(Left(originalStr, length)) & paddingString
End Function