Validating "Orders" form

[Martin]

I hope not, but, I think the answer to this question is "it can't be done".

Northwind sample database. 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 a new order without a single item having been ordered.
Not something a user should be able 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_BeforeUpda te() to validate without ambiguity this could be
policed very nicely.

If you place 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, either
through new entries or existing order modification. Where do you place the
code?
Thank you,

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

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

[Rick Brandt]

Martin wrote:

I hope not, but, I think the answer to this question is "it can't be
done".

It can't be done. If you're concerned about it you can certainly make a routine
that will find any orders with no line items and delete them. Perhaps run this
whenever the form is closed.

At some point the user is always able to enter garbage.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Anthony England]

"Martin" <0_******@pacbe ll.net> wrote in message
news:u9******** ********@newssv r11.news.prodig y.com...

I hope not, but, I think the answer to this question is "it can't be done".

Northwind sample database. 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 a new order without a single item having been ordered.
Not something a user should be able 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_BeforeUpda te() to validate without ambiguity this
could be policed very nicely.

If you place 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, either
through new entries or existing order modification. Where do you place
the code?
Thank you,

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

At 'table level' you cannot force an order to have at least one order item -
that is, the database table structure allows this. Therefore, you need to
restrict how users add a new order.
There is no quick fix for this application, but one technique you could look
at is unbound forms. For example, the orders form would no longer allow
simple additions, but you have a New Order button which pops up an unbound
form. Here you add the customer, product, quantity, etc and an OK button
will create an order with that one line item and then allow further line
items to be added in the subform.
Another possibility is temporary tables, where you create both the order and
the line items in tables TempOrder and TempItem and have an OK button on the
order form which checks everything is OK in both tables before committing
the data to the real tables.
Both of these possibilities may be more work and less fun than you had hoped
for, but both are techniques I have seen used with this sort of problem.

[Martin]

Anthony England wrote:

At 'table level' you cannot force an order to have at least one order
item - that is, the database table structure allows this. Therefore, you
need to restrict how users add a new order.
Understood. That's what I was hoping to do by only allowing a database
write (to any affected table) upon reasonable verification of minimum order
requirements. Yes, of course, users will make mistakes (mispellings, wrong
data, etc.). You should, however, be able to do a little more validation
than simply saying "oh well, I use a subform so I have to take an incomplete
order and live with it".
There is no quick fix for this application, but one technique you could
look at is unbound forms.

While I don't have a tremendous amount of experience with Access it is
starting to seem like it is a royal PITA. Controls that don't work
properly, ActiveX issues, and this matter of proper UI/data validation being
a few of the items I've run into so far. I'll continue to explore for a
little while longer. However, my current sentiment is that it would be much
more intelligent (and maybe even productive) to use VB6 to manage the UI
experience with unbound controls and then drive the database with
significantly improved solidity.

I am astounded that Access does not provide a mechanism to determine if a
BeforeUpdate event was triggered by intra-form navigation rather than
form-to-form navigation (among other things). Thinking out of the box for a
moment, a possible fix might be to write some code to be able to trap
relevant Windows messages before the form gets them. This would allow
setting a form-level variable to indicate what the user clicked with the
mouse or pressed on the keyboard. Checking this variable in
Form_BeforeUpda te() would then provide a mechanism to intelligently decide
what to do with the event.

I guess I can compromise and say that I don't have a problem with the table
corresponding to the "one" side of the form being updated. It would be nice
to not allow the user to leave the form unless the required "many" side/s
can be validated to a reasonable extent.

Thanks,
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~
Martin Euredjian
eCinema Systems, Inc.

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

[Arno R]

What is wrong with Rick's idea?
IMO it would be a PITA to write an app in VB6 because of this 'limitation'
You need to know the 'habits' of Access and treat Access like a lady ;-)

I have a similar app and simply check for Order details when I close the Orders form.
If no details ==> the record is deleted. (no confirmation but only a message)
I also check for details before I allow to leave the Record (e.g. navigation)
Also on startup I run a routine to simply delete all OrderRecs without details (without asking)
This is just to make sure ...

You could also use something like a global var to determine the last accessed ordersID.
On current: If no details present with last accessed ID ==> delete that record.

