Suspend form close

[Guest]

I have a form in which I run a Before Update event that verifies whether
certain fields contain data. If they do not, the user needs to enter values
in those fields or the record will not be saved. My Before Update event is
along the lines of:

If IsNull(Me.txtStartDate) Then
Me.txtStartDate.SetFocus
Call Validate
Cancel = True
ElseIf IsNull (Me.txtDueDate) Then
Me.txtDueDate.SetFocus
Call Validate
etc. as before, through two more controls
End If

My form has four tab controls, each for a different part of the process (and
each typically filled out by a different person). The code I have shown is
for the first tab. After this code is a similar block of code (still in
Before Update) that checks to see if the second tab control is complete, then
the third tab control, then the fourth. Since the first tab control needs to
have all four text boxes filled in for the record to be saved, and since
clicking the second tab control caused the record to be saved (and triggers
the Before Update event), before the code checks the second tab control it
verifies that one of the text boxes on the first tab control is not null (if
one of the text boxes is null they all are), and so forth for the third and
fourth tab controls.

One person starts the form (which is used to make a recommendation). That
person needs to fill in the four text boxes on the first tab control, after
which they typically navigate away from the record. Later, somebody else
enters into text boxes on the second tab control the response to the
recommendation. Again, all fields (3) need to be filled in. Another person
follows up (third tab control) and a fourth issues final approval (fourth
tab). The code checks to see that the first tab control is complete before
checking the second, then that the second tab control is complete before
checking the third, etc. In this way the person starting the recommendation
does not confront the message box because of blanks in Response, Follow Up,
and Final Approval.
Validate is a public procedure that generates a message box advising the
user that they need to complete the record or it will not be saved. This
works as intended when saving the record from within the form (such as when
moving from one tab control to another). However, if the user enters
incomplete information and then closes the form, the Before Update code runs,
but the form closes whether or not the necessary fields have beeen completed.
What I would like to do is to stop the form from closing if the user needs
to go back and fill in missing information.
I have thought of some complicated things that might accomplish this, such
as testing the controls (on Dirty, maybe) as I have done in the Before Update
event. If information is incomplete the Close button would actually be a
Save button, but if the information is complete the faux Close button would
be invisible, and the real Close button would be hiding underneath.
I have gone into some detail about the database structure because in
addition to the specific question I would like a general opinion. As is
stands the database is pretty much a single form with about twenty-five
fields. Recommendation, Response, Follow-up, and Approval are all part of
the same record. I can't think of any reason why each of those sections
should have its own table, but would be interested in hearing thoughts on the
topic. If I am going to change the design, now is the time, before there are
real records.

 

[Graham Mandeno]

Hi Bruce

First the problem of the form closing even though BeforeUpdate has failed -
the easiest way to prevent this is by cancelling the form's Unload event if
the record is dirty:

Private Sub Form_Unload( cancel as integer)
Cancel = Me.Dirty
End Sub

Now, your Close/Save button can do an explicit save before closing the form:

If Me.Dirty then Me.Dirty = False
DoCmd.Close acForm, Me.name

If the BeforeUpdate event is cancelled, then the first line will fail with a
trappable error. At this point you might offer the user the option to undo
changes and continue closing, or fix the problem. If the save is
successful, then the form will no longer be dirty, so the Unload event will
proceed without being cancelled.

On the other question, have you considered keeping a single table, but
having four different forms bound to that table, each showing only the
fields pertinent to that stage of the process?

 

[Guest]

Thankd you for your reply. I would have responded sooner, but I came to
realize I had headed down the wrong track, and have been setting things right
for most of the day. I also continued to try and figure out things on my
own, so that when I ask the next part of the question I am better informed
than I was when I started the question.

Second part of question/answer first: I am using four separate tab
controls, each showing only the fields relevant to that stage in the process.
It is usually necessary for the respondent to see the recommendation, for
the follow-up person to see both the recommendation and the response, and so
forth. Would using separate forms simplify my custom data validation?

I have learned most of what I know about Access from reading and from
newsgroups, and of course by experimentation. As a result there are things I
don't quite understand, but I can't figure out how to learn more about them.
One such item is Cancel = True (or Cancel = Anything, for that matter). If I
try to look it up in a groups search I can find lots of code, but no real
explanation.

I had been doing some unnecessary things with validation. All I really need
to do in most cases is attempt to save the record, triggering the Before
Update event. The Before Update event tests each control. At the first
control for which IsNull is true the focus is set to that control, a message
box is generated, and the next line of code is Cancel = True.

The thing I have done so far that may not work is that the message box that
is generated as described above is a called procedure. I did that because
the same message box can be generated by nulls at four different points in
the code. It works fine as long as I am just clicking OK at the message box,
but I want to give the user the option of just closing out without completing
the section or saving. As I understand it I can't close without saving
(would that be Me.Undo?) from a called procedure.

