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

Functions to Check if something exists or is Loaded

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. One recurring task is to find if a form exists, or a report exists or if the form or report is loaded. Here are a suite of Microsoft Access functions that will help you check for the existance of forms and reports. There are five functions.

  • Check if a form is loaded
  • Check if a table exists
  • Does a report exist
  • Does a field exist in a table
  • Does an external file exist

Check if a Form is Loaded

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 form called frmCustomer for example you can check if it is loaded by using:

If funIsLoadedForm("frmCustomer") = True 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
Const conObjStateClosed = 0
Const conDesignView = 0

On Error GoTo Error_funIsLoadedForm

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." & vbCrLf & _
    "Please write down the following details:" & vbCrLf & vbCrLf & _
    "Module Name: modGeneric" & vbCrLf & _
    "Type: Module" & vbCrLf & _
    "Calling Procedure: funIsLoadedForm" & vbCrLf & _
    "Error Number: " & Err.Number & vbCrLf & _
    "Error Description: " & Err.Description

     Resume Exit_funIsLoadedForm
     Resume

End Function


Check if a Table exists

This uses a different approach. It loops through each table to see if it can find the table you are looking for. To find a table you would use:

If funTableexists("tblCustomers")= True Then
    Do something

'---------------------------------------------------------------------------------------
' Procedure : funTableExists
' Author : Neville Turbit
' Date : 04/06/09
' Purpose : Check if the table is already in this Database
'---------------------------------------------------------------------------------------
'

Public Function funTableExists(strTblName As String) As Boolean

Dim dbs As Database
Dim tbl As TableDef
Dim dbsExist As Object

On Error GoTo Error_funTableExists

    funTableExists = False
    Set dbs = CurrentDb
    Set dbsExist = dbs.TableDefs

'--------------------------------------------------------------
' Search for AccessObject objects in AllTables collection.

    For Each tbl In dbsExist
        If tbl.Name = strTblName Then
            funTableExists = True ' Set the function to true
            GoTo Exit_funTableExists ' Quit if true
        End If
    Next tbl

Exit_funTableExists:
    On Error GoTo 0
    Set dbsExist = Nothing ' Clean up
    Exit Function

Error_funTableExists:

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

    Resume Exit_funTableExists
    Resume

End Function

Does a Report Exist

This function checks if the report exists.

Say the report name is rptDetails. You can use this function in the following manner.

If funReportControlExists("rptDetails") = True Then
    Do Something
End If

'---------------------------------------------------------------------------------------
' Procedure : funReportControlExists
' Author : Neville Turbit
' Date : 04/06/09
' Purpose : Loops through the controls in a report to search for the existance of a control with that name
'---------------------------------------------------------------------------------------
'

Function funReportControlExists(strFormName As String, strControlName As String) As Boolean
Dim ctlCtrl As Control

On Error GoTo Error_funReportControlExists

    funReportControlExists = False ' Set the default to no control exists

'--------------------------------------------------------------
' Loop through the controls

    For Each ctlCtrl In Reports(strFormName).Controls
        If ctlCtrl.Name = strControlName Then ' Found the control
            funReportControlExists = True ' Function is true
            Exit Function ' Exit
        End If
    Next

Exit_funReportControlExists:
    On Error GoTo 0
    Exit Function

Error_funReportControlExists:

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

    Resume Exit_funReportControlExists
    Resume

End Function

Does a Field Exist

This function checks if a field exists in a table. It is useful for checking if a particular table change has taken place. For example if you have to add a new field to a table, you can run this when the application opens to check if that field has been added to this particular Access database.

Once again, use something like:

If funFieldExists("txtFirstName", "tblCustomers") then
           do something

'---------------------------------------------------------------------------------------
' Procedure : funFieldExists
' Author : Neville Turbit
' Date : 04/06/09
' Purpose : Check a field exists in a table
'---------------------------------------------------------------------------------------
'

Function funFieldExists(ByVal strFieldName As String, ByVal strTableName As String) As Boolean
Dim dbs As Database
Dim tbl As TableDef
Dim fld As Field

On Error GoTo Error_funFieldExists

    funFieldExists = False ' Default is false

    Set dbs = CurrentDb
    Set tbl = dbs.TableDefs(strTableName)

'--------------------------------------------------------------
' Check each field in the table

    For Each fld In tbl.Fields
        If fld.Name = strFieldName Then
            funFieldExists = True ' Found so set to true
Exit For
End If
Next

Exit_funFieldExists:
On Error GoTo 0
Exit Function

Error_funFieldExists:

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

Resume Exit_funFieldExists
End Function

Does an External File Exists

Sometimes you need to check if an external file exists. Perhaps you are creating output to Excel and want to check if the Excel file already exists before you overright it.

Use the following to see if C:\My Documents\output.xls exists.

If funFileExists("C:\My Documents\output.xls") Then
      do something

'---------------------------------------------------------------------------------------
' Procedure : funFileExists
' Author : Neville Turbit
' Date : 09/06/09
' Purpose : Check if an external file exists
'---------------------------------------------------------------------------------------
'

Public Function funFileExists(strPath As Variant, Optional lngType As Long) As Boolean
Dim intTest As Integer

On Error Resume Next 'Ignore errors to allow for error evaluation

    intTest = GetAttr(strPath)

'Check if error exists and set response appropriately
    Select Case Err.Number
        Case Is = 0
            funFileExists = True
        Case Else
            funFileExists = False
    End Select

Exit_funFileExists:
    On Error GoTo 0
    Exit Function

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