Arno R


"Martin" <0_******@pacbe ll.net> schreef in bericht news:7g******** ***********@new ssvr14.news.pro digy.com...

Anthony England wrote:

At 'table level' you cannot force an order to have at least one order
item - that is, the database table structure allows this. Therefore, you
need to restrict how users add a new order.

Understood. That's what I was hoping to do by only allowing a database
write (to any affected table) upon reasonable verification of minimum order
requirements. Yes, of course, users will make mistakes (mispellings, wrong
data, etc.). You should, however, be able to do a little more validation
than simply saying "oh well, I use a subform so I have to take an incomplete
order and live with it".

There is no quick fix for this application, but one technique you could
look at is unbound forms.

While I don't have a tremendous amount of experience with Access it is
starting to seem like it is a royal PITA. Controls that don't work
properly, ActiveX issues, and this matter of proper UI/data validation being
a few of the items I've run into so far. I'll continue to explore for a
little while longer. However, my current sentiment is that it would be much
more intelligent (and maybe even productive) to use VB6 to manage the UI
experience with unbound controls and then drive the database with
significantly improved solidity.

I am astounded that Access does not provide a mechanism to determine if a
BeforeUpdate event was triggered by intra-form navigation rather than
form-to-form navigation (among other things). Thinking out of the box for a
moment, a possible fix might be to write some code to be able to trap
relevant Windows messages before the form gets them. This would allow
setting a form-level variable to indicate what the user clicked with the
mouse or pressed on the keyboard. Checking this variable in
Form_BeforeUpda te() would then provide a mechanism to intelligently decide
what to do with the event.

I guess I can compromise and say that I don't have a problem with the table
corresponding to the "one" side of the form being updated. It would be nice
to not allow the user to leave the form unless the required "many" side/s
can be validated to a reasonable extent.

Thanks,


~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~
Martin Euredjian
eCinema Systems, Inc.

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

[Martin]

Arno R wrote:

What is wrong with Rick's idea?
Take the example of an existing --and perfectly valid-- order being edited.
The user deletes all detail information and navigates away from the form.
Now you have a previously-good order that has retained the information on
the "one" side of the equation but has lost all data on the "many" side. If
you "auto clean" the DB and delete all orders that have no items, you just
added insult to injury. An order that was perfectly real was just cleansed
off the face of the planet. The user should have never been allowed to edit
away all detail data. If the intent was to delete the order, then a proper
and separate mechanism should be provided.

Of course, some sort of a multi-form/multi-state approach could be used to
slice-up the order entry and editing process so that you can police each and
every step of the way. Someone suggested using temporary tables, for
example. That's fine. There's always a work around. However, MS has loads
of form/subform examples that seem to suggest that this is an acceptable and
supported way to manage one/many type data entry. Which, of course, isn't
true because it opens the doors to a horrible mess.
IMO it would be a PITA to write an app in VB6 because of this 'limitation'
Most of the form/record management code can be nicely encapsulated into a
reusable class. The PITA is creating the forms and fine-tuning the UI.
But, that's true of any nicely tuned UI project. They take lots of work.
VB6 has nice advantages that I cannot understand why Access does not offer,
for example, control arrays. You save tons of coding by using them where
appropriate.
You need to know the 'habits' of Access and treat Access like a lady ;-)

Very true, that's why I am not going to give up just yet. It is painful,
however, to burn well over a week trying to figure out a work-around for
this form/subform BeforeUpdate problem and finally have to descend into
allowing bad records to be entered into the database. Not my idea of
elegance in design by a long shot.
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~
Martin Euredjian
eCinema Systems, Inc.

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

[Arno R]

"Martin" <0_******@pacbe ll.net> schreef in bericht news:6Q******** ********@newssv r25.news.prodig y.net...

Arno R wrote:

What is wrong with Rick's idea?
Take the example of an existing --and perfectly valid-- order being edited.
The user deletes all detail information and navigates away from the form.
Now you have a previously-good order that has retained the information on
the "one" side of the equation but has lost all data on the "many" side. If
you "auto clean" the DB and delete all orders that have no items, you just
added insult to injury.