Could I get around that limitation by defining the message box in Before
Update:
MsgBox1 = msgbox "Prompt",acOkCancel,"Title" (or whatever), and then
referring to MsgBox1 where I am now calling an outside procedure?

A related question is that I am sending e-mails from within the program
(SendObject). Before sending the e-mail I test for fields that are null but
shouldn't be. If everything is OK the e-mail message is generated, otherwise
there is a message box. However, Cancel = True does not prevent the e-mail
from opening, even if there is a null field. I have to add Exit Sub. Why
does the validation test at SendObject seem to behave differently from the
one at BeforeUpdate..

To sum up my question, what is the best way for a user to bail if they
decide not to continue entering a record? One way they would need to do that
is by way of the message box that is generated upon discovery of a null
field. Also, the way this will work is that a record with a few fields will
be saved, then later a few more fields will be added to the same record, and
so on. I expect that Undo (or however the record is prevented from being
saved) will apply only to fields added since the record is reopened. In
other words, after the Recommendation is complete the record is saved. Later
somebody else reopens the record to respond, but decides not to finish. Will
their Undo affect only the fields that have been added or modified since the
record was last saved, or will it affect the entire record? I suspect the
former, but am not sure.

Hi Bruce

First the problem of the form closing even though BeforeUpdate has failed -
the easiest way to prevent this is by cancelling the form's Unload event if
the record is dirty:

Private Sub Form_Unload( cancel as integer)
Cancel = Me.Dirty
End Sub

Now, your Close/Save button can do an explicit save before closing the form:

If Me.Dirty then Me.Dirty = False
DoCmd.Close acForm, Me.name

If the BeforeUpdate event is cancelled, then the first line will fail with a
trappable error. At this point you might offer the user the option to undo
changes and continue closing, or fix the problem. If the save is
successful, then the form will no longer be dirty, so the Unload event will
proceed without being cancelled.

On the other question, have you considered keeping a single table, but
having four different forms bound to that table, each showing only the
fields pertinent to that stage of the process?


--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a form in which I run a Before Update event that verifies whether
certain fields contain data. If they do not, the user needs to enter
values
in those fields or the record will not be saved. My Before Update event
is
along the lines of:

If IsNull(Me.txtStartDate) Then
Me.txtStartDate.SetFocus
Call Validate
Cancel = True
ElseIf IsNull (Me.txtDueDate) Then
Me.txtDueDate.SetFocus
Call Validate
etc. as before, through two more controls
End If

My form has four tab controls, each for a different part of the process
(and
each typically filled out by a different person). The code I have shown
is
for the first tab. After this code is a similar block of code (still in
Before Update) that checks to see if the second tab control is complete,
then
the third tab control, then the fourth. Since the first tab control needs
to
have all four text boxes filled in for the record to be saved, and since
clicking the second tab control caused the record to be saved (and
triggers
the Before Update event), before the code checks the second tab control it
verifies that one of the text boxes on the first tab control is not null
(if
one of the text boxes is null they all are), and so forth for the third
and
fourth tab controls.

One person starts the form (which is used to make a recommendation). That
person needs to fill in the four text boxes on the first tab control,
after
which they typically navigate away from the record. Later, somebody else
enters into text boxes on the second tab control the response to the
recommendation. Again, all fields (3) need to be filled in. Another
person
follows up (third tab control) and a fourth issues final approval (fourth
tab). The code checks to see that the first tab control is complete
before
checking the second, then that the second tab control is complete before
checking the third, etc. In this way the person starting the
recommendation
does not confront the message box because of blanks in Response, Follow
Up,
and Final Approval.
Validate is a public procedure that generates a message box advising the
user that they need to complete the record or it will not be saved. This
works as intended when saving the record from within the form (such as
when
moving from one tab control to another). However, if the user enters
incomplete information and then closes the form, the Before Update code
runs,
but the form closes whether or not the necessary fields have beeen
completed.
What I would like to do is to stop the form from closing if the user needs
to go back and fill in missing information.
I have thought of some complicated things that might accomplish this, such
as testing the controls (on Dirty, maybe) as I have done in the Before
Update
event. If information is incomplete the Close button would actually be a
Save button, but if the information is complete the faux Close button
would
be invisible, and the real Close button would be hiding underneath.
I have gone into some detail about the database structure because in
addition to the specific question I would like a general opinion. As is
stands the database is pretty much a single form with about twenty-five
fields. Recommendation, Response, Follow-up, and Approval are all part of
the same record. I can't think of any reason why each of those sections
should have its own table, but would be interested in hearing thoughts on
the
topic. If I am going to change the design, now is the time, before there
are
real records.

 

[Graham Mandeno]

Hi Bruce

Some thoughts and answers inline...

Thankd you for your reply. I would have responded sooner, but I came to
realize I had headed down the wrong track, and have been setting things
right
for most of the day. I also continued to try and figure out things on my
own, so that when I ask the next part of the question I am better informed
than I was when I started the question.

