How to repeat code that is used in all forms

  • Thread starter Thread starter Christine
  • Start date Start date
C

Christine

I have the following code in all my forms to check that
the user has completed all necessary form fields. It
occurs to me that this isn't very efficient, but I have
absolutely no idea how to do it, other than thinking it
might have something to do with a Call or Function or Do
command.

Dim ctl As Control
Dim intMsgResponse As Integer
Dim Cancel As Integer
' Loop through all the controls on the form
For Each ctl In Me.Controls
' Check the tag property
If ctl.Tag = "RequiredField" Then
' Check this controls has a value
If ctl = "" Or IsNull(ctl) Then
' No Value - Cancel the update event
Cancel = True
End If
End If
Next
' Check to see if a required field was blank and
inform the user

If Cancel = True Then

MsgBox "Please fill in all required fields and
click Submit.", vbExclamation, "Information is missing"

Else
<Do the commands that are very specific to individual
form>

End If

Can someone give me the code to make my this code work for
all my forms, along with an explanation of what's going on?

Many thanks,
Christine
 
Christine,

I think you can do this by creating a Public Function or Sub in a Module.
You will have to remove all the "Me" language when referring to a form.

Here is a code snippet which shows how to refer to the active form and its
controls from a module, which you can use as an example to modify the code
you already have:

Public Function AllFms()

Dim f As Form
Dim ctl As Control

Set f = Screen.ActiveForm
MsgBox f.Name

For Each ctl In f.Controls
MsgBox ctl.Name
Next ctl

End Function
 
Back
Top