Check if a form has changed since it was last saved

[bunter]

I am developing an application using an ms access front end and a
visual foxpro 8 backend. The application uses adodb connections to add/
update/edit data in the back end database. When a user clicks the
save button on main data capture form the user input is
programmatically inserted into a new record or an existing record is
updated by creating SQL INSERT INTO or UPDATE statements on the fly.

I was wondering if anyone knew of an efficient way of coding a
mechanism to check if the form had changed since the save button was
last pressed. I guess this can be achieved by capturing the On Change
event of each control on the form but I was hoping there was an easier
way?? (50+ controls on form) I have tried the forms After Update event
but this only seems to occur if the form is bound directly to a
recordset.

I am self taught in vba for you'll have to excuse me if this is a
stupid question.

Thanks

Mike

p.s I have just realised by the time I have written this I could have
copied and pasted some code behind the On Change event of each
control....maybe the answer to this question save someone else some
work!?

 

[Albert D. Kallal]

I am developing an application using an ms access front end and a
visual foxpro 8 backend. The application uses adodb connections to add/
update/edit data in the back end database. When a user clicks the
save button on main data capture form the user input is
programmatically inserted into a new record or an existing record is
updated by creating SQL INSERT INTO or UPDATE statements on the fly.

The above tells me you using the wrong tool. The ms-access object model
REALLY REALLY REALLY falls down when you using un-bound forms. You
would be MUCH better to use vb here, as it has TONS of wizards, and
data controls that allow you to build forms with a data connections.

in ms-access, you don't have all those cool wizards and development aids
because you DO NOT need them when you use bounds forms.

The fact that your asking for a way to know if the form been updated shows
exactly this point.

if you use a bound form, then the dirty flag will be set = true.

In code, you can simply go:

if me.Dirty = True then
...... form been updated...

I guess this can be achieved by capturing the On Change
event of each control on the form

No, even if using bound forms, you want to use the controls after update
event. The on-change fires for every single keypress, and you don't know at
that point the user actually changed the value. In fact, they might go
edit->undo. You REALLY want to use the after update event of the control,
since it will ONLY fire when value is actually changed and the value is
going to committed to the table (however, you can still do verification, and
these values are NOT yet written to the table). So, use after update, and it
will ONLY fire when the user is done editing the control. On-change the
WRONG event. (and, father it means you likely have to use the .text property
of a control, and again, you don't want to do that either....use the .value
property of controls).

but I was hoping there was an easier
way??

Well, is there any reason why you can't using linked tables to the back end?
I would just link the form to the tables direct, and you done!!! It would
take zero code here. If you do use a linked odbc table, and bind the form to
that table, then you not need to write ONE line of code,or bother checking
if a control had its value changed.

I am self taught in vba for you'll have to excuse me if this is a
stupid question.

No, not stupid question at all. However, you can't cut steak with a fork.
You can eat the steak, but the approach is just wrong.

Ms-access is very much the wrong tool, and thus there not a trivial, or
simple answer to your approach. You CAN cut a steak with a fork, but usually
really a bad process....

