December 9th, 2010
Sometimes it can be handy to be able to identify whether or not a module is in a database or not. One way to check is to loop through the AllModules collection to see if it is there or not. Below is a sample procedure that demonstrates how you can check for the existance of a VBA module within your database.
Determine whether a Module Exists using the AllModules Collection
'---------------------------------------------------------------------------------------
' Procedure : ModuleExist
' Author : CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Determine if a module exists within the database
' Copyright : The following may be altered and reused as you wish so long as the
' copyright notice is left unchanged (including Author, Website and
' Copyright). It may not be sold/resold or reposted on other sites (links
' back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sModuleName - Name of the module you are searching for
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' ModuleExist("Module2")
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2008-Aug-13 Initial Release
'---------------------------------------------------------------------------------------
Function ModuleExist(sModuleName As String) As Boolean
On Error GoTo Error_Handler
Dim mdl As Object
ModuleExist = False 'Initialize our variable
'Loop trhough all the modules in the database
For Each mdl In CurrentProject.AllModules
If mdl.Name = sModuleName Then
ModuleExist = True
Exit For 'No point continuing if found
End If
Next
Error_Handler_Exit:
On Error Resume Next
Set mdl = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: ModuleExist" & vbCrLf & _
"Error Description: " & Err.Description, _
vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function |
Share and Enjoy
MS Access VBA Programming | 
No Comments »
December 6th, 2010
One option is to systematically check for the various dos and don’t using a series of if statements such as that in the function below:
Public Function isValidEmail(inEmailAddress As String) As Boolean
' Author: Unknown
If (Len(inEmailAddress) = 0) Then
MsgBox "Please enter your email address."
isValidEmail = False
Exit Function
End If
If (InStr(1, inEmailAddress, "@") = 0) Then
MsgBox "The '@' is missing from your e-mail address."
isValidEmail = False
Exit Function
End If
If (InStr(1, inEmailAddress, ".") = 0) Then
MsgBox "The '.' is missing from your e-mail address."
isValidEmail = False
Exit Function
End If
If (InStr(inEmailAddress, "@.") > 0) Then
MsgBox "There is nothing between '@' and '.'"
isValidEmail = False
Exit Function
End If
If ((InStr(inEmailAddress, ".")) = ((Len(inEmailAddress)))) Then
MsgBox "There has to be something after the '.'"
isValidEmail = False
Exit Function
End If
If ((Len(inEmailAddress)) < (InStr(inEmailAddress, ".") + 2)) Then
MsgBox "There should be two letters after the '.'"
isValidEmail = False
Exit Function
End If
If (InStr(1, inEmailAddress, "@") = 1) Then
MsgBox "You have to have something before the '@'"
isValidEmail = False
Exit Function
End If
isValidEmail = True
End Function |
A Second better approach, like with many web programming languages, is to use regular expressions to validate certain type of strings. Now Access’ VBA does not allow RegEx persey, but you can very easily access such functionality by adding 2 simple lines of code. You then end up with a total of a three lines of code to validate almost any string, including an e-mail address. For all the details, and a link to where you can get a multitude of the RegEx so you don’t have to reinvent the wheel simply check out my post VBA – Using Regular Expressions (RegEx)
Share and Enjoy
MS Access VBA Programming, MS Excel VBA Programming, MS Word VBA Programming | No Comments »
December 5th, 2010
Below is come sample VBA which illustrates how one can easily loop through all the controls within a form to identify and work with them.
Dim ctl As Control
For Each ctl In Me.Controls
ctl.Name 'Get the name of the control
ctl.Value 'Get or set the value of the control
ctl.Visible = False 'Control the visibility of the control
Next ctl |
Now how can this be put to good use? Well one use for such code would be to setup a Select All, or Select None button for a series of check boxes on a form. Below is what the code could look like for each command button:
'Select All
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox Then
If ctl.Value <> True Then
ctl.Value = True
End If
End If
Next ctl
'Select None
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox Then
If ctl.Value <> False Then
ctl.Value = False
End If
End If
Next ctl |
Lastly, you could easily adapt the general form specific code and transform it into a generic procedure to which you will supply the form name to loop through the control, rather than working with the current form, you could do something along the lines of:
Function YourProcedureName(ControlName As String, frm As Access.Form)
Dim ctl As Access.Control
For Each ctl In frm.Controls
Next ctl
End Function |
Share and Enjoy
MS Access Forms, MS Access VBA Programming | 8 Comments »
