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.