I only "auto clean" because there still is a very slight chance that orphan records exist...
I understood you wanted to prevent orphan records...
An order that was perfectly real was just cleansed
off the face of the planet. The user should have never been allowed to edit
away all detail data. If the intent was to delete the order, then a proper
and separate mechanism should be provided.

It is up to *you* as the developer to provide such mechanism.

If the user wants to add records you can allow or deny that.
If you want to validate data before saving, you can do that
You can prevent the editing of the detailrecords from the subform if you like ...
You can validate each and every field while editing ....
You can also prevent the deletion of the detailrecords from the subform if you like ...
You can ask the user if he/she would want to delete and allow or prevent that ....

What more do you need?

Arno R

[Martin]

Arno R wrote:

If the intent was to delete the order, then a proper
and separate mechanism should be provided.

It is up to *you* as the developer to provide such mechanism.
That's what I said.

If you want to validate data before saving, you can do that
No you can't!

Take the Northwind database "Orders" form.
Validation rule: While using this form, the operator shall not be permitted
to enter an order into the system if the order does not contain any items.

I am using the Northwind database "Orders" form as an example that everyone
is likely to have on their computers. My application is not a product
ordering application but rather an engineering solution I am attempting to
fit into Access. There will be many forms with one/many relationships
handled with form/subforms for the UI. Clean and fully-validated data
records is a must.

If I was outsourcing the work to a consultant, the above validation rule
would have been in the specifications, without a doubt. Based on all
current indications, this counsultant wouldn't have a way to deliver this
without resorting to some pretty extensive girations.
What more do you need?

Just that, for now. :-)

Later today I am going to explore the idea of popping the user back into the
newly created record if the last record added to the database did not
contain detail items. Thinking out loud, this would probably have to be
added to the Form_OnCurrent( ) event along with (maybe) a global variable set
to the last record loaded onto the "one" side of the form. The code would
query for order items for the last order and, if none are found, pop the
user back to that record. Again, in Form_OnCurrent( ) a check would be made
for order items in the (now current) order and offer a dialog box providing
the option to either delete the order or add items to it.

That's the theory. I'll see how/if it works later tonight.

This, of course, is tenous in that it would have to allow the "one" side of
the order to be saved and then attempt to cleanup (delete) or complete the
"many" side of the order. If anything in the pop-back process is disturbed
(say it is a remote connection, or the user isn't interested) the bad
order's "one" side would now be saved to the corresponding table.

One other thought. Maybe I can add a boolean "OrderItemsEnte red" field to
Orders table. It would have a default value of "False". This would provide
for a much more solid way to determine if an order is valid. This field
would only be set to "True" upon entry of at least one item in the subform.
In my case there are more than one "many" tables related to what would be
the orders "one" side. Imagine if you had a table of phone calls and
another table of notes or history on each order. You could have multiple
order items, multiple call logs and multiple notes. Maybe even more than
one of these "many" relationships would require at least one entry for a
valid order. In this case, the Orders table would have one boolean flag for
each of the required "many" records.

A little messy, but there might be a way yet!
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~
Martin Euredjian
eCinema Systems, Inc.

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

[Lyle Fairfield]

Northwind is not renowned for its exemplary practices. It's somewhere
between a shill and a trapeze act.
I suppose that you have identified a legitimate concern although I
don't pretend to have studied everything in this thread carefully.
Northwind was not on my computer but I donwloaded it so that I could
see just what you are telking about.

I !!!think!!! this is a sample of something about which I've argued
[it's hard to imagine my arguing but it has actually happened once or
twice!] and lost contracts. That is, should we go from the particular
to the general, from the child to the parent, or vice versa. I prefer
small first, large later. That is children have parents; parents do not
have children.

I believe my Orders form would simply be a list of products required. I
assume the DB and Access could decide from whom to order them, (I might
want to indicate a preference if an item were available from more than
one supplier), print an Order, and mark each record as having been
ordered. But the ORDER would have existence only in real-time, it would
be the printing of the Order report. We could keep information about
the Products, Date Ordered, Signing Authority, Budget Charged,
Cancelled, quite easily.

Ah, the regulars will say, "You CAN'T DO THAT!" And I will say, "Yes I
know that you WON'T DO THAT" but I actually have done something
extremely similar which is likely to mean that I can do that.