Access Tip – Check if something exists or is Loaded

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