Second part of question/answer first: I am using four separate tab
controls, each showing only the fields relevant to that stage in the
process.
It is usually necessary for the respondent to see the recommendation, for
the follow-up person to see both the recommendation and the response, and
so
forth. Would using separate forms simplify my custom data validation?

Yes, probably, but you may have good reasons for preserving the form you
have, so that each person can see the data entered at the previous stages.

The problem is that the form needs to know which stage is being entered, so
that, for example, follow-up fields are not validated during the response
stage. Also, you may require that users be prevented from altering data
from previous stages.

A good way to do this is by using the OpenArgs mechanism. You can pass
either a string ("recommendation", "response", etc) or a number (1, 2, 3, 4)
via the OpenArgs argument of the OpenForm method. Whatever you pass is then
available in the form's class module as Me.OpenArgs.

This can first be used by your Form_Load event procedure to hide the tabs
for future stages and, possibly, lock fields pertaining to previous stages
that should not be edited.

Then it can be used again by Form_BeforeUpdate to ascertain which fields to
verify.

I have learned most of what I know about Access from reading and from
newsgroups, and of course by experimentation. As a result there are
things I
don't quite understand, but I can't figure out how to learn more about
them.
One such item is Cancel = True (or Cancel = Anything, for that matter).
If I
try to look it up in a groups search I can find lots of code, but no real
explanation.

Cancel is always an Integer argument, but it essentially functions as a
Boolean. (I suspect it is a hangover from Access 2, which had no Boolean
data type.) Zero means False, and anything else is interpreted as True.

I had been doing some unnecessary things with validation. All I really
need
to do in most cases is attempt to save the record, triggering the Before
Update event. The Before Update event tests each control. At the first
control for which IsNull is true the focus is set to that control, a
message
box is generated, and the next line of code is Cancel = True.

The thing I have done so far that may not work is that the message box
that
is generated as described above is a called procedure. I did that because
the same message box can be generated by nulls at four different points in
the code. It works fine as long as I am just clicking OK at the message
box,
but I want to give the user the option of just closing out without
completing
the section or saving. As I understand it I can't close without saving
(would that be Me.Undo?) from a called procedure.

Correct. If the form is dirty when it closes, then the record will be
saved, UNLESS there is some validation failure at *table* level. This could
include Required fields which are absent, or table-level validation rules
which are not satisfied. However, you can perform a Me.Undo before closing
if you wish not to save the changes.

Could I get around that limitation by defining the message box in Before
Update:
MsgBox1 = msgbox "Prompt",acOkCancel,"Title" (or whatever), and then
referring to MsgBox1 where I am now calling an outside procedure?

Whether you call another procedure or not is immaterial. The important
logic is that you:
(a) ascertain if the changes are valid
(b) if not, prompt for required action (undo the changes or fix the problem)
(c) if undo, then Me.Undo, otherwise set Cancel = <nonzero>

All this is of course in Form_BeforeUpdate. However, Form_BeforeUpdate
could call another procedure(s) to modularise the verification process.

A related question is that I am sending e-mails from within the program
(SendObject). Before sending the e-mail I test for fields that are null
but
shouldn't be. If everything is OK the e-mail message is generated,
otherwise
there is a message box. However, Cancel = True does not prevent the
e-mail
from opening, even if there is a null field. I have to add Exit Sub. Why
does the validation test at SendObject seem to behave differently from the
one at BeforeUpdate..

You should simply attempt to save the record before sending the email. That
way, Form_BeforeUpdate will perform the validation for you.

On Error Goto ProcErr
If Me.Dirty Then Me.Dirty = False
' code will not get here if record validation fails
DoCmd.SendObject ...
ProcEnd:
Exit Sub
ProcErr:
' error message here
Resume ProcEnd

To sum up my question, what is the best way for a user to bail if they
decide not to continue entering a record? One way they would need to do
that
is by way of the message box that is generated upon discovery of a null
field. Also, the way this will work is that a record with a few fields
will
be saved, then later a few more fields will be added to the same record,
and
so on. I expect that Undo (or however the record is prevented from being
saved) will apply only to fields added since the record is reopened. In
other words, after the Recommendation is complete the record is saved.
Later
somebody else reopens the record to respond, but decides not to finish.
Will
their Undo affect only the fields that have been added or modified since
the
record was last saved, or will it affect the entire record? I suspect the
former, but am not sure.

Correct. Me.Undo undoes only the changes made since you last started
editing the record. So, if you modify three fields, then save the record,
then modify a fourth without closing the form or changing focus to another
record, Me.Undo will affect only the fourth field.

 

[Guest]

