What is "the access way" to validate a form?

[LAS]

I found that I had to calls to my validation code in a number of places,
before a close, before a .movenext, etc. I couldn't put it in
..before_update or .before_insert because a failed validation would interrput
those actions and cause an error. I keep bumping into advice to use "the
access way," (not necessarily about this particular issue), but not always
an explanation of what it is. Is there "an access way" to validate a form
before saving? Or is "the access way" to put validation on each control?

TIA
LAS

 

[David W. Fenton]

I found that I had to calls to my validation code in a number of
places, before a close, before a .movenext, etc. I couldn't put
it in .before_update or .before_insert because a failed validation
would interrput those actions and cause an error. I keep bumping
into advice to use "the access way," (not necessarily about this
particular issue), but not always an explanation of what it is.
Is there "an access way" to validate a form before saving? Or is
"the access way" to put validation on each control?

1. For single controls:

a. choose the most specific control type and limit user input to
valid values.

b. validate in the control's BeforeUpdate event.

2. For groups of fields whose values are interdependent:

a. write a validation routine for the set of controls and call
that single validation routine in the BeforeUpdate of all the
controls.

OR

b. create a user interface that steps the user through choosing
values one at a time, with each validated individually at each
step. The standard MS wizard interface is very good for this
because it's quite familiar to users.

In other words, do your validation as close to the editing of each
individual piece of data as possible.

 

[Bob Quintal]

I found that I had to calls to my validation code in a number of
places, before a close, before a .movenext, etc. I couldn't put
it in .before_update or .before_insert because a failed validation
would interrput those actions and cause an error. I keep bumping
into advice to use "the access way," (not necessarily about this
particular issue), but not always an explanation of what it is.
Is there "an access way" to validate a form before saving? Or is
"the access way" to put validation on each control?

TIA
LAS

The before_update is where you usually want validation to occur, either
the control's version of the event if you are only validating the
control, or the form's version if validating consistency between
controls.

They are supposed to interrupt those actions because logic says you do
not want to write erroneous data to the tables.

If you get an error, you have incorrectly coded the validation or are
not correctly handling the cancellation of the update,

 

[LAS]

But if my application works best to do some validation after all the data
has been entered by the user (and there are some reasons for that sometime),
how does one validate? I understand how to validate control by control,
but my question is specifically how to deal with the failure if validation
was in the before_update event of the form.

 

[LAS]

The problem is that if I have "If fncValidated() = false then
exit sub
end if
in the Before_Update event of the form, and the update was triggered by a
..movenext, then the movenext can't complete and I get an error. I'd have to
go and re-create the problem (I took the validation out of before_update and
put it before .movenext, .addnew, etc.), to remember the exact error.

 

[Rick Brandt]

But if my application works best to do some validation after all the data
has been entered by the user (and there are some reasons for that
sometime),
how does one validate? I understand how to validate control by control,
but my question is specifically how to deal with the failure if validation
was in the before_update event of the form.

In the BeforeUpdate event you run your test code. When there are problems
you display a message to the user and cancel the update. It does not matter
what action triggered the update. It will not even be attempted if you
cancel the update event.

 

[Bob Quintal]

The problem is that if I have "If fncValidated() = false then
exit sub
end if
in the Before_Update event of the form, and the update was
triggered by a .movenext, then the movenext can't complete and I
get an error. I'd have to go and re-create the problem (I took
the validation out of before_update and put it before .movenext,
.addnew, etc.), to remember the exact error.

If fncValidated() = false then
cancel = true
exit sub
end if

would have saved you headaches.

Please do yourself a favor and read a book, or take a course....

 

[David W. Fenton]

But if my application works best to do some validation after all
the data has been entered by the user (and there are some reasons
for that sometime), how does one validate? I understand how to
validate control by control, but my question is specifically how
to deal with the failure if validation was in the before_update
event of the form.

I would say that, as a general principle, validation is not often
best implemented at the record-level event.

One of the issues on which many disagree with me, however, is on the
question of using a single form for both adding and editing records.
I very often have a separate unbound form for adding new records,
and allow the creation of the new record only when the controls on
the unbound New Record form have been properly filled out. I don't
include all fields on the New Record form, only the ones that are
required to create a valid record.

This avoids a number of issues in the full detail form in that you
then need only validate in the events related to editing, and don't
have to worry about the insert events.

I know that I never get much in the way of support for this approach
when I describe it, but it has been the easiest way for me to
address these kinds of problems over the last 16 years I've been
developing in Access professionally.

I think it just overcomplicates your coding if you have to account
for both additions and editing in the same form.

 

[David W. Fenton]

And after you add the new record to the underlying table, do you
then open the edit form so the user can fill in any other fields
for that record?

Yes, exactly.

 

[LAS]

Yes, it does. When the before_update is cancelled, the .MoveNext (or
whatever) sends a message that it cannot complete. If necessary, I'll
re-create the situation so I can tell you the exact message.

 

[LAS]

I don't know why you think I should read a book or take a course. The code
you show below is EXACTLY what I have in the before_update event. Have you
tried it? What happens in your situation if a .movenext can't complete
because the code can't leave the current record?????

 

[Bob Quintal]

