BeforeUpdate

[Martin]

Access 2003.

Orders form.

The form "BeforeUpdate" event fires when clicking into the datasheet from
the other fields --all belonging to tblOrders.
This makes it very difficult to perform validation before navigating out to
another order. I am having trouble figuring out how to determine if the
form's BeforeUpdate event is happening when navigating to another order or
simply because the user is clicking into the datasheet subform.

Any suggestions?

Thanks,

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Martin

To send private email:
x@y
where
x = "martineu"
y = "pacbell.net"

 

[Van T. Dinh]

There is a feature called "AutoSave" on the Form/Subform combination that
Microsoft implemented to preserve the Referential Integrity of data in a
One-to-Many relationship for which the Form / Subform combination is
designed.

Basically, when the Main Form is dirtied and the user moves the Focus to the
Subform, Access will update the One/Parent Record on the Main Form into the
Table. Thus, the BeforeUpdate Event of the Main Form will occur here.

The same can be said when the user moves from Subform back to the Main Form,
i.e. the current Record in the Subform will be updated into the Table
automatically.

 

[Martin]

How can I tell if BeforeUpdate is happening due to moving from form to
subform or from record to record on the main form (records in tblOrders)?

As a comment, I've heard from those proposing that using bound controls is a
bad idea. This "BeforeUpdate" problem might suggest they are right. With
unbound controls you have to do a bunch more work (which can be encapsulated
into a class module and reused from form to form) but you have absolute
control of it all.

-Martin

 

[Van T. Dinh]

If the main Form is dirtied, the BeforeUpdate Event WILL happen if the Focus
is moved from the main Form to the Subform.

Not sure what you are talking about in the 2nd paragraph. I suspect that
you actually meant "not a bad idea" rather than "a bad idea".

There a a number of work-arounds but all require a fair bit of coding. In
my case for data entry into Tables in a One-to-Many relationship, I hide the
Subform until the user explicitly saves the "One" Record on the main Form
(using a CommandButton provided on the main Form). After that, I make the
Subform visible so that the user can enter the "Many" Records.

 

[Martin]

I guess I am asking if there's a way to distinguish a BeforeUpdate happening
because of a move from the form to a subform vs. the case of navigating form
records (the "one" side of the equation).

Or, put a different way: How do you know why a form's "BeforeUpdate" event
happened?

I've been playing with a state-machine approach to see if I can make sense
out of this. Not sure yet.


Regarding the comment about bound controls. I actually meant to say that
they can be a really bad idea.

-Martin

 

[Van T. Dinh]

See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)

I guess I am asking if there's a way to distinguish a BeforeUpdate
happening because of a move from the form to a subform vs. the case of
navigating form records (the "one" side of the equation).

Not AFAIK of since the BeforeUpdate Event will fire before the navigational
event(s) even though it is activated by the navigation done by the user.

I guess if you can disable all the navigation methods and provide
CammandButtons or a navigational ComboBox for the user to navigate (in the
"One" side) then you can recognise this in the CommandButton_Click Event or
the ComboBox_AfterUpdate Event.

Or, put a different way: How do you know why a form's "BeforeUpdate"
event happened?

