Re: Skip a line of code and continue

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

a sub and a function are basically the same, as in how you call them and how
they run. the differences are 1) a function can return a value to the
calling code - or not, it doesn't have to - but a sub cannot return a value;
2) a public function can be called outside of VBA; think of using Date(), or
IIf() - those are built-in functions, but the custom functions you write are
functions just like those, and can be called from a toolbar or menubar
option, from within a query, or from a property event line in the Properties
box in a form, and, of course, from other VBA routines - but a sub can only
be called from within a VBA routine (another sub, or a function).

if you'll take another look at the code i posted, it actually DOES call the
isMissingData function from the form's BeforeUpdate event procedure. see the
code fragment below, as

Private Sub Form_BeforeUpdate(Cancel As Integer)

If isMissingData Then

if you were to paste the complete code into your form, and then step through
it, you'd see that when that line executes, it skips to the function and
executes it, then skips back to that line and evaluates the function return
value as True or False, and then executes the remaining lines of code, or
not, based on that evaluation.

i'm not sure i'm getting to the heart of your question, but functions and
subs are just that simple to call from another routine. you can use the Call
statement, but you don't need to; and, according to A2003 VBA Help, if you
use the Call statement to call a function, then the function's return value
is discarded. i don't think i've ever used Call in working code, so i can't
say if that's true or not.

hth


"Bob Waggoner" <BobWaggoner@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:67F6A1AF-DA77-4165-A4C3-7B46FEA7DB14@xxxxxxxxxxxxxxxx
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" <BobWaggoner@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0FFD6E61-E532-49E4-99C0-50D191219543@xxxxxxxxxxxxxxxx
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" <BobWaggoner@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:6E784FBB-4A66-4E6F-82A7-857A7EEF8846@xxxxxxxxxxxxxxxx
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?")
If intanswerEmployeeName = vbYes Then

[This is the missing code....]

End If
If intanswerEmployeeName = vbNo Then
MsgBox "Advisory - Please enter Employee Name.",
vbQuestion,
"You forgot to enter the Employee Name"
DoCmd.GoToControl "EmployeeName"
Exit Sub
End If

End If

If you can help, I'd appreciate it. Thanks.









.

Quantcast