Thanks so much for all of your help. Regarding validation, all of the fields
on the Recommendation tab need to be completed before the record is saved.
In other words, it's all of the fields or none of them in a section. To
validate the next section (Response) I use:
If Not IsNull(Me.txtResponse) And IsNull(Me.cboResponseName) Then
(validation code)
End If
If any of the text boxes in Recommendation contains a value the others are
tested, otherwise the code passes to the next step. As it stands the record
could contain a value for name or date but nothing for the actual response,
but that is very unlikely. I can add something if that ever turns out to be
a problem.
I have heard of OpenArgs, but I don't think I have used it. I will have to
investigate further. I have used class modules only when they have been part
of code provided by others. Another item on my list of things about which I
need to know more.
I would rather validate at the form level, and undo if needed. I will admit
I am not that familiar with table level validation. It works well enough
from what I have seen, but the error messages aren't all that helpful. Also,
table level validation would be difficult (I think) with the record being
added to in stages.
The thing I need to accomplish with validation is to prevent the user from
saving an incomplete section. Users must not have that option.
In the code snippet you suggested for the e-mail:

If Me.Dirty Then Me.Dirty = False
' code will not get here if record validation fails
DoCmd.SendObject ...

I take it that this would be placed after the attempt to save the record.
My partial understanding of "If Me.Dirty Then Me.Dirty = False" is:
"If the record has been changed (Dirty) then...", but I still can't get my
mind around what Me.Dirty = False means. For that matter, I'm not sure I
have the first part correct.

My point about the called procedure is that I want the form's Before Update
event to say:
"If this text box is null but shouldn't be, then generate an error message
(MsgBox vbOKCancel). If the choice is "OK", undo the changes and exit. If
the choice is Cancel, go back to the form to fill in the missing information."
As it stands, I am calling the message box procedure from within Before
Update, which means (I think) that I can't undo from within the called
procedure. I can only undo from within Before Update (or from another
Event), unless there is a reasonable way to pass the choice (OK or Cancel)
from the called procedure back to Before Update. Even if I can do that, it
is probably just as well to keep the message box (and the resultant choice
between OK and Cancel) within Before Update. That is my best guess based on
my possibly incomplete understanding.

Again, thanks for your help. If I could trouble you just a bit further (and
ask for your patience with what may be denseness on my part), the thing about
Cancel = True (or False) that I don't understand is the Cancel part. What is
Cancel = True saying? What is being cancelled?

Hi Bruce

Some thoughts and answers inline...

Thankd you for your reply. I would have responded sooner, but I came to
realize I had headed down the wrong track, and have been setting things
right
for most of the day. I also continued to try and figure out things on my
own, so that when I ask the next part of the question I am better informed
than I was when I started the question.

Second part of question/answer first: I am using four separate tab
controls, each showing only the fields relevant to that stage in the
process.
It is usually necessary for the respondent to see the recommendation, for
the follow-up person to see both the recommendation and the response, and
so
forth. Would using separate forms simplify my custom data validation?

Yes, probably, but you may have good reasons for preserving the form you
have, so that each person can see the data entered at the previous stages.

The problem is that the form needs to know which stage is being entered, so
that, for example, follow-up fields are not validated during the response
stage. Also, you may require that users be prevented from altering data
from previous stages.

A good way to do this is by using the OpenArgs mechanism. You can pass
either a string ("recommendation", "response", etc) or a number (1, 2, 3, 4)
via the OpenArgs argument of the OpenForm method. Whatever you pass is then
available in the form's class module as Me.OpenArgs.

This can first be used by your Form_Load event procedure to hide the tabs
for future stages and, possibly, lock fields pertaining to previous stages
that should not be edited.

Then it can be used again by Form_BeforeUpdate to ascertain which fields to
verify.

I have learned most of what I know about Access from reading and from
newsgroups, and of course by experimentation. As a result there are
things I
don't quite understand, but I can't figure out how to learn more about
them.
One such item is Cancel = True (or Cancel = Anything, for that matter).
If I
try to look it up in a groups search I can find lots of code, but no real
explanation.

Cancel is always an Integer argument, but it essentially functions as a
Boolean. (I suspect it is a hangover from Access 2, which had no Boolean
data type.) Zero means False, and anything else is interpreted as True.

I had been doing some unnecessary things with validation. All I really
need
to do in most cases is attempt to save the record, triggering the Before
Update event. The Before Update event tests each control. At the first
control for which IsNull is true the focus is set to that control, a
message
box is generated, and the next line of code is Cancel = True.

The thing I have done so far that may not work is that the message box
that
is generated as described above is a called procedure. I did that because
the same message box can be generated by nulls at four different points in
the code. It works fine as long as I am just clicking OK at the message
box,
but I want to give the user the option of just closing out without
completing
the section or saving. As I understand it I can't close without saving
(would that be Me.Undo?) from a called procedure.

Correct. If the form is dirty when it closes, then the record will be
saved, UNLESS there is some validation failure at *table* level. This could
include Required fields which are absent, or table-level validation rules
which are not satisfied. However, you can perform a Me.Undo before closing
if you wish not to save the changes.