You can avoid having to put in code for each controls after update event by
using a public function, and placing it in the actual property sheet for the
control (and, if you highlight all 50 controls,...you only have to type the
function name once -- however, this kind advice I given you is really only
giving you more rope in which you hang yourself around the un-bound form
apporach that don't work well at all in ms-access).

 

[mikeh]

The above tells me you using the wrong tool. The ms-access object model

REALLY REALLY REALLY falls down when you using un-bound forms. You
would be MUCH better to use vb here, as it has TONS of wizards, and
data controls that allow you to build forms with a data connections.

in ms-access, you don't have all those cool wizards and development aids
because you DO NOT need them when you use bounds forms.

The fact that your asking for a way to know if the form been updated shows
exactly this point.

if you use a bound form, then the dirty flag will be set = true.

In code, you can simply go:

if me.Dirty = True then
..... form been updated...

I totally agree this is not an ideal solution, unfortunately the
organisation I work for will not allow me to develop applications on
anything other than the Microsoft office type environment. The
application I am developing is to replace the current database which
is ms access front end and back end using link tables - this database
is extremely slow even and unstable though I think it is quite well
designed and is compacted regularly. It seems access is unable to
support the 30-50 users this application will be used co-currently by,
having used Fox Pro databases in the past I am more confident of its
ability to hold up with this many users (many more than the database
was originally intended)

In an ideal world I would have Web based application served by a SQL
server, however what I can only describe as "red tape" means this
would have to be done by external suppliers who have an awful habit of
charging significant amounts of money!

I am really using the development as an opportunity to demonstrate
what is possible, I am hoping that the organisation will realise the
benefits and will agree to fund a scalable/secure/complete solution.

Happy to take any board any suggestions for alternative development
tools, given the current constraints.

No, even if using bound forms, you want to use the controls after update
event. The on-change fires for every single keypress, and you don't know at
that point the user actually changed the value. In fact, they might go
edit->undo. You REALLY want to use the after update event of the control,
since it will ONLY fire when value is actually changed and the value is
going to committed to the table (however, you can still do verification, and
these values are NOT yet written to the table). So, use after update, and it
will ONLY fire when the user is done editing the control. On-change the
WRONG event. (and, father it means you likely have to use the .text property
of a control, and again, you don't want to do that either....use the .value
property of controls).

Understood, thanks

Well, is there any reason why you can't using linked tables to the back end?
I would just link the form to the tables direct, and you done!!! It would
take zero code here. If you do use a linked odbc table, and bind the form to
that table, then you not need to write ONE line of code,or bother checking
if a control had its value changed.

There were two reasons I did not consider odbc:

1) As I understood it the visual foxpro odbc drivers have not been
updated since foxpro 6, version 8 contains new features which the obdc
driver does not recognise (such as auto-increment). If anyone know
differently or knows of a driver that will work with vfp 8 features
please let me know.

2) In terms of performance I always thought that using adodb
connections would be more efficient than odbc connections. Not quite
sure why I thought this....How does adodb compare with odbc in terms
of performance?

Many thanks for taking the time to respond, your help is much
appreciated.

Regards

Mike

 

[Albert D. Kallal]

support the 30-50 users this application will be used co-currently by,
having used Fox Pro databases in the past I am more confident of its
ability to hold up with this many users (many more than the database
was originally intended)

I *really* doubt that dropping native access to a mdb back end
and going to a fox back end is going to be more reliable. You
adding a complex ADO + fox driver "software" layer here that
you did not have before. and, ms-access is still going to be
loading the native "JET" data engine it needs anyway.

So, you adding layers of software here.

If anything, you wind up with a LESS stable
system. Now, your system will have to open table, read table (perhaps
close table at this point). Put data into form. After we edit, we
then take data from form, re-open table, re-execute sql to write back
to that table with sql updates. You more then doubled the steps and
work then just letting access update the table direct like it does
now.

Further, this approach could wind up using MORE bandwidth then
letting ms-access do a NATIVE update to a mdb file. Remember, in the case of
letting ms-access do the update, there is NO odbc, no
extra ADO layer, and I am quite sure that NO sql statements need be
parsed to make the update. Note that just to parse the sql, the sql
has to be *checked* against legal fields in the back end.

So, you likely use LESS bandwidth by leaving the tables open and
using native mdb file for the back end. All of your open/close,
read/load data into form, and then write back out + open/close
connection stuff can actually cause MORE bandwidth to be used
then just letting ms-access update a record it just read
into a form.

Ms-access is able to raw update that table, and,
access is NOT using odbc to connect to the
back end...it a native update system.

I do not see ANY reasonable argument here that moving the back end
to a Fox db, adding additional layers of code and ado.
I don't think you gain anything here. And, the argument that a
Fox back end is going to be more stable is weak.
(all those who developed in FoxPro, raise
your had if you ever had a corrupted index (everyone in the room will
raise their hand)).

