The Dude said:
Hello everyone,
and happy new year to all of you! May it bring you good health and enough
patience...
I had a quick question regarding forms: I now develop the forms unbound,
with ADO commands, because I find it much more convenient to trap errors
and
events. Yet, I realized that, in some needed cases, the unbound form is
not
refreshed (obviously...) hence my questions:
- If I want to update the form's data, can I use a Timer event to requery
the ADO command with a "Where" based on the current record?
I'm pretty much of the belief that if you have to start using timer events
to refresh a form, then it's not the best approach. There is a significant
number of issues that come up if you fire off a refresh event via a timer,
this is especially so when the users actually doing data entry or working on
the form. That user might be part way typing some characters in a text box
and "bam!", off fires a timer event that causes the form to refresh. I
suppose that this is a display only form, then some type of timer event
refreshing is a reasonable solution here.
Another common scenario is in which a multi-user has several uses entering
new records into a form. If one of the people just finished up talking on
the phone with a customer, and five minutes later that customer phones back
and you get a different operator in the building, then that new operator
will NOT be able to find/search that new customer if they been working in
that same customer form. In other words, you could fire some timer event to
re-load the form. However, the simple soliton here is to use what we call
"social engineering" to ELIMONATE this problem.
In other words we are not going to use code to solve this problem, but we
simply change the way the users actually use the application to prevent the
problem from occurring in the first place! A very simple solution in this
case is to simply build a search form that encourages users to search for
the customer first before they try to add the customer. If they don't find
what they're looking for, then they can hit a convenient button that allows
them to add ONE customer record. The beauty of this approach is that when
done with the customer they close the form and the customer data is
instantly save to the hard drive. Better yet the The data entry operator is
now ready to do battle with the next customer.
The above approach means that any other person using the application can
receive a phone call and query or bring up information about that particular
customer. Notice how no strange coding techniques are used, no re-query of
the forms recordset is needed. So, by simply changing the way users work
with the application we solve a lot of problems here. There's also
significantly more benefits to this type of social engineering and I explain
these in the following article where I explain how one to set up a search
form in MS access here:
http://www.members.shaw.ca/AlbertKallal/Search/index.html
To make a long story short, the above simply shows that the solution was not
to set up a timer and have the form requery each time as users are doing
data entry, but in fact to simply forced users to close and save their work
when they're done with one customer. This philosophy and software design
approach also works well when you make the leap to web based type software
in which a web browser can't even begin to be refreshed automatically over
the web with all kinds of users running a web browser all over the world.
So the solution here is not to use more technology like a timer event, but
to simplified the overall design and force users to interact with your
software in a particular manner.
- Should I rather use bound forms (and use the weird Cancel BeforeSave
thinggy)?
I really do strongly suggest the above approach. Your approach means that
you have to open up a ado connection, pull the data into a record set, bind
the record set to the form (or even worse perhaps write code that places
values into un-bound controls on the given form). When user is done, then
you are forced to reverse this whole complex process and push the data back
via code. You then also likey have to kill your reocdset, and possibly even
close your connection.
Contrast to all of the above process to that of simply using a bound form
that allows you to edit the data, and then saves it for you. In fact I'm
betting this approach will likely result in less bandwidth requirements then
all those amounts of code that creates a recodset, then pulls data, and then
has to push it back to the file.
Of course the other significant issue on this problem is that MS access was
never designed around the concept of un-bound forms. At least when you're
working in .net or even vb6 they had all kinds of wizards and data
connection controls that allowed you to alleviate much of the grunt work to
design and build unbound forms.
When you move to MS access you have no wizards and no support tools to allow
you to build those unbound forms. Even worse than that is we have probably a
5 to 10 times the amount of data events in our access forms (over that of
vb6 forms). For example in MS access we have both on on opened, and on load
event where is in vb6 you only have one event. Furthermore we have events
like before update with a cancel option, and then after that occurs we have
an after update event. There is a zillion number of data verification type
events in access forms that you have to give up when you go un-bound.
So when you throw in the fact that we have no wizards and support for
unbound forms, and worse when you go unbound you loose more than 50% of the
forms data capture events, you're really walking into the worst of both
worlds. The end result as you simply increase your development costs by
substantial amount, and you actually wind up having a less flexible
application for a given amount of work.
It is my belief and philosophical approach that if you're designs require
unbound forms, then you should use a different tool that has support for the
unbound type model such as .net, or vb6.
I will be the first admit that there's also some give and take here. There's
no question that there are some advantages to unbound forms, but you wind up
sacrificing far too much when you adopt that type of programming model in MS
access. So, if you really are going to adopt unbound forms, then you should
use a set of development tools that supports this type of development, and
unfortunately MS access does very poorly in this regards.
MS access gains most of its productivity advantages over other development
systems because of its bound forms object model. It is this very feature
that allows us to develop amazing applications in half or even 1/3 or 1/5
the time of competing development platforms, But to get the Productivity and
gains we have to stick to the "access way" of doing things, and that's tends
to be bound forms...