Could I get around that limitation by defining the message box in Before
Update:
MsgBox1 = msgbox "Prompt",acOkCancel,"Title" (or whatever), and then
referring to MsgBox1 where I am now calling an outside procedure?

Whether you call another procedure or not is immaterial. The important
logic is that you:
(a) ascertain if the changes are valid
(b) if not, prompt for required action (undo the changes or fix the problem)
(c) if undo, then Me.Undo, otherwise set Cancel = <nonzero>

All this is of course in Form_BeforeUpdate. However, Form_BeforeUpdate
could call another procedure(s) to modularise the verification process.

A related question is that I am sending e-mails from within the program
(SendObject). Before sending the e-mail I test for fields that are null
but
shouldn't be. If everything is OK the e-mail message is generated,
otherwise
there is a message box. However, Cancel = True does not prevent the
e-mail
from opening, even if there is a null field. I have to add Exit Sub. Why
does the validation test at SendObject seem to behave differently from the
one at BeforeUpdate..

You should simply attempt to save the record before sending the email. That
way, Form_BeforeUpdate will perform the validation for you.

On Error Goto ProcErr
If Me.Dirty Then Me.Dirty = False
' code will not get here if record validation fails
DoCmd.SendObject ...
ProcEnd:
Exit Sub
ProcErr:
' error message here
Resume ProcEnd

To sum up my question, what is the best way for a user to bail if they
decide not to continue entering a record? One way they would need to do
that
is by way of the message box that is generated upon discovery of a null
field. Also, the way this will work is that a record with a few fields
will
be saved, then later a few more fields will be added to the same record,
and
so on. I expect that Undo (or however the record is prevented from being
saved) will apply only to fields added since the record is reopened. In
other words, after the Recommendation is complete the record is saved.
Later
somebody else reopens the record to respond, but decides not to finish.
Will
their Undo affect only the fields that have been added or modified since
the
record was last saved, or will it affect the entire record? I suspect the
former, but am not sure.

Correct. Me.Undo undoes only the changes made since you last started
editing the record. So, if you modify three fields, then save the record,
then modify a fourth without closing the form or changing focus to another
record, Me.Undo will affect only the fourth field.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Guest]

Just something I didn't include earlier is that if there is nothing in the
Response section then anybody can enter a response. (In practice this will
be done only by the person assigned to do so. It's not likely that somebody
will be tempted to take on an extra task on their own.) However, I have a
sort of low-level security in that the user selects their name from a combo
box. The combo box's Before Update event is to open a password form. The
password is contained in a hidden column in the combo box record source.
Once the name is selected and verified, both the combo box and the text field
are locked, and require the user's password to unlock. As I said, fairly
low-level security, but in all the years of Word documents we have not had
problems with people changing responses other than their own.

Hi Bruce

Some thoughts and answers inline...

Thankd you for your reply. I would have responded sooner, but I came to
realize I had headed down the wrong track, and have been setting things
right
for most of the day. I also continued to try and figure out things on my
own, so that when I ask the next part of the question I am better informed
than I was when I started the question.

Second part of question/answer first: I am using four separate tab
controls, each showing only the fields relevant to that stage in the
process.
It is usually necessary for the respondent to see the recommendation, for
the follow-up person to see both the recommendation and the response, and
so
forth. Would using separate forms simplify my custom data validation?

Yes, probably, but you may have good reasons for preserving the form you
have, so that each person can see the data entered at the previous stages.

The problem is that the form needs to know which stage is being entered, so
that, for example, follow-up fields are not validated during the response
stage. Also, you may require that users be prevented from altering data
from previous stages.

A good way to do this is by using the OpenArgs mechanism. You can pass
either a string ("recommendation", "response", etc) or a number (1, 2, 3, 4)
via the OpenArgs argument of the OpenForm method. Whatever you pass is then
available in the form's class module as Me.OpenArgs.

This can first be used by your Form_Load event procedure to hide the tabs
for future stages and, possibly, lock fields pertaining to previous stages
that should not be edited.

Then it can be used again by Form_BeforeUpdate to ascertain which fields to
verify.

I have learned most of what I know about Access from reading and from
newsgroups, and of course by experimentation. As a result there are
things I
don't quite understand, but I can't figure out how to learn more about
them.
One such item is Cancel = True (or Cancel = Anything, for that matter).
If I
try to look it up in a groups search I can find lots of code, but no real
explanation.

Cancel is always an Integer argument, but it essentially functions as a
Boolean. (I suspect it is a hangover from Access 2, which had no Boolean
data type.) Zero means False, and anything else is interpreted as True.

I had been doing some unnecessary things with validation. All I really
need
to do in most cases is attempt to save the record, triggering the Before
Update event. The Before Update event tests each control. At the first
control for which IsNull is true the focus is set to that control, a
message
box is generated, and the next line of code is Cancel = True.

