Skip a line of code and continue

well, you're very welcome. it took some preserverance on both our parts, but
we got there.

note: AFAIK, the only difference between a function in a standard module vs
a form (or report) module is one of scope. a public function in a standard
module can be "seen" and called from anywhere in the db at any time. a
private function in a form/report module is available only to the object the
module belongs to; a public function in a form/report module is available to
be called from anywhere in the db, but only while the "owner" object is open
(i'm fairly sure this is correct, if not hopefully somebody will set me
straight). the problem you were having was not with the function per se, but
with the Me keyword. in context, Me refers to the "owner" object of a
module - but a standard module has no "owner", so you can't use the Me
syntax in a standard module. the code i gave you could easily run from a
standard module, if it were modified to remove the Me keyword and replace it
with a complete form reference.

hth


"Bob Waggoner" wrote in message
...
This response - your patience - has earned my undying gratitude. I didn't
know there was a difference between function in a module and in a form...
THANK YOU. And there're a dozen other things to say thanks for - but the
code
works! Thank you!


"tina" wrote:

comments inline.

"Bob Waggoner" wrote in message
...
Thank you.
Now I get "Invalid use of me keyword."

the error you cited above normally occurs when you use the Me keyword in
a
*standard* module. so if you put the code in a standard module, delete
it,
and paste it into your form's module, as i instructed before.


My real question is: how do I trigger a function (as opposed to a sub
procedure)? How do I call functions?

For example, I can click a control - "Check work" and in the "on
click"
event, call the function. How do I do that?

Private Sub Check_work_Click()

TypeNameOfFunctionHere

End Sub

or, if the function is declared as Public, you can call it directly from
the
Event "line" in control [Check work]'s Properties box, as

=TypeNameOfFunctionHere()

if the function has arguments, include the argument values between the
parentheses.

hth


I know that sometimes functions are called in the properties dialogue
box
event and sometimes in code but I'm confused as how to call them.

Thanks,
Bob

"tina" wrote:

from my first post in this thread, i've copied the function itself,
below.
did you paste this function into your form module? if not, do so
now. do
NOT
put it inside an event procedure, or any other procedure. just
scroll
down
to the bottom of the form module, and paste it below the last line
of
code
in the module. then try compiling your code, again.

hth

Private Function isMissingData() As Boolean

Dim ctl As Control

For Each ctl In Me.Controls
If ctl.Tag = "r" Then
If IsNull(ctl) Then
ctl.BackColor = yello
isMissingData = True
Else
ctl.BackColor = wite
End If
End If
Next

End Function



"Bob Waggoner" wrote in
message
...

Tina,
I get the compile error: variable not defined - then it opens the
form
event
code and highlights "If IsMissingData then"

What did I do wrong?

"Bob Waggoner" wrote:

Thanks for correcting the code and answering my question. I've
been
trying to
take my VBA knowledge to the next level and it's frustrating
trying
to
figure
out how to handle modules. I can convert macros to functions and
then
"gut"
the function and rewrite it to private subs but using a function
without
copying/gutting and pasting it to the form procedure events is
where
I'm
stuck.

Do you know of any on line course/help I can get to teach the
basics
of
using functions / calling functions, and etc?

Thanks again.
Bob

"tina" wrote:

well, i'm glad you posted back, Bob. first, let me fix the
code -
i
left off
the line that closes the If expression, sorry! here's the
corrected
code, as

Private Sub Form_BeforeUpdate(Cancel As Integer)

If isMissingData Then
Cancel = True
MsgBox "Enter the missing information in the " _
& "highlighted fields, please.", vbExclamation, _
"RECORD SUBMISSION CANCELLED"
End If

End Sub

okay, to answer your question: copy the entire function code
(posted
previously) and paste it into the form's module. then, in the
form's
Design
view, in the Properties box, click on the Event tab and find
the
BeforeUpdate event. double click the white space beside the
event
name, it
will fill in automatically with

[Event Procedure]

at the right is a "build" button (...); click the button and
it
will
open
the form module with the cursor inside the newly create event
procedure, as

Private Sub Form_BeforeUpdate(Cancel As Integer)
cursor blinking here, at the left margin
End Sub

where the cursor is blinking, paste in the "guts" of the code
above,
as

If isMissingData Then
Cancel = True
MsgBox "Enter the missing information in the " _
& "highlighted fields, please.", vbExclamation, _
"RECORD SUBMISSION CANCELLED"
End If

so that the complete procedure in your module ends up looking
like
the
first
code i posted above. the code will run every time the form's
BeforeUpdate
event fires; that is, when you add a new record or edit an
existing
record
and then 1) move to another record, or 2) close the form, or
3)
move
from a
mainform into a subform, or vice versa, or 4) explicitly save
the
record
from a menu bar or toolbar option or by running code - from a
command
button, for instance - to save the record.

hth


"Bob Waggoner" wrote
in
message
...
Tina,
Thank you for your help on this. I'm a relative novice - if
you
could help
me in one more thing...how do I call a function? I don't
know
where
or how
to
place this code so that it activates at the right time.

Thanks
Bob

"tina" wrote:

here's some code that i use to highlight required controls
in
a
SingleForm
view, when the data isn't entered, as

Private Function isMissingData() As Boolean

Dim ctl As Control

For Each ctl In Me.Controls
If ctl.Tag = "r" Then
If IsNull(ctl) Then
ctl.BackColor = yello
isMissingData = True
Else
ctl.BackColor = wite
End If
End If
Next

End Function

for each control i want to have evaluated, i enter an "r"
in
the
Tag
property of the control. the "yello" and "wite" variables
are
global
variables that i use throughout my db for consistent
coloring.
you
can
set
variables for the colors you want, or just use the number
values
directly.
in my case, i run the code from a command button that
releases
the
record
from one dept's control to the next dept. but it would
work
equally well
in
a form's BeforeUpdate event procedure, as

Private Sub Form_BeforeUpdate(Cancel As Integer)

If isMissingData Then
Cancel = True
MsgBox "Enter the missing information in the " _
& "highlighted fields, please.",
vbExclamation, _
"RECORD SUBMISSION CANCELLED"
End Sub

hth


"Bob Waggoner"
wrote
in
message
...
I have a "Check Work" button a user can click to see if
they've
completed
a
record.
Can anyone show me a bit of code that simply pops up a
list
of
skipped
items? For example: VendorCode, TypeofComment, Comment,
ContactPerson
are
some of the fields the program checks.

Right now, I have this code evaluating the fields to see
if
they
are
complete:

DoCmd.Echo True, ""
If (IsNull(.EmployeeName)) Then
Beep
MsgBox "Advisory - Please enter the Employee
Name.",
vbInformation, "You Forgot the Employee Name"
DoCmd.GoToControl "WebEmployeeName"
Exit Sub
End If

Instead of having code that notifies the user of each
skipped
box and
then
stops, I'd either like to list the skipped fields or
allow
the
user to
allow
the code to continue checking.

My attempt to allow the user to continue the check goes
like
this:
DoCmd.Echo True, ""
If (IsNull(.[EmployeeName])) Then
Dim intanswerEmployeeName As Integer
intanswerEmployeeName = MsgBox("Continue?",
_
vbQuestion + vbYesNo, "Continue?")