Access Tip – Generic functions
Microsoft Access developers often use the same code to perform functions in many procedures. Long ago, I came to the conclusion that it was easier to build even simple MS Access functions once and get them right. Turn them into a function, strore them in a module and reuse them.
Check for Blank, Null or Zero
This is probably the one I use most in Microsoft Access VBA. It is only a few lines but has a built in error handling, and I know it works. I never have to think about it, or worry about whether it is a text field or numeric.
If you have a value (say a textbox called txtName) you can check if it is blank by using:
If funCheck4Nothing(Me!txtName) = True then
do something
| ‘————————————————————————————— ‘ Procedure : funCheck4Nothing ‘ Author : Neville Turbit ‘ Date : 04/06/09 ‘ Purpose : Check the value passed is null, zero string (“”) or zero. Returns true if any of these are true. ‘————————————————————————————— ‘ Public Function funCheck4Nothing(var As Variant) On Error GoTo Error_funCheck4Nothing If IsNull(var) Or var = “” Or var = 0 Then funCheck4Nothing = True Else funCheck4Nothing = False End If Exit_funCheck4Nothing: Error_funCheck4Nothing: MsgBox “An unexpected situation arose in your program.” & funCrLf & _ Resume Exit_funCheck4Nothing Public Function funCrLf() |
Check if a Form is Loaded
Another few lines that are useful if you are trying to establish if a form is actually loaded. Say the form is frmSwitchboard, you would use:
If funIsLoadedForm(“frmSwitchboard”) Then
Do something
| ‘————————————————————————————— ‘ Procedure : funIsLoadedForm ‘ Author : Neville Turbit ‘ Date : 04/06/09 ‘ Purpose : Checks if a form is loaded ‘ Returns True if the specified form is open in Form view or Datasheet view. ‘————————————————————————————— ‘ Public Function funIsLoadedForm(ByVal strFormName As String) As Boolean On Error GoTo Error_funIsLoadedFormConst conObjStateClosed = 0 Const conDesignView = 0 If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then Exit_funIsLoadedForm: Error_funIsLoadedForm: MsgBox “An unexpected situation arose in your program.” & funCrLf & _ Resume Exit_funIsLoadedForm End Function Public Function funCrLf() |
Is it an Access Subform
This function checks if the form is a subform. You might use this in a generic routine that does something with forms. For example, the routine might check if a user is permitted to view the form. Since the subform inherits the permissions of the main form, you may not want to check the subform.
Say the subform name is frmDetails. You can use this function in the following manner.
If funIsSubForm(“frmDetails”) = True Then
Do Something
End If
| ‘————————————————————————————— ‘ Procedure : funIsSubForm ‘ Author : Neville Turbit ‘ Date : 04/06/09 ‘ Purpose : Checks to see if a form is a subform. Errors if it cannot find a parent ‘————————————————————————————— ‘ Public Function funIsSubForm(frm As Form) As BooleanOn Error Resume Next Dim strParentName As String strParentName = frm.Parent.Name Exit_funIsSubForm: End Function Public Function funCrLf() |
In all my applications I plug in a generic module with about 30 different functions and subroutines. I probably never use them all in one database, but they are there if I need them. It saves trying to remember how I did it before. Every developer should have a collection in a generic module.