Skip a line of code and continue

[Bob Waggoner]

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.

 

[Beetle]

If you just want to present a list of all the controls that
still need to be filled in, you could put a user friendly name
in the Tag property of each of the controls in question, then
use code like the following in Before Update event of the form;

Private Sub Form_BeforeUpdate (Cancel As Integer)

Dim ctl As Control
Dim strMsg As String

strMsg = "Please fill in the following fields;" & vbCrLf & vbCrLf 'length =
40

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If Nz(ctl, "") = "" Then
strMsg = strMsg & ctl.Tag & vbCrLf
End If
End If
Next ctl

If Len(strMsg) > 40 Then
MsgBox strMsg, vbInformation, "Missing Data"
Cancel = True
End If

End Sub


This would present a message box that looks like;

***********************
Missing Data

Please fill in the following fields;

Employee Name
Employee Address
Phone Number
etc
etc
************************

 

[tina]

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]

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

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

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.

 

[tina]

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

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

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

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.

 

[Bob Waggoner]

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

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

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

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


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.

 

[tina]

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

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

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

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

:

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


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.

 

[tina]

to answer your last question, which my previous post didnt: i'm sorry, i
don't know of any courses, online or offline, that cover that particular
topic. you might try googling. if that brings no joy, you might post the
question to the newsgroup microsoft.public.access.modulesdaovba
and see if anybody else has suggestions.

but honestly, i don't think you need a whole course, Bob. seems like this is
one of those "lightbulb" concepts, once the light goes on for you, it'll
make complete sense. i bet if you could sit with somebody for 15 minutes of
demonstration/explanation, you'd get it, and be off and running. i worked my
way up through Access to my current level (nowhere near the top, yet!) from
dirt bottom, with no newgroup or other online resources to help me until i
had already graduated from macros to VBA. i did have the great good fortune
to find a couple local nightschool classes that taught me the basics of
relational design principles and later the very very basic basics of writing
VBA code (this was back around 1999 or 2000). and before that, i remember
reading and re-reading Access Help, and beating my head against a wall, for
two months before i finally grasped the concept of macros in Access - in one
of those lightbulb moments. so i think i can understand your frustration!
<g>

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

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

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

:

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


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.

 

[Bob Waggoner]

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?

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

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

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

:

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


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.

 

[tina]

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

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?

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

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


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

:

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


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.

 

[Bob Waggoner]

Thank you.
Now I get "Invalid use of me keyword."

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?

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

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

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?

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

:

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


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

:

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


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.

 

[tina]

comments inline.

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

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

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?

:

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

:

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


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

:

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" <[email protected]> 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?")
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.

 

[Bob Waggoner]

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!

comments inline.

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

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




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?

:

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

:

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


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

:

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


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?")

 

[tina]

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

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!

comments inline.

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

:

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




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?

:

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

:

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


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

:

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" <[email protected]>

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?")