To me, this is a separate and distinct question. When the current Record
(in the Form's buffer) needs to be updated into the Table, then Access
exposes the BeforeUpdate Event so that we can hook our custom code to for
additional processing. Note that in Access bound Form, only the Form's
current Record can be processed so if the current Record is modified, it
will need to be updated (into the Table) before another Record can become
the Form's current Record.

I've been playing with a state-machine approach to see if I can make sense
out of this. Not sure yet.


Regarding the comment about bound controls. I actually meant to say that
they can be a really bad idea.

I and (lots of other programmers) don't have any problem with bound
Controls. In fact, I think the power of Access comes from bound Controls as
Access will do a lot of work on data retrieval, manipulations and updating
automatically using bound Forms / bound Controls. However, if you are more
comfortable with unbound Controls, you can use them also but you will have
to handle data retrieval, manipulations & updating by your own code.

 

[Martin]

I and (lots of other programmers) don't have any problem with bound

Controls. In fact, I think the power of Access comes from bound Controls
as Access will do a lot of work on data retrieval, manipulations and
updating automatically using bound Forms / bound Controls. However, if
you are more comfortable with unbound Controls, you can use them also but
you will have to handle data retrieval, manipulations & updating by your
own code.

Don't get me wrong, bound controls are great. When they work well, they are
great time savers.
I've seen lots of examples where it might be much more robust to avoid them
at all costs.


For the purpose of this thread, the specific scenario I am trying to work
through seems to be one that might justify doing it the hard way. If
there's a way not to, by all means, I'd love to know. Let me use the
Northwind example to illustrate:

Orders form.

Go to a new record.
Select a customer in "Bill To:"
Don't enter any products whatsoever.
Now click or page up/down away from this new record.

You just created an order without a single item being ordered. Not
something you would want to allow a user to do.

You would think that one could use the form's BeforeUpdate even to validate
the order. This would be true if the form did not contain any subforms.
However, in the case of "Orders" the subform gets in the way.

Another example of this is that you can go into any one order with multiple
ordered items and delete every single item in the order. Again, if you
could use Form_BeforeUpdate() to validate without ambiguity this could be
policed very nicely.

If you put validation code in the form's BeforeUpdate even this code will
also run when the use clicks or tabs through into the subform. And, as far
as I have been able to determine, there is no way to distinguish a
Form-to-Form BeforeUpdate event from a Form-to-Subform BeforeUpdate event.
This distinction, I think, is important.

So, here's the task: Make the Nothwind "Orders" form solid enough not to
allow faulty/incomplete orders to be entered into the database. Where do
you place the code?

I am relatively new to Access but not new at all to programming (well over
20 years, everything from embedded/hardware/software using assembly, FORTH,
C, C++, VB, LISP, Verilog and others). I still remember hand-wiring my own
Intel 8080-based computers and writing the OS by hand, including disk
drivers for 8 inch floppies. Anyhow, the coding process isn't a problem
here.

It might just be that I haven't identified just what to hook into in Access
in order to make it do what I want. Given my current failure I to find "the
way", I am thinking that the only way to make a form like Northwind's
"Orders" behave as stated is to take over and use unbound controls for most,
if not all, of it.

For the sake of maintaining focus, I am not interested in exploring an
alternative mult-form approach to entereing orders. I think using the
Northwind "Orders" form as it stands, with the aim of making it fully
validate order entry pretty much narrows my current quest (and hours of
frustration).

I'll appreciate a shove in the right direction if there is one to follow.

Thanks for your help,


-Martin

 

[Martin]

Well, it seems very clear that this can't be done. A very serious flaw as
far as I am concerned.

-Martin

 

[Rick Brandt]

Well, it seems very clear that this can't be done. A very serious
flaw as far as I am concerned.

-Martin

It would be a nice feature, but here is the way I look at an order with no
line items on it (assuming it was not caught by any means).

Is anyone going to be shipped product without be billed for it?
NO

Is anyone going to be billed for product they don't receive?
NO

Is inventory going to be incorrect as a result?
NO

Ergo, not such a big deal

Orders typically have several stages. They are entered, they are picked,
they are shipped, they are invoiced. An order with no line items is going
to be caught up at all of the stages past data entry and someone will either
fix it or delete it.

 

[Martin]

I see your point. But, this would be considered bad programming if you were
buying an application at any price. It's not clean. At least not the way I
like to do things.

Anyhow, see my thread at comp.databases.ms-access. I have an idea that I am
going to try later today.

Thanks,

-Martin

 

[Van T. Dinh]

The NorthWind database is a *sample* database and Microsoft only want to
present basic database concept and basic GUI. I am sure Microsoft want to
make it as simple as possible so that new users can understand and therefore
there will be defects in NorthWind if considered as a real-life application.

I mentioned there are a number of work-arounds in one of my previous
replies. One work-around is to use temporary Tables (for both the "One"
side and the "Many" side). Use code to force the user to explicit saves the
set of Records. When this happens, check the whole set before actually
writing from the temp. Tables to the real Tables. It is also possible to
use ADO Recordsets (which are not based on real Tables) as the Recordsets
for the main Form / Subform instead of the temporary Tables.

 

[Martin]

The NorthWind database is a *sample* database and Microsoft only want to

present basic database concept and basic GUI.

Understood. Just convenient to refer to it because everyone has it.

I mentioned there are a number of work-arounds in one of my previous
replies.

Again, understood. The reason for posting, however, was to determine if a
Northwind "Orders" type form with one or more subforms could be used and
fully validated without resorting to extensive work-arounds. Imagine if you
have over a dozen form/subform combinations to deal with...which I do. I
want to write as little code as possible. It would have been so easy to be
able to say "if the BeforeUpdate event was triggered by entering the
subform, validate form data and enter the subform for further data entry,
else, validate the form data and proceed with navigation to another record".

I still think that MS should have provided trigger-source data on the
BeforeUpdate event. Either that or information on how to fully validate
form/subform instances without brain surgery.


Thanks for your input,

-Martin