Re: Access 2007 Technical Questions

Thank you for a much more constructive and specific response than what most
others has given. I can tell you have given this through some thorough
thought process on this rather than just coming out stating that I'm doing
it all wrong. You went beyond that and went to understand my dilemma and
then to help me out with other work arounds that I may not have necessarily
thought of.

1 & 2) Validations

I will need to reevaluate doing it this way as there are things at times
that I still like to have done after the entry of a field level has been
done, but will with a high probability take the trade off. Just as you
mentioned about the reasons for form level validations, I also have form
level validations for those same reasons. I just like to have all errors
caught ASAP. Where a lot of this comes from, I have been in all 3
positions, transcriber (data entry) from when I worked at the IRS,
developer, and user. These last 2 are as of the current position, even
though most of the work I do is development, but that's only cause I have
automated my primary job to the point that my primary job only takes at
most, 10% of my total time. Given my primary job, I am still held
responsible to the extent reasonably possible, so I also have to use
measures to make sure it's reasonably as accurate as it can be. For a
while, we did use a manufacturing DB program to get away from paper logs.
However, cause of the issues with it, plant by plant, the use of it was
dropping, which then my plant was the last plant to drop it. The reason why
my plant was the last one to drop it, I had work arounds in place to address
a lot of the issues. However, a lot of the others didn't want to use the
work arounds for one reason or another. Of course, my work arounds was more
so for the manufacturing side, and most of our plants are more so geared
towards assembly.

Anyhow, from the time of notice to the time when we were dropping the
manufacturing program, I had only 3 weeks to get something else in place
that would record all of the needed data and not only that, but also report
into our main DB program. Our IT department wanted the operators to use the
text version of the screens, which the supervisors and I all saw too many
issues with that, which still exists today, and that was in June of 2001,
when it was the last month to use the manufacturing DB program. The cost of
DB program was way too high to use for just one plant, thus didn't make
sense to renew the licenses on it. At that time, I would have liked to use
Access, but due to my limited knowledge of Access (I knew of the basics, but
that was about it as I knew very little of the programming side in it), and
I had only 3 weeks time tops to get something in place, debugged, and have
all operators trained, I did create a program within Excel, though I spent
12 hours per day for 5 days per week over those first 2 weeks and 2 days,
which then trained operators over the last remaining 3 days. I forewarned
my bosses that this program that I created in Excel would only be intended
as an intermediate program, not a permanent solution cause of all of the
different issues that I knew of in Excel, which obviously still exists today
in Excel. Of course, that's no fault of Excel cause Excel isn't really
intended for that sort of stuff, but it's all that I really had to go with
given the level of knowledge and the amount of time at that point of time.
Here it is, going on 5 years since put into place, and we are still using
it. I haven't done much to the program either since then, other than
monitoring the data, but that's still way better than reverting back to the
paper long days and manually putting that data into our main DB program.
But then our IT department hasn't done much either as it's hands been tied
by senior management being rather stingy on the funds. In this time period,
we have gone through 2 different sales of the division.

Anyhow, while I had plans on developing an Access program in the place of
Excel, I wasn't about to release a version without meeting certain
criterias. This response has renewed that possibility.

Even if going with what you suggested doesn't work out, at least using the
toolbar and toolbar buttons as the way to get around the limitations should
do the trick as you also stated.

3) ADO doesn't allow for Dynamic Cursor Keyset against Jet Engine

Sorry if I caused any confusion on this as when I see databases primarily
with a Jet engine, I seen mostly extensions of MDB/MDE, even of those that
aren't ran via MS Access. Yes, this is a general problem of ADO and Jet as
it has also been documented.

4) Listbox properties not updating until after it has lost focus

Yes, I have attempted to use the AfterUpdate event. As I attempted to use
the AfterUpdate event to do certain things, it came to my realization that
the properties of the listbox wasn't updating. I also used messageboxes to
troubleshoot it, and cause it isn't updating until it loses focus, it's the
very thing that caused me to go from using Centralized Standard Module to a
Class Module to address this issue.

