|
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
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
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
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
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
For Each ctlCtrl In Reports(strFormName).Controls
If ctlCtrl.Name = strControlName Then ' Found the control
funReportControlExists = True ' Function is true
Exit Function
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
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)
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
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)
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
|
When you subscribe you can download a free eBook