So, I really doubt you going to gain additional
stability, and I doubt you gain ANY performance here. The ONLY reason
why you might gain performance is because your FORCING your design
to only load the one record to the form, and you should do that
in ms-access anyway.

With bound tables in ms-access, you don't need to write any update
code, and you seem VERY sensitive to time + cost issues here, and yet
are using up large amounts of developer time to write a bunch of update
code? You could use that time to migrate to sql server.

You not going to gain any more stability or performance here in my IMHO.

In an ideal world I would have Web based application served by a SQL
server, however what I can only describe as "red tape" means this
would have to be done by external suppliers who have an awful habit of
charging significant amounts of money!

You do realize there is at least 2, perhaps 4 free versions of sql server
available from Microsoft? The time and cost to re-write this stuff
to use ado, fox is likely the same for migration to sql server.

Happy to take any board any suggestions for alternative development
tools, given the current constraints.

I would first check if your existing application is correctly setup.

As long as you do the following, you should get good performance, and
stability:


1) split the databae (that likely a given on your part)

2) always distribute a mde to users.

3) NEVER open up a form bound direct to a table unless you use a where
clause.
(so, bound forms are ok, but you MUST restrict the records loaded. With
a small table of 100,000 records, the time to load the form will be same for
a table of 1 record *if* you respect this approach. Here is quick example of
a typical way to "restrict" records (eg: ask the user BEFORE you load the
form

http://www.members.shaw.ca/AlbertKallal/Search/index.html

This also means that as a general rule you not need, nor allow the
navigation buttons
in he form because you load only one record.

Further, once you split the database, and reduced the number of records that
flow to a form, then you 90% of the way to having a application is VERY
close to a good setup for running the data from sql server. So, assuming you
do eventually get sql server, then you simply move your back end data from
the mdb file to sql server, and then link your front end tables to sql
server. At this point, 90%, or more of your code and forms will work "as is"
WITHOUT having to write ANY code. so, you simply tweak that last 10% to work
with sql server...

There were two reasons I did not consider odbc:

1) As I understood it the visual foxpro odbc drivers have not been
updated since foxpro 6, version 8 contains new features which the obdc
driver does not recognise (such as auto-increment). If anyone know
differently or knows of a driver that will work with vfp 8 features
please let me know.

I not heard the above. I would assume that the latest drivers for
fox would support the basic functions you speak of. Further, any
reason why you can't used a supported version of fox for the back
end.

2) In terms of performance I always thought that using adodb
connections would be more efficient than odbc connections. Not quite
sure why I thought this....How does adodb compare with odbc in terms
of performance?

In general, there not much difference. This is *especially* so when using
a file share, and NOT a server (and, that is our case until you spring
for sql server). However, with ms-access, you not using
a odbc layer, you not using a ADO layer, you using native JET to the mdb.
Now, your proposing to throw in ado layer that you did have before, and
also the FoxPro driver which also has to be loaded and run in addition to
ms-access. Lots and lots of more
layers of software you adding here.

I don't think you gain a thing in terms of stability, or performance
if you have a good desing now...

 

[Albert D. Kallal]

I going to add one more comment:

Looking to run 40, or 50 is pushing ms-access.

There are reports that when everything is JUST right, some have manage to
push ms-access to 100 users, but that is in a absolute "ideal" case. And, it
was in a era when we did not load up pc's with a zillion other applications.

And, it should be pointed out that even Microsoft rated the JET database
engine for 50 users (but, that was *before* they had sql server in their
stable (and, before Unicode was used in JET).

Ms-access can be run with 50 users, but I think 50 is really pushing it....

30 users? Hum, sure ok, but only if you have a VERY stable environment (no
network problems, and NO pc's that freeze or get rebooted by users).

And, yes, I do think the FoxPro database format is somewhat more robust then
is ms-access (but, not by much, and it not going to get you a lot more users
IMHO).

Given that versions of sql server are available for free that can easily
handle 50 users, I don't see the cost benefit to try and use a file share
with so many users.