The thing I have done so far that may not work is that the message box
that
is generated as described above is a called procedure. I did that because
the same message box can be generated by nulls at four different points in
the code. It works fine as long as I am just clicking OK at the message
box,
but I want to give the user the option of just closing out without
completing
the section or saving. As I understand it I can't close without saving
(would that be Me.Undo?) from a called procedure.

Correct. If the form is dirty when it closes, then the record will be
saved, UNLESS there is some validation failure at *table* level. This could
include Required fields which are absent, or table-level validation rules
which are not satisfied. However, you can perform a Me.Undo before closing
if you wish not to save the changes.

Could I get around that limitation by defining the message box in Before
Update:
MsgBox1 = msgbox "Prompt",acOkCancel,"Title" (or whatever), and then
referring to MsgBox1 where I am now calling an outside procedure?

Whether you call another procedure or not is immaterial. The important
logic is that you:
(a) ascertain if the changes are valid
(b) if not, prompt for required action (undo the changes or fix the problem)
(c) if undo, then Me.Undo, otherwise set Cancel = <nonzero>

All this is of course in Form_BeforeUpdate. However, Form_BeforeUpdate
could call another procedure(s) to modularise the verification process.

A related question is that I am sending e-mails from within the program
(SendObject). Before sending the e-mail I test for fields that are null
but
shouldn't be. If everything is OK the e-mail message is generated,
otherwise
there is a message box. However, Cancel = True does not prevent the
e-mail
from opening, even if there is a null field. I have to add Exit Sub. Why
does the validation test at SendObject seem to behave differently from the
one at BeforeUpdate..

You should simply attempt to save the record before sending the email. That
way, Form_BeforeUpdate will perform the validation for you.

On Error Goto ProcErr
If Me.Dirty Then Me.Dirty = False
' code will not get here if record validation fails
DoCmd.SendObject ...
ProcEnd:
Exit Sub
ProcErr:
' error message here
Resume ProcEnd

To sum up my question, what is the best way for a user to bail if they
decide not to continue entering a record? One way they would need to do
that
is by way of the message box that is generated upon discovery of a null
field. Also, the way this will work is that a record with a few fields
will
be saved, then later a few more fields will be added to the same record,
and
so on. I expect that Undo (or however the record is prevented from being
saved) will apply only to fields added since the record is reopened. In
other words, after the Recommendation is complete the record is saved.
Later
somebody else reopens the record to respond, but decides not to finish.
Will
their Undo affect only the fields that have been added or modified since
the
record was last saved, or will it affect the entire record? I suspect the
former, but am not sure.

Correct. Me.Undo undoes only the changes made since you last started
editing the record. So, if you modify three fields, then save the record,
then modify a fourth without closing the form or changing focus to another
record, Me.Undo will affect only the fourth field.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Graham Mandeno]

Hi Bruce

Thanks so much for all of your help. Regarding validation, all of the
fields
on the Recommendation tab need to be completed before the record is saved.
In other words, it's all of the fields or none of them in a section. To
validate the next section (Response) I use:
If Not IsNull(Me.txtResponse) And IsNull(Me.cboResponseName) Then
(validation code)
End If

OK - then it may not be necessary to "tell" the form which phase is being
completed. However, you might find it's easer to use OpenArgs to do so.

If any of the text boxes in Recommendation contains a value the others are
tested, otherwise the code passes to the next step. As it stands the
record
could contain a value for name or date but nothing for the actual
response,
but that is very unlikely. I can add something if that ever turns out to
be
a problem.
I have heard of OpenArgs, but I don't think I have used it. I will have
to
investigate further. I have used class modules only when they have been
part
of code provided by others. Another item on my list of things about which
I
need to know more.

Oh yeah - there's always plenty of those

A form's class module is simply a fancy name for the code module attached to
a form. The reason it's a *class* module is that the code is only
instantiated when the class object (the form) exists (is open).

I would rather validate at the form level, and undo if needed. I will
admit
I am not that familiar with table level validation. It works well enough
from what I have seen, but the error messages aren't all that helpful.
Also,
table level validation would be difficult (I think) with the record being
added to in stages.

Table level vaidation will NOT work here. You cannot have "required" fields
which are not requred... *just yet*.

The thing I need to accomplish with validation is to prevent the user from
saving an incomplete section. Users must not have that option.
In the code snippet you suggested for the e-mail:


I take it that this would be placed after the attempt to save the record.
My partial understanding of "If Me.Dirty Then Me.Dirty = False" is:
"If the record has been changed (Dirty) then...", but I still can't get my
mind around what Me.Dirty = False means. For that matter, I'm not sure I
have the first part correct.

I should have explained... it's a bit arcane, but Me.Dirty = False is simply
one way to save a record. If BeforeUpdate is cancelled, then it will fail
with an error, so execution will pass to the error handling section and the
next line of code will not be executed.