I don't know why you think I should read a book or take a course.
The code you show below is EXACTLY what I have in the
before_update event. Have you tried it? What happens in your
situation if a .movenext can't complete because the code can't
leave the current record?????

Your code as posted does not have the cancel = true statement.
Exiting the sub without cancelling the update can cause all sorts of
errors.

Note that cancelling the before update event by setting cancel = true
may trigger an error #2501 in the calling code which is very easy to
work around, the web is full of examples.

As to your .movenext, if it is in the code following your exit sub,
it will not execute.
If it is somewhere else, you need to learn how to set a variable
indicating the fact that the update is cancelled and bypass your
..movenext statement.

Not seeing the entire code segment makes it impossible to state the
exact changes needed to your code.

 

[Bob Quintal]

Yes, it does. When the before_update is cancelled, the .MoveNext
(or whatever) sends a message that it cannot complete. If
necessary, I'll re-create the situation so I can tell you the
exact message.

Where is your .movenext, and how have you attempted to test that the
event was cancelled in your code.

 

[David W. Fenton]

Yes, it does. When the before_update is cancelled, the .MoveNext
(or whatever) sends a message that it cannot complete. If
necessary, I'll re-create the situation so I can tell you the
exact message.

If BeforeUpdate is cancelled, you have to cancel the .MoveNext
command.

You may have to set a module-level flag variable to test whether the
..MoveNext needs to happen or not -- it depends on where it is being
called.

But, again, I'd question moving the record pointer using the form's
recordset (which is the only object where .MoveNext would cause the
error). You're just doing things WRONG from the get-go and we've
been telling you that repeatedly (at least *I* have), but you still
keep encountering problem after problem after problem.

Maybe you need to face up to the possibility that I'm right that
your basic approach is wrong.

 

[LAS]

OK, so what I'm getting is that I can't let the .movenext be the thing that
triggers the update (it does trigger an update). Instead, I need to do an
explicit update before the .movenext happens. Or something else. OK.
That's an answer.

Access has so much power. There's stuff like, for instance, .movenext. Are
you saying "the access approach" is not to design anything that doesn't make
use of the navigation buttons, etc? If so, that's an answer to my original
question. I'm not doing it "the Access way." I'm not sure I want to.
Pretty sure I don't, actually, in this particular instance. But at least it
clarifies the terminology.

 

[David W. Fenton]

Access has so much power. There's stuff like, for instance,
.movenext.

I don't use .MoveNext in forms at all, only in recordsets, and then
only very seldom. The reason is because there is very seldom a case
where it is appropriate to walk through a group of records one at a
time.

You've jumped to using a recordset before you'd learned how to use
the form, it seems to me, and so far as I can tell (and I am only
guessing) that's the source of most of your problems.

Are
you saying "the access approach" is not to design anything that
doesn't make use of the navigation buttons, etc?

In general, it's unusual to not use the built-in navigation buttons.
I've done it, but only occasionally. In the cases where I've done
it, I've used the code from the Access Developers Handbook, and that
code uses the form's recordsetclone to navigate.

Now, I do certain kinds of record navigation with the recordsetclone
in many applications, usually combo boxes to skip to a different
customer or inventory item, for instance. In all of these, before
the code that actually moves the current record pointer, I check if
the form is dirty, and force a save BEFORE calling the navigation
code.

In a Find combo box, this is what that looks like:

With Me.RecordsetClone
.FindFirst "[InventoryID]=" & Me!cmbFindInventory
If Not .NoMatch Then
If Me.Dirty Then
Me.Dirty = False
End If
Me.Bookmark = .Bookmark
End If
End With

The Me.Bookmark line is the one that actually moves the current
record pointer, and I don't get there until after any changes have
been saved. In that code, if there's a BeforeUpdate event, it would
be triggered by the Me.Dirty = False.

But I'd really be very wary about mixing up validation code with
navigation. I would tend to not allow navigation at all until a
record has been saved. That would mean having a SAVE command button,
and disabling all navigation while the record is dirty. You can do
this with the OnDirty event, a handy addition post-A2000 (or maybe
it was in A2000 -- I can't remember).

If so, that's an answer to my original
question. I'm not doing it "the Access way." I'm not sure I want
to. Pretty sure I don't, actually, in this particular instance.
But at least it clarifies the terminology.

If you don't want to do it the Access way, you're bound to continue
banging your head up against continuing problems that are going to
pop up all the time.

That said, it's not that you have to do things the Access way, but
you'd damned well better understand the Access way and have good
reasons for choosing to do things in some other way.

Multi-field validation is actually a fairly complicated issue in
Access, and in my experience, everyone who tries to use the events
of a bound form to implement it has problems with it. That's why I
don't do multi-field validation using the form-level events -- I try
to validate each field as it's being edited, and where that's not
entirely possible either use an unbound form for creating new
records (where it's much easier to control) or a wizard-type
interface that breaks down the validation into simple steps
(allowing only appropriate choices at each step).

Others may differ on how to solve this difficult problem, but I can
guarantee you that you're making your task more difficult than it
needs to be by insisting on mucking about with the form's recordset.
Lose that and things will simplify substantially -- it can still be
complex and tricky, but you'll never solve it the way you've been
approaching it.