Reusing code to check for blank fields: module / function

[Christine]

I have the following code in all of my forms to check for
blank fields before carrying out an action. (Found it
someplace either here or on the web, can't recall.)In each
form, an action takes place if Cancel = False.

I realise that repeating this code in all of the project's
forms isn't very tidy. Does it belong in a module, a
function? How do you call a module and what code has to be
before and after it? Does "Cancel" have to be declared as
public in each form?

Dim ctl As Control
Dim intMsgResponse 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 control 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.",
vbExclamation, "Information is missing"
End If


Many thanks,
Christine

 

[Paul]

You can cut down the code a bit by creating a public function (in a code
module, not behind a form) that returns True or False.

Public Function CheckForNulls(frm as form) as boolean
Dim ctl As Control
Dim intMsgResponse As Integer
' Loop through all the controls on the form
For Each ctl In frm.Controls
' Check the tag property
If ctl.Tag = "RequiredField" Then
' Check this control has a value
If ctl = "" Or IsNull(ctl) Then
' No Value - Cancel the update event
CheckForNulls = True

'May as well ecit the loop as soon as you have found an
incomplete required field
Exit For

End If
End If
Next
' Check to see if a required field was blank and inform the user
If CheckForNulls = True Then
MsgBox "Please fill in all required fields.", vbExclamation,
"Information is missing"
End If
End Function


In you form code you then enter:
Cancel = CheckForNulls(me.form)




Another alternative is to create a class module and sink events from the
form(s), but that is pretty complex stuff!

 

[Christine]

Thank you, Paul. I created a module called CheckForNulls.
It brings up a compile error that says "Expected variable
or procedure, not module."

Thinking it didn't like the module being called the same
name as the function, I renamed the module to
CheckForNulls Module. With this, I get a compile error
that says "Variable not defined".

Can you help clarify what's going on?

Also, in all of the forms that use this module, if
CheckForNulls = False (all fields have been filled in),
then an email routine kicks in. So, in the forms is it
okay to put the following:
If CheckForNulls = Fale Then
' do some stuff...
Does CheckForNulls need to be declared public someplace?

Many, many thanks,
Christine

 

[Paul]

Please see below

--
Visit my website www.pdtech.co.uk for Access Developer Resources

Thank you, Paul. I created a module called CheckForNulls.
It brings up a compile error that says "Expected variable
or procedure, not module."

Thinking it didn't like the module being called the same
name as the function, I renamed the module to
CheckForNulls Module. With this, I get a compile error
that says "Variable not defined".

Can you help clarify what's going on?

Hmm, check it is a normal module, NOT a class module. If it still does not
work, reply here and I'll take a closer look (the code was off the cuff so I
may have made a typo).

Also, in all of the forms that use this module, if
CheckForNulls = False (all fields have been filled in),
then an email routine kicks in. So, in the forms is it
okay to put the following:
If CheckForNulls = Fale Then
' do some stuff...

Yes this is fine. Try to avoid calling CheckForNulls more than once in any
routine (store the result in a variable if you need) as each time you it, it
will have the overhead of rechecking all the controls again.

Does CheckForNulls need to be declared public someplace?

As long as it is in a normal module (not class or form) then the first line:

Public Function CheckForNulls(frm as form) as boolean

makes the function available throughout your project.

 

[Christine]

Hi Paul, I checked the module and it is definately not a
Class Module. I'm still getting that compile
error "Compile error that says "Expected variable or
procedure, not module."

Ta SO much for your help!
Christine

 

[Paul]

Hi,

I just copied the code from the earlier post into Northwind sample db in
Access 97 and hooked up a button behind which I placed the following code as
a test:

Private Sub Command17_Click()
Dim x As Boolean
x = CheckForNulls(Me.Form)
End Sub

It all compiled and ran fine.

Can you try removing the code and seeing if you still get the error
message - perhaps the problem is actually something else.

 

[Christine]

Hello again, Paul.

I modifying your previous code...
Cancel = CheckForNulls(Me.Form)

to what you said in your last post...
Dim X As Boolean
X = CheckForNulls(Me.Form)

Still getting the same message "Compile error: Expected
variable or procedure, not module."

I tried removing the code, with the same results. I have
verified that CheckForNulls is NOT a class module.

Where to next? :-(

Cheers,
Christine

 

[Paul]

Hi,

If you removed all the CheckForNulls code and still got an error then the
bug must be elsewhere in your code.

If you want to mail me the code I'll take a look. If you send it to

pdof[insert current year in format yyyy] AT yahoo.com

I'm trying to avoid spam!