My point about the called procedure is that I want the form's Before
Update
event to say:
"If this text box is null but shouldn't be, then generate an error message
(MsgBox vbOKCancel). If the choice is "OK", undo the changes and exit.
If
the choice is Cancel, go back to the form to fill in the missing
information."
As it stands, I am calling the message box procedure from within Before
Update, which means (I think) that I can't undo from within the called
procedure. I can only undo from within Before Update (or from another
Event), unless there is a reasonable way to pass the choice (OK or Cancel)
from the called procedure back to Before Update. Even if I can do that,
it
is probably just as well to keep the message box (and the resultant choice
between OK and Cancel) within Before Update. That is my best guess based
on
my possibly incomplete understanding.

Here is a function I use routinely to check for required fields:

Public Function fm_CheckRequiredFields(f As Form, FieldList As Variant, _
Optional NormalColour As Long = vbWhite, _
Optional HighlightColour As Long = &H60FFFF) As Integer
Dim iFirstTab As Integer, sFirstTab As String
Dim c As Control, i As Integer, iBadFields As Integer
For i = LBound(FieldList) To UBound(FieldList)
Set c = f.Controls(FieldList(i))
If IsNull(c) Then
If iBadFields = 0 Or c.TabIndex < iFirstTab Then
iFirstTab = c.TabIndex
sFirstTab = c.Name
End If
iBadFields = iBadFields + 1
c.BackColor = HighlightColour
Else
c.BackColor = NormalColour
End If
Next
If iBadFields Then
f.Controls(sFirstTab).SetFocus
fm_CheckRequiredFields = iBadFields
End If
End Function

You pass it the current form object (Me) and an array of control names. It
checks each of the controls for null. If a control is OK (not null) it sets
its BackColor to NormalColour (default white), otherwise it sets it to
HighlightColour (default pale yellow). It sets focus to the first (if any)
of the invalid (null) controls according to the tab order of the form, and
then returns the number of invalid controls.

