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:

'--------------------------------------------------------------
' 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

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

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.

'--------------------------------------------------------------
' 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

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.