Project Perfect
PROJECT  PERFECT
                 Project Management Software
                          Specialists in Project Infrastructure		 Microsoft Access Development Tips

Home - Microsoft Access Development - Microsoft Access Tips

Sample Microsoft Access VBA

Simple Functions

The following are some samples of VBA code that may prove useful.

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:
    On Error GoTo 0
Exit Function

Error_funCheck4Nothing:

MsgBox "An unexpected situation arose in your program." & funCrLf & _
    "Please write down the following details:" & funCrLf & funCrLf & _
    "Module Name: modGeneric" & funCrLf & _
    "Type: Module" & funCrLf & _
    "Calling Procedure: funCheck4Nothing" & funCrLf & _
    "Error Number: " & Err.Number & funCrLf & _
    "Error Descritption: " & Err.Description

Resume Exit_funCheck4Nothing
    End Function

Public Function funCrLf()
funCrLf = vbCrLf
End Function

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_funIsLoadedForm

Const conObjStateClosed = 0
Const conDesignView = 0

If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
    If Forms(strFormName).CurrentView <> conDesignView Then
        funIsLoadedForm = True
    End If
End If

Exit_funIsLoadedForm:
    On Error GoTo 0
    Exit Function

Error_funIsLoadedForm:

MsgBox "An unexpected situation arose in your program." & funCrLf & _
    "Please write down the following details:" & funCrLf & funCrLf & _
    "Module Name: modGeneric" & funCrLf & _
    "Type: Module" & funCrLf & _
    "Calling Procedure: funIsLoadedForm" & funCrLf & _
    "Error Number: " & Err.Number & funCrLf & _
    "Error Descritption: " & Err.Description

    Resume Exit_funIsLoadedForm

End Function

Public Function funCrLf()
funCrLf = vbCrLf
End Function

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 Boolean

On Error Resume Next

Dim strParentName As String

strParentName = frm.Parent.Name
funIsSubForm = (Err.Number = 0)

Exit_funIsSubForm:
    On Error GoTo 0
    Exit Function

End Function

Public Function funCrLf()
funCrLf = vbCrLf
End Function

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. If you have any suggestions, put them on our blog for other Access Developers.

Return to the top