So, you can say something like this:
Select Case Me.Openargs
case 1 ' recommendation
Cancel = fm_CheckRequiredFields( Me, _
Array( "Field1", "Field2", "Field3")
case 2 ' response
Cancel = fm_CheckRequiredFields( Me, _
Array( "Field4", "Field5", "Field6")
... etc
End Select
If Cancel then
If msgbox( "Input is required in the highlighted fields. Click OK
to fix " _
& "this, or Cancel to undo all your changes and close the
form", _
vbOkCancel ) = vbCancel Then
Cancel = false
Me.Undo
DoCmd.Close acForm, Me.name
End If
End If

Again, thanks for your help. If I could trouble you just a bit further
(and
ask for your patience with what may be denseness on my part), the thing
about
Cancel = True (or False) that I don't understand is the Cancel part. What
is
Cancel = True saying? What is being cancelled?

Cancel is an argument provided for the event procedure (EP) of most of the
events that can be cancelled. Setting Cancel to a non-zero value will cause
that event to be cancelled when the EP exits.

So, Cancel=True in Form_BeforeUpdate causes the form's Beforeupdate event to
be cancelled (which prevents the update occurring). Similarly, Cancel=999
in Form_Unload cancels the form's Unload event, which prevents the form
closing. There are many other events which can be cancelled.

 

[Guest]

Thanks again for all of your help, and for the validation code you provided.
I have copied this posting, and will study it in more detail over time. I
think I see the general idea of passing a field list to the called procedure,
and the result back to the original event. I like the highlighting feature.
I am dealing mostly with people who are highly proficient in their areas of
expertise, but applications such as this are not among those areas. My
approach to databases, especially ones that some people will use only rarely,
is that training shouldn't involve much more than showing them where the
program is located. Even if they are trained, they probably won't remember
three months later what they learned about the program (nor would I really
expect them to), so it needs to be as self-explanatory as I can make it.
Thank you too for your explanation of Cancel = True. As I understand it, if
an event's code gets to that line it is almost as if the code never ran,
except that I will have seen message boxes and other things that occurred
before that line. If Before Update is cancelled, the update (save) does not
occur.
OpenArgs is my next project. I will investigate and experiment. That
should keep me busy for a while (along with the rest of my job). I really
appreciate the time, expertise, and detail you have put into your replies.

Hi Bruce

Thanks so much for all of your help. Regarding validation, all of the
fields
on the Recommendation tab need to be completed before the record is saved.
In other words, it's all of the fields or none of them in a section. To
validate the next section (Response) I use:
If Not IsNull(Me.txtResponse) And IsNull(Me.cboResponseName) Then
(validation code)
End If

OK - then it may not be necessary to "tell" the form which phase is being
completed. However, you might find it's easer to use OpenArgs to do so.

If any of the text boxes in Recommendation contains a value the others are
tested, otherwise the code passes to the next step. As it stands the
record
could contain a value for name or date but nothing for the actual
response,
but that is very unlikely. I can add something if that ever turns out to
be
a problem.
I have heard of OpenArgs, but I don't think I have used it. I will have
to
investigate further. I have used class modules only when they have been
part
of code provided by others. Another item on my list of things about which
I
need to know more.

Oh yeah - there's always plenty of those

A form's class module is simply a fancy name for the code module attached to
a form. The reason it's a *class* module is that the code is only
instantiated when the class object (the form) exists (is open).

I would rather validate at the form level, and undo if needed. I will
admit
I am not that familiar with table level validation. It works well enough
from what I have seen, but the error messages aren't all that helpful.
Also,
table level validation would be difficult (I think) with the record being
added to in stages.

Table level vaidation will NOT work here. You cannot have "required" fields
which are not requred... *just yet*.

The thing I need to accomplish with validation is to prevent the user from
saving an incomplete section. Users must not have that option.
In the code snippet you suggested for the e-mail:


I take it that this would be placed after the attempt to save the record.
My partial understanding of "If Me.Dirty Then Me.Dirty = False" is:
"If the record has been changed (Dirty) then...", but I still can't get my
mind around what Me.Dirty = False means. For that matter, I'm not sure I
have the first part correct.

I should have explained... it's a bit arcane, but Me.Dirty = False is simply
one way to save a record. If BeforeUpdate is cancelled, then it will fail
with an error, so execution will pass to the error handling section and the
next line of code will not be executed.

My point about the called procedure is that I want the form's Before
Update
event to say:
"If this text box is null but shouldn't be, then generate an error message
(MsgBox vbOKCancel). If the choice is "OK", undo the changes and exit.
If
the choice is Cancel, go back to the form to fill in the missing
information."
As it stands, I am calling the message box procedure from within Before
Update, which means (I think) that I can't undo from within the called
procedure. I can only undo from within Before Update (or from another
Event), unless there is a reasonable way to pass the choice (OK or Cancel)
from the called procedure back to Before Update. Even if I can do that,
it
is probably just as well to keep the message box (and the resultant choice
between OK and Cancel) within Before Update. That is my best guess based
on
my possibly incomplete understanding.

Here is a function I use routinely to check for required fields:

Public Function fm_CheckRequiredFields(f As Form, FieldList As Variant, _
Optional NormalColour As Long = vbWhite, _
Optional HighlightColour As Long = &H60FFFF) As Integer
Dim iFirstTab As Integer, sFirstTab As String
Dim c As Control, i As Integer, iBadFields As Integer
For i = LBound(FieldList) To UBound(FieldList)
Set c = f.Controls(FieldList(i))
If IsNull(c) Then
If iBadFields = 0 Or c.TabIndex < iFirstTab Then
iFirstTab = c.TabIndex
sFirstTab = c.Name
End If
iBadFields = iBadFields + 1
c.BackColor = HighlightColour
Else
c.BackColor = NormalColour
End If
Next
If iBadFields Then
f.Controls(sFirstTab).SetFocus
fm_CheckRequiredFields = iBadFields
End If
End Function

You pass it the current form object (Me) and an array of control names. It
checks each of the controls for null. If a control is OK (not null) it sets
its BackColor to NormalColour (default white), otherwise it sets it to
HighlightColour (default pale yellow). It sets focus to the first (if any)
of the invalid (null) controls according to the tab order of the form, and
then returns the number of invalid controls.

So, you can say something like this:
Select Case Me.Openargs
case 1 ' recommendation
Cancel = fm_CheckRequiredFields( Me, _
Array( "Field1", "Field2", "Field3")
case 2 ' response
Cancel = fm_CheckRequiredFields( Me, _
Array( "Field4", "Field5", "Field6")
... etc
End Select
If Cancel then
If msgbox( "Input is required in the highlighted fields. Click OK
to fix " _
& "this, or Cancel to undo all your changes and close the
form", _
vbOkCancel ) = vbCancel Then
Cancel = false
Me.Undo
DoCmd.Close acForm, Me.name
End If
End If

Again, thanks for your help. If I could trouble you just a bit further
(and
ask for your patience with what may be denseness on my part), the thing
about
Cancel = True (or False) that I don't understand is the Cancel part. What
is
Cancel = True saying? What is being cancelled?

Cancel is an argument provided for the event procedure (EP) of most of the
events that can be cancelled. Setting Cancel to a non-zero value will cause
that event to be cancelled when the EP exits.

So, Cancel=True in Form_BeforeUpdate causes the form's Beforeupdate event to
be cancelled (which prevents the update occurring). Similarly, Cancel=999
in Form_Unload cancels the form's Unload event, which prevents the form
closing. There are many other events which can be cancelled.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Graham Mandeno]

I really appreciate the time, expertise,
and detail you have put into your replies.

You're welcome, Bruce