As for the other listbox selection issue that causes the listbox to freeze
up, I have not only attempted with the class module, but also without the
class module and still get the same behavior. As a matter of fact, I did it
without the class module first cause I had to learn the specific behavior of
the listbox quite thoroughly [with it's selection mode set to "Extended"] in
order to even properly address it via a class module. I have to store in
the class module what items are selected, if any, on the listbox as it takes
place. Knowing users, I had to test for any combination of keyboard and
mouse usage, and that's how I found the freezing up patterns. Sad thing
about it, on one of the two lockup patterns, it doesn't happen often enough
to be able to find the specific issue, but yet, it still happens often
enough to know there is obvoiusly something going on.

--
Ronald R. Dodge, Jr.
Production Statistician/Programmer
Master MOUS 2000

"Albert D.Kallal" <PleaseNOOOsPAMmkallal@xxxxxxx> wrote in message
news:O0ihc4OiGHA.4140@xxxxxxxxxxxxxxxxxxxxxxx
Issue 1: Error checking isn't user friendly (Very Critical)

The problem is much your design approach. You always have two choices when
choosing a tool
to develop software:


change your designs to fit the tool
or
change your tool.

I would be quite pretentious of me to post in a VB6 newsgroup,and complain
that
you don't have inheritance or all of the OO features. Or, how about
something
as
simple as bound data forms that we have in ms-access? VB6 is NOT going
to get bound forms! It is just not going to happen. As much as you
would like the universe to move to "your" view of things, a major change
in
the event model and change to ms-access is not likely.

To be fair, it would be nice to be able to turn of validation. However,
you
also have to turn off the before update event. And, even further, you
might
even have to turn off a for more events. So, *just* turning off validation
would not work, since we do have the before update event also (this is
likely were *most* of your validation code was/goes anyway..right?).

Not much you can do here:


Either you have to change your designs, or change your tool.

If you conclude that you can't get the level of user friendly with bound
controls, then you simply have to dump ms-access.

Now that you spend the time (or perhaps a better word wasted the time)
moving to un-bound controls, then you really receive no benefits of using
ms-access. VB6 and other tools are *optimized* with wizards etc to
help make up for the "lack" of bound controls. If you extensively use
un-bound forms and controls in ms-access, you loose all of the advantages
from a cost point of view, but receive NONE of the tools that other
systems
have to make up for the lack of bound form. It is the worst of both
worlds,
and you are spending money that your company could give to the poor, or
for use in helping other people (or, just simply not throwing $$ down the
drain).

However, it is fair to ask what kind of workarounds, and what approaches
do
work in ms-access.

I have two suggestions that will work quite well in ms-access.

Suggestion #1

Keep bound controls, and move the validation code to the forms before
update event. This of course means that you loose a field by field
validation.
However, the simple solution in that case is to use the controls before
update event and have that validation code in there also. BUT DO NOT
USE THE CANCEL feature of the before update.. This means that a user
receives a message that the control is required (or not setup to whatever
value etc etc etc) but is ALLOWED to continue to move on in the form.

In fact, from a user friendly point of view, this approach is BETTER then
forcing the user to STAY on control, or use some quit button on the form!
You see, in your "quit" link example, that ONLY works if the users hits
the
quit command. What happens if they need to go back a few fields and edit
something but are now are STUCK on the validation for the current
control.
This just simply causes HUGE user frustration. If you want a debate on
user friendlness, then you DO NOT want to force a user to be stick on that
control, and ONLY give them the option to quite. Hence, move the *final*
valuation that prevents a record save, or form exit of the form to the
forms
before update event.

Further, if the user does NOT ENTER INTO THAT REQUIRED FIELD/control,
, then you STILL MUST HAVE form's level validation code!!!. In other
words,
if you have some control on the form that is required, or MUST meet some
special
value, but the user DOES NOT EDIT that control, THEN YOU MUST still have
that forms
level validation code anyway!! So, my point here that even when you used
control level
validation (I am talking about before update + cancel feature of a
control),
you STILL had to write a forms level validation routine anyway to catch
those
controls/fields that were not edited by the user!!!

In conclusion, you at *always* had to write a forms level validation
routine in the forms before update event routine for user friendly code.
So, for each control, give the user a error message/warning..but
simply do not use the cancel feature of that control. Simply reduce user
frustration, and allow the user to continue to work their way through the
form. As I said, 9 out of 10 users prefer this, and they do NOT
like get being stuck on a particular control, but can continue to move to,
and edit
other controls on the screen. With the VB6 approach, you can't move to
other
controls but must ONLY be faced with a cancel, or quit that disables the
validation.

The above approach I outline is not only MORE user friendly since they
don't
get stick
in the middle of a form, it ALSO LESS CODE then what you wind up with in
VB6, or your wrong
choice of un-bound controls. This approach is friendly, and works very
well
with bound forms +
controls.

So, In above, we are STILL giving the user a message when they enter some
value in a control
that is incorrect, but they are allowed to continue on to the next
control...and
users prefer this. And, thus our *final* validation is in the forms before
update catches this stuff (and as mentioned, you *always* had to write
that
code anyway!).


Suggestion #2
Move your quite command, or other commands that "must skip" the
validation to a toolbar, or a menu bar. Again, this is more "widows" like,
interface, and the added benefit is that menu commands DO NOT TRIGGER
the before update event, or even any of
the last focus events. I used this approach in place of #1, and also that
of
validation in VB6. So, I have used menu bars to get around the fact that
we
don't have the ability to turn off the before update events, or validation
code (by the way, we need to turn off BOTH events to allow the equivalent
features of VB6 -- and, in thinking about this, perhaps a few more vents.
So,
turning on the events in ms-access is FAR MORE complex problem then the
very simple validation that you have in vb6).

Again, of course, if you are not willing to modify you designs to the
above,
then I can't help you. However, in no way can I see that above
two choices is ANY LESS user-friendly then what you done (expect that you
wasted huge $$ moving to un-bound controls).

You can't design your appcation with one architecture in mind, and then
hope
that FoxPro, or ms-access, or power-builder will work the way you want
(they
don't by the way...YOU have to change your design...not the other way
around). Learning to design within in the bounds of the tool you are
using is the difference between developers that understand the subtleties
of
developing..and those that don't...

As a number of others stated, you can very well reach a *VERY* high degree
of validation AND ALSO reach a level of user friendly nesss. As
mentioned,
the above two suggestions are of many possible approaches. I too also have
some VERY high degree of demands for checking and validations,but also
that
of
user friendly at the same time..

the EditMode property no longer
has it's enum value that it suppose to have. This more or less has
rendered
DAO programming useless.

I not seen this error pointed out. You might want to elaborate this with a
new question/post


Issue 3: ADO programming doesn't allow for DynamicCursor Keyset against
a
Jet Engine (Critical)

ADO, and JET are only pieces used by ms-access, or VB.net, or VB6. Are you
saying the above ONLY applies to ms-access, or are you talking that the
general use of ADO and JET has this problem? (this would thus not be a
problem special to ms-access, but to ado in general, and presumably, then
also
this problem would exist with .net also???)

Issue 4: Listbox properties doesn't update until it has lost focus when
it's allowed to have multiple selections (Moderate)

Hum, that don't sound right. Try using the after update event, and use the
itemsselect collection...

Place the following code in the after update event of a multi-select list
box....


MsgBox Me.MyListBoxName.ItemsSelected.Count

Try the above. ItemsSelected should be updated correctly...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@xxxxxxx
http://www.members.shaw.ca/AlbertKallal






.

Loading