Putting code in a subform?

[Maury Markowitz]

I have a subform that I'd like to include a few basic methods. However when I
attempt to call it with me.(name of subform).methodName, it fails and states
that it doesn't support that method.

I'm guessing this is because the subform only support those four basic
updating methods (enter, exit, etc). Is there a trick to calling my own
methods in this way?

Maury

 

[Allen Browne]

Try:
Call Me.Sub1.Form.Test_Click

Alternatively:
Call Form_Sub1.Test_Click

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Maury Markowitz]

Alternatively:
Call Form_Sub1.Test_Click

Thanks Allan, this one worked fine. I was also able to use this to talk to
the fields in the subform, some of which had to be cleared out before calling
the sub in question.

It only does this for the first row in the subform, but I suppose that's not
too surprising. Is there a method to talk to all of the records in the
subform?

Maury

 

[Albert D. Kallal]

Is there a method to talk to all of the records in the
subform?

The fact of a sub-form, a normal form, or a datasheet, or even just a
regular plain form makes no difference here. When you bring up a regular
form, it is sitting on ONE particular record. The user might choose to
"move" to another record. This movement is possible in a sub-form, a regular
form, a continues for etc. When you are looking a form, you are looking at
ONE record. Even with a sub-form, you are at ONE record. You might choose to
move...but you can also choose to move in the "main" form part to another
record also. So, conceptually, there is no difference here.

So, really, question we are asking is:

Is there a method to talk to all records in a form?

yes, you can "process", or work with all the records in a form (or in your
case a sub-form) by using the recordsetclone.

So, now that we know we can "talk" to all the records...the next question
would be what do you want to do to those records?

To display a total..you can work with "all" the records in the sub-form. A
control with:

=(sum([PayAmount]))

The above control data source expression would display the total of the
payamount field at the bottom of the form (you have to place this in the
sub-form footer to make this work).

Often, to "process" all those child records, might just use some sql code.
If you need a "total", then perhaps you can just place a control in the
sub-forms footer. But, you can always resort to using standard code:

dim strSql as string

strSql = "update set PayAmount = 0 where main_id = " & me.id
currentdb.execute strSql

The above code (likely placed behind a button on the main form) would set
all payamount values in the sub-form = 0, and this is based on the main key
id of "id". So, in above, the field "main_id" was used in the sub-form to
relate back to the main form. So, the above is a possible way to execute, or
work with all related records. The above example of course does NOT rely on
the sub-from at all..but just the relation used.

Now, you CAN deal with, and use all reocrds (I mentioned the recordset
clone). So, I could replace the above code with:

dim rstRecs as dao.recordset

set rstRecs = me.mySubForm.Form.RecordSetClone

do while rstRecs.EOF = false
rstRecs.Edit
rstReces!PayAmount = 0
rstRecs.Update
rstRecs.MoveNext
loop

set rstRecs = nothing

The above is thus a basic processing loop. And, of course, for any form, you
can work with "all" records. (and we assumed all code is in the main form so
far). Hence, to use the reocrdset of a main form, we can go:

set rstRecs = me.RecordSetClone

So, in fact, you can always work with the "set" of records that belong to a
form....the fact of this being a sub-form does NOT change this..but of
course you do have to use the correct syntax to reference the sub-form
control on the form.

 

[Maury Markowitz]

yes, you can "process", or work with all the records in a form (or in your
case a sub-form) by using the recordsetclone.

Oh, for sure, but that's not a very clean method considering the tremendous
paradigm shift compared to binding a field to the current record. Since
subforms define a linked set of data, it would make a lot of sense to be able
to bind a control on the form to _all_ the rows of a subform (ie,
[Form_mySubform]!ShippingCost).

Looping over recordsetclone is fine, but as always, I have had tremendous
problems getting them to work. My subform is basically "find all the orders
that were grouped with this one for shipping", which includes the original
order as well. Since the shipping costs are global to all the orders,
changing it for one means changing it for all of them (via a calculation, the
main form has a "shipping per dollar" field, the subform shows the actual
shipping total). So I wrote the code to do this, but since "me" is being
changed in the subform, I cannot get the thing to ever save -- either the
main form or subform will complain that the other one changed the data out
from under them.

The above code (likely placed behind a button on the main form) would set
all payamount values in the sub-form = 0, and this is based on the main key
id of "id".

This is the approach I took, but as I mentioned, it doesn't seem very easy
to get it to work. It' particularilly annoying because the fields in the form
and subform do NOT overlap, so in theory there shouldn't be a problem. Of
course the system isn't smart enough to know this, nor do I really expect it
to be.

Maybe I can fix this by turning off the locking in one of the forms? Any
suggestions here?

rstRecs.Edit

What EXACTLY does this do? The docs don't seem very obvious, and it seems
to do the same thing if I leave this line out.

Maury

 

[Albert D. Kallal]

Oh, for sure, but that's not a very clean method considering the
tremendous
paradigm shift compared to binding a field to the current record. Since
subforms define a linked set of data, it would make a lot of sense to be
able
to bind a control on the form to _all_ the rows of a subform (ie,
[Form_mySubform]!ShippingCost).

In the above..you mean that shipping cost comes from the "main" form? Or, do
you mean an actual field in the sub-form? If you mean sub-form, then which
record would this refer to? I mean, if there is going to be more then one
value in this "set" of data, then a reocrdset is about the best collection
of data you could work with (perhaps some concept of a collection could be
created here..but you always would need to be able to selectively
edit/update, and work with ONE reocrd from that set. You *might* need to
work with all at the same time, but you also need to be able to work with
each individual record. So, we got sql, and what....3 lines of code can
operate on the set of data??? I think that is about as good as it gets. And,
we need to worry about things like referential integrity, default values set
in the table etc. There is a LOT of stuff here, and a reocrfdset has all of
this ability built in here. So, I can't really come up with anything better
then reocrdset (or recordsetclone) in this particular case.

If you need ONE value to refers to "all" records, then you got a data design
issue of normalizing the data. Any repeating data should be removed, and
moved up to the shipping order reocrd along with "assembled" date, employee
who assembled the order etc. So any ONE piece of data that is the same for
all records should be in the shipping order record (or whatever you called
your "main" record). If you data is normalized (no repeating data), then you
likely could do this with little, or no code at all, since changing one
value would apply to all child records.

And, you can most certainly can bind a "expression" to a text box control in
the sub-form that resolves to a value on the main form. This expression will
not be editable in the sub form (but it is in the main form). There is
several ways to bind a expression this way.

Anyway, a simple sql "update set somevalue = " is darn good when working
with a set of data. If you don't want to loop through a reocrdset, then just
use one sql statement..and that gets you for magic statement that works on
all fields...and, you can do this with two lines of code.....

me.Refresh
currentdb.Execute "update tblChildReocrds set someField = someValue where
main_id = " & me.id

The above will thus work on all the child records...

So I wrote the code to do this, but since "me" is being
changed in the subform, I cannot get the thing to ever save -- either the
main form or subform will complain that the other one changed the data out
from under them.

The problem here is that you may have data that has been changed on the
form, but has pending writes, and has not yet be written back to disk.
Virtually anytime I am about to "run" some code that *might* modify data
that includes the current data I am looking at, you need to commit current
pending writes on the form BEFORE you do this (I think this is common sense,
since what happens if another user, or some code modifies the current
records...you obviously got a conflict). In fact, even when I have one form
launch another form, I force out pending writes before launching the form.
That way, if the pc freezes, or is turned off, at least my data has been
written to disk as opposed to having 3, 4 or even more forms open that have
pending writes.

So, as a general rule, if you are writing code that modifies data, and some
of that data is currently (or might be) attached to a form with pending
writes, it goes without saying you need to force those writes BEFORE running
that update code.

This is the approach I took, but as I mentioned, it doesn't seem very
easy
to get it to work.

As mentioned, start to conceptually think about the issue of a reocrd being
editing (dirty) with pending writes, and you trying to run some update code
at the same time....who will get the last disk write? Hence, the simple
solution is simple to write out pending updates on your form to disk.

Maybe I can fix this by turning off the locking in one of the forms? Any
suggestions here?

Assuming the button code is run in the main form, then all you have to do is
go:

me.Refresh

A lot of developers have suggested to me to use:

if me.Dirty = True then
me.Dirty = false
end if

The above is considered better, as you might have a form with MANY records
as the data source, and me.refresh can case more (and unnecessary) network
traffic. However, since most of my designs reocrd forms to ONE record (with
no navigation for the main record), then this is not a problem for me..and I
can lazy use me.Refresh. However, I might as well start you out with good
habits...and thus you can use the me.Dirty = False to force a disk write
(they both do...but the me.Dirty can be more efficient).

What EXACTLY does this do? The docs don't seem very obvious, and it seems
to do the same thing if I leave this line out.

My sample code snips will NOT work if you leave the above out. You have to
execute the "edit" method of a dao.reocrdset.

MAKE SURE you are qualifying your recordset type here, since if you use
ADO..then you do NOT need the edit method. However, if you use DAO..then you
most certainly do need the .edit

So, if you got record locking turned on, then executing the edit method will
lock the record (and, you can test at that point if someone else has the
record locked also..but that is issue for another day).

So, you either got:

Dim rst As ADODB.Recordset
or
Dim rst As DAO.RecordSet

Depending on which data object model you use..there is a good deal of
different syntax..and with ADO..you don't have to use the .edit method first
(but, it does exist for developers and ease of conversion. So, in your case,
I betting you are using ADO..and the .edit method is NOT required, but there
for compatibility issues).

So, right before you run the code, simple force a disk write.

me.Refresh
.....you update code here.....

If you running the code in the sub-form, then ms-access ALWAYS forces a disk
write of the main form, and you thus only need to force a disk write of the
current record in the sub form, so simple disk write idea should also work
just fine (and, if we did have some magical way of referring to all values
in the sub-form with one expression, I still at a loss as to how editing,
record locking, and a number of other things would work!!

 

[Maury Markowitz]

In the above..you mean that shipping cost comes from the "main" form?

Well the number is calculated that way. This is a bit confusing I guess, my
explaination wasn't very clear.

The main form has a field called "shipping per pound". Each line item in the
subform has a weight. There is another field on every line in the subform
that is "shipping for this item". The amount is the weight x the shipping per
pound on the main form.

Wait, I know what you're going to say -- just use a formula. This doesn't
really work in this case though, for a couple of reasons:

1) the number can be overriden by hand for special cases, and in these cases
it is still vital to remember the number typed into the main form.

2) each line in the subform is complete on it's own as well, so if the user
types into that field on the main form, that number has to be copied down
into all the rows.

3) in reality the formula is quite complex, and requires code to run --
currently hooked up to the afterUpdate on the field in question.

And that's where I get the problem. After the use changes the value I loop
over the records in the subform and calculate the new values and write out
the results (which is already bad enough, see below for my reasons) -- I call
this "copy down". But at that point the screen does NOT have the right values
on it, the data has been changed out from under it.

So the normal solution here would be to call .refresh. Ahhh, but here's the
problem. If someone changed anything in the subform, the refresh tries to
flush the change and you get the conflicting writes error. There seems to be
no obvious way to avoid this, it seems there's always some order of
operations that will cause a problem. Either the subform will flush changes
that cause the main form to complain, or the main form will flush changes
that the subform won't like.

Anyway, a simple sql "update set somevalue = " is darn good when working
with a set of data.

Sure, but like I said the problem is the updates. With a recordset you're
basically doing SQL on the records "behind the scenes", and that's when
Access starts getting really confused.

If there was a way to loop through the form items IN THE FORM (as opposed to
records in the database which is what a recordset is), then the form would be
directly updated and there would be no sync problems.

Ideally I'd like to be able to do something like
Me!TheSubform!Form!Rows(x)!shippingAmount. Is there some syntax like this?

Virtually anytime I am about to "run" some code that *might* modify data
that includes the current data I am looking at, you need to commit current
pending writes on the form BEFORE you do this

And this really violates the whole UI of my app. I want the user to be able
to hit my Cancel button and have all changes disappear. As soon as anyone
writes out pending changes, this becomes a much harder problem, notably due
to the fact that my app logs all writes for change tracking (compliance). In
fact I want no changes at all until they hit the Save button -- in your
example of a power failure, they _expect_ there to be no changes to the data.

MAKE SURE you are qualifying your recordset type here, since if you use
ADO..then you do NOT need the edit method. However, if you use DAO..then you
most certainly do need the .edit

Ahhh, that explains it, I'm ADO.

Maury

 

[Albert D. Kallal]

3) in reality the formula is quite complex, and requires code to run --
currently hooked up to the afterUpdate on the field in question.

And that's where I get the problem. After the use changes the value I loop
over the records in the subform and calculate the new values and write out
the results

If you are talking the "main" form text box control, then in your after
update event, you go:

me.Refresh ' force the main form write.

......run your code to update sub-form

at this point, you likely should use a
me.MySubForm.Form.Requery

The above however will loose your cursor position in the sub-form. If you
need to keep the position in the sub-form, but show updates, then you should
be able to use me.MySubForm.From.Refresh.

The above should NOT give you an error of write conflicts.

(which is already bad enough, see below for my reasons) -- I call
this "copy down". But at that point the screen does NOT have the right
values
on it, the data has been changed out from under it.

Hum, using the above should work for you.

So the normal solution here would be to call .refresh. Ahhh, but here's
the
problem. If someone changed anything in the subform, the refresh tries to
flush the change and you get the conflicting writes error.

No, you can not get back to the main form without a write being triggered
here in the sub-form (ms-access does this for you). So, the merge fact to
changing the focus to the main form should trigger a write here. (perhaps
you main form is based on the same record source as the sub-form, and that
I( would consider changing....).

There seems to be
no obvious way to avoid this, it seems there's always some order of
operations that will cause a problem. Either the subform will flush
changes
that cause the main form to complain, or the main form will flush changes
that the subform won't like.

Hum, as mentioned, when the focus moves from main to sub, the main is
written to disk. And, the reverse is also true. Using the above me.refresh,
and then running your code should work. I am not sure what I am missing
here..but I done this for years....

If there was a way to loop through the form items IN THE FORM (as opposed
to
records in the database which is what a recordset is), then the form would
be
directly updated and there would be no sync problems.

The problem is that is each detail line is written to disk when you navigate
in the sub-form. That data is not held in-memory! The form records ARE the
table records...and your trying to distinguish them is a problem. I
certainly agree it would be really nice if we could wrap a form (and
sub-forms) records into a transaction, and edit them at will, and then
either commit, or not commit changes. We don't have this ability..and I kind
of wish we did. However, since we don't have this ability, then we have to
work the way ms-access works. A "copy" of the data is NOT being made here.

Ideally I'd like to be able to do something like
Me!TheSubform!Form!Rows(x)!shippingAmount. Is there some syntax like this?

The above would not in fact fix your problem at all. You might get a
different way of referring the data..but that is NOT your problem!. Your
problem is one of committed records. I mean, if a copy of the data was being
made here, then your original problem would not exist if you ONLY edited the
copy of the data). So, some type of new reference here will not fix
anything. You got a issue of write commits here. Any writes, or cursor
movement from one sub-form record to another forces a disk write. There is
no "buffer" of the list of records, and you must work with ONE record at a
time, and EACH record MUST be written to disk before you move to the next.

So, based on this knowledge, then in the case when you run some update code,
then the ONLY record you *should* need to force to disk is the current
record that has the focus (in your case, the code is being triggered in the
main form..and thus all you need is to commit the main record).

And this really violates the whole UI of my app. I want the user to be
able
to hit my Cancel button and have all changes disappear. As soon as anyone
writes out pending changes, this becomes a much harder problem, notably
due
to the fact that my app logs all writes for change tracking (compliance).
In
fact I want no changes at all until they hit the Save button

Unfortunately, you can NOT use the concept of a save button when you got a
sub-form. With referential integrity , and a sub-from, you MUST generate the
parent key id first BEFORE you can add child records. As a result the
instant the cursor moves from the main form to the sub-form, the main form
is written to disk! How can your save button work with this? Users will be
very confused, since they will assume some kind of un-do is available..when
in fact it is not. Sure, if you don't use sub-forms at all, then your idea
of using a save button can work, but the instant you start using sub-forms,
then a save occurs by a cursor moving into the sub-form..and this effect
will render your save button concept useless.to no use.

-- in your

example of a power failure, they _expect_ there to be no changes to the
data.

No, in fact, if they entered a bunch of data and moved to other forms..they
expect that data is saved. My point is WHEN YOU move to another form..you
need to (and should) save the data. Sure...if you don't move to another
form...I agree with you. However, from both a user point of view, and a
coding point of view..if you leave a form to go to another..it don't make
sense to leave pending writes. Your problems are proof that not forcing
writes don't work well at all.

So, the real issue is that other forms, or other data, or other reports
may need this data. So, if for example you make a button to print the
current record on a form, then you have to force a disk write (assuming we
are using a report here). If you make a one- button click to email, or
better yet display the values of the current form in a report, then have to
force a disk write. The very fact of you not doing this is why you are
having
such trouble with your updates.

The concept of "save" to users in ms-access is implied..and users quickly
learn that save is not needed.

The way ms-access works in this regards really does force you to save your
data, and I would be the first to admit that this does tie your hands..and
does restrict your choices here....

 

[Maury Markowitz]

of using a save button can work, but the instant you start using sub-forms,
then a save occurs by a cursor moving into the sub-form..

So then this is the real problem. The "secret save" I'be been trying to
track down is simply a mouse click on the form. Geez.

Ok so then the question becomes much simpler - how do I turn this off?

I certainly don't expect to save a document simply by clicking on a
different one. I've always found this aspect of Access very odd. Now it's not
only odd, but working against me too.

The concept of "save" to users in ms-access is implied..and users quickly
learn that save is not needed.

This assumes you are working in an access-like world. I'm not, my app needs
to be a lot more like a normal desktop app. Ie, you open a form, do some
edits, and either save or cancel the edits.

I _cannot_ have database writes going out all the time. Period. It's
basically illegal in this case. Like cops will come and arest people. I need
absolute control over what and when the writes go out.

So basically unless I can trap out that "save on navigate", I have to chuck
the subform and re-implement.

Maury

 

[Albert D. Kallal]

So then this is the real problem. The "secret save" I'be been trying to
track down is simply a mouse click on the form. Geez.

Yes, and when you actually move a icon on your desktop, are you prompted to
save the location? You are not, but both the new position of the icon does
in fact cause a disk write, and the new location is saved to disk.

Can you imagine if you put a key in a car, and then turned it to the start
position, and then the car pops up box asking if you want to start the car?
(this is very funny!). You open a file cabinet, put the file in, and then
ask you...do you really want to file the file?

Fact is, you move to a new record..and it gets saved. Heck, even outlook
now has a save and close button. And, the very successful palm pda also has
implied saves.

The fact that YOU know about ram, and memory, and disk drives should not be
used to confuse users, and not change the fact that prompts to save data all
the time is a real annoyance. I mean, once you find out about how a starter
motor in a car works..are you now going to design cars that prompt after
you try and start them? How about when you lift the door handle, a box
comes up and asks you if you want to open the door?

As mentioned, an actual ton of software you use saves data all the time. You
create a email in outlook express, and then whack send...it does not ask you
to first save to the outbox, but that is in fact what happens. So, I can
think of a ton of software that does in fact save. In fact, I betting there
is more implied saves going on in software then there is software that asks.
The problem is that you are just not aware of this fact. If you use outlook
express to look at a newsgroup message, then that message actually download,
and then saved for you. At no point are you actually asked! I can go on and
on here. I just saying the trend in this industry is to imply things..and
not nag everyone to death. Software that don't bug people is a joy to use.
There is FAB article on this very concept here..and it continues to be
adopted in our industry:

http://www.joelonsoftware.com/uibook/chapters/fog0000000057.html:

Of course, the solution is to eliminate save prompts, but one REALLY MUST
GIVE the user a un-do command. And, that sorry to say is something we can't
do with ease when using a sub-form. So, to be clear, I am a big fan of
allowing a un-do here (and, if we could do that..then you could also use the
annoying save prompts concept of yours also!!). So, I think we all agree
that a user needs a way to "bail" out..but we can accept that we view things
a little different on the use of nag prompts and save prompts (I obliviously
don't like them..and think they are annoying and un-necessary).

Ok so then the question becomes much simpler - how do I turn this off?

Due to the fact that sub-forms are for "one to many" data relationships,
then when you try and add records in a sub-form, you MUST first have a
parent record. For enforced relations, Oracle, ms-sql server, Sybase, and
even the open source MySql ALL require that a parent record must FIRST be
saved and added to the database before child records are inserted.

If you must warp the current users work and record editing and record
navigation into a "session", then about the only solution here is to copy
the records to a temp data table (or several tables if you have one to many
data structure here). Doing this will allow you to edit the data, and the
issue of forcing the disk writes will now not matter. And, then when the
user hits a save button, you then send the records back to the actual data
table. So, the answer here is that you need to make a copy of the data..and
then you can edit and play with it at will.

The other possible solution is to wrap the reocedsets in a transaction. The
JET data engine does support commit, and rollback, but these commands only
apply to your recordset code, and not to forms that edit data. (you can look
these up in the help). It turns out that you can load up a reocrdset in
code, and then attach that reocrdset to the form..and you thus do get a
rollback ability. I never tried this approach, and don't know how well it
works. I would consider starting a new thread/question on this issue. (ie:
can I use commit, and rollback for a form...and how can I give users a un-do
command).

I _cannot_ have database writes going out all the time. Period. It's
basically illegal in this case. Like cops will come and arest people. I
need
absolute control over what and when the writes go out.

So basically unless I can trap out that "save on navigate", I have to
chuck
the subform and re-implement.

Yes, lets assume for the second that you could turn off the auto save when
your cursor moves from the main form to the sub-form. You still have a
problem since record movement between EACH record in the sub-form also
causes a save (unless, perhaps you want to prompt for each of those
records...as you do seem to like save prompts). So, really, the auto save of
the main record once again is not at all a problem here.

So, either you use transactions...or temp tables....

 

[Maury Markowitz]

Fact is, you move to a new record..and it gets saved.

And unless there is a way to turn this off, I will have to re-implement.
It's as simple as that.

Yes, lets assume for the second that you could turn off the auto save when
your cursor moves from the main form to the sub-form.

So how do I do this?

You still have a
problem since record movement between EACH record in the sub-form also
causes a save (unless, perhaps you want to prompt for each of those
records...as you do seem to like save prompts). So, really, the auto save of
the main record once again is not at all a problem here.

Well, obviously, I want to turn off ALL examples of "save on navigate".

And if there is no way to do this, I will re-implement.

Maury

 

[Maury Markowitz]

:

You seem to be confused about my Access complaint. I am not suggesting that
Access prompt for changes (the assumption you make repeatedly below), I am
suggesting that Access simply not save unless the action is "save" or some
similar action that the user specifically requested.

Clicking on a row, for me, for you, for everyone in the world, is not the
same as "save".

Every one of the examples you present to back up this point fails to match
the real UI rules. It also fails to address the case in question, where you
do one thing, and Access does another.

The rules are simple: any permanent state change to _user_data_ should either:

1) be easily undoable
2) be the result of deliberate user action
3) prompt the user
4) do (3) even in the case of (1) and (2) if the action is dangerous or slow

Let me illustrate...

when you actually move a icon on your desktop, are you prompted to
save the location?

Fails because: No permanent change of state to USER DATA (icon position is
meta data, not user data), obvious undo (move it back), fast and
non-dangerous.

Example that addresses my concern: When you move an icon, it re-organizes
the entire drive for better opening performance for that file.

Real world example: Move icon to trash in a case where there is no "empty
trash" capability (network, large file, etc). This is non-reversable,
potentially dangerous, often time consuming. This should ovbiously prompt,
and does.

Can you imagine if you put a key in a car, and then turned it to the start
position, and then the car pops up box asking if you want to start the car?

Fails because: You are confusing prompts with (2), deliberate action. You
told the car to start but turning the key, there's no need to ask again.
Likewise if you press the Save button on my form, there's no need to prompt
again.

Example that addresses my concern: You turn the key and it shifts into
drive. I mean, why else would you start the engine but to drive, right?

Real world example: Almost all cars WON'T let you start them unless they are
in Park or have the clutch onto the floor.

You open a file cabinet, put the file in, and then
ask you...do you really want to file the file?

Fails because: No permanent change to user data (the file is unchanged),
obvious undo (take it back out), is the result of deliberate user action
(wants to put it in the cabinet).

Example that addresses my concern: You put the file into a file cabinet and
it mails a copy to your boss.

Counterexample: Put file into paper shredder. Permanent change to user data,
no undo. Many companies have legal requirements not to do this, and yet this
still causes so many problems they make ads joking about it.

Heck, even outlook now has a save and close button.

Fails because: well it's pretty obvious in this case, it DOES have a save.

Example that addresses my concern: You hit send and it also CCs your mom.


Now lets consider my case. The user clicks in the window and the record is
saved out, thereby causing a permanent change to the user data. While that
change is potentially undoable, it is also unintended (I didn't say save,
print, send, or anything like that, I _clicked_ on something that wasn't even
a button!). It is also potentially time consuming, non-reversable and
dangerous.

So what should it do in this case? DON'T SAVE. Just hold the changes in
memory and save if and when a .update goes out. This should be obvious.

This is very bad UI. Period. In fact, I can think of only a few programs
that operate this way, and they too are bad. Let's consider other apps first
though, even sticking with Office...

Subforms look like tables (sort of). Excel works with tables. By your logic,
it would be OK if Excel saved every time I click in another cell.

Subforms also look like "subdocuments". Excel has these too, as worksheets.
Again, I think you'd agree that Excel should not save every time you click
between sheets.

As a last resort, subforms look like OLE objects embedded in something else,
say a Word document. Once again I think you'd agree that clicking on a table
should not save the document.

Of course these programs don't work this way, because it's a bad idea.
Access's surreptitious saves are also a bad idea for the same reasons. We
live with them because that made it easier to write Access, and, let's face
it, Access is pretty kick-ass for development so we all put up with it.

Note that in order to solve this, MS has spent a considerable amount of
effort on ADO.NET in order to avoid these issues by working in a completely
disconnected fashion and only saving when the user deliberately calls
..update. Unfortunately this hasn't rolled into Access yet (any likely won't).

Of course, the solution is to eliminate save prompts, but one REALLY MUST
GIVE the user a un-do command. So, I think we all agree that a user needs a
way to "bail" out

Which Access doesn't. Thus my complaint:

click on row != save changes

Maury

 

[Albert D. Kallal]

message do one thing, and Access does
another.

Fails because: You are confusing prompts with (2), deliberate action. You
told the car to start but turning the key, there's no need to ask again.
Likewise if you press the Save button on my form, there's no need to
prompt
again.

On the other hand, when a user navigates to a another record..WHY would they
want to navigate to another record? The user wants to move to the next
roared, and MOST CERTAINLY they want the current record saved. I see NO
reason to ask the user to save the current record? Why would a user try and
move to another record without first wanting to save? This is in fact
exactly the same thing as the user starting a car. Moving to another record
is deliberate action on the part of the user. Fact is, moving to another
record implies the user wants to save the current record. This is most
certainly deliberate action on the part of the user. I am MOST CERTAIN we
took a poll of people and asked them if they are going to navigate to new
record..do they want the current record saved? (99 out 100 times..the answer
is yes..for the exception..the user should use the undo). The problem here
is you are applying a concept of document like word to ms-access which is a
database. DBase, FoxPro, DataEase, Reflex and most database applications
have this implied save. In fact, even using the Enterprise tools for sql
server, you will find that navigation again has implied saved when editing
data. You got the whole database industry against you on this issue!

Having excel save the whole document is not a fair comparison here. However,
what is a FAIR comparison would be to prompt the user to save each row BACK
INTO the spreadsheet when a user navigates (and that was my point). You seem
to be telling me that navigation is not a deliberate action, and it should
not
save the data.

So, my point about Excel is not saving the whole document, but in fact
saving the current row. Fact is, when should a row of data in Excel be
committed to the spreadsheet? And should the user be prompted for each
row...(and, no..they should not!).

And, the above issue also applies to closing a form. In fact, if a user
closes a form, then that is a deliberate action again..and save should be
implied.

Now, having said the above..I am in total agreement with you about the undo
issue! It is a WEAK spot in ms-access, and I SHOULD be able to wrap a form
session in a transaction. (this would give me my needed un-do..and give you
an
ability to save).

Example that addresses my concern: You turn the key and it shifts into
drive. I mean, why else would you start the engine but to drive, right?

We are talking about reasonable action here.

Fails because: No permanent change to user data (the file is unchanged),
obvious undo (take it back out), is the result of deliberate user action
(wants to put it in the cabinet).

Oh,...no, I am assuming we did modify the documents in the folder..and now
are
putting it back. No way should I prompted again.

Example that addresses my concern: You put the file into a file cabinet
and
it mails a copy to your boss.

That is not expected behavior, nor what 99 out of 100 people would want.
However, it is WITHOUT QUESTION that 99 out of 100 people want the file
saved!. The difference between a good software developer and poor one is
that
you have to make a reasonable assumption here. You have to think here!

Example that addresses my concern: You hit send and it also CCs your mom.

No, the above is a poor example, since that is not what people would
normally want! A good example is that you hit send, and
then the software prompts you to save the document into the out box. Every
person on the planet agrees that the document should be saved into the
outbox.. (you seem to hint that the user should be prompted in this case).
It would be silly to have the document cc to someone else (that is just
plain
silly logic here). The real issue here is because of user action, should
the user be prompted to save the document in the outbox. IT IS IN
FACT SAVED THERE FOR YOU!!! Users are NOT asked if
they want to save the email in the out box. Fact is, it is just a bad
idea to nag users all day..so the designers do not!!

You seem to hinting that users all day long for hitting send
also need to be prompted to first save the document into the out box!. I
just can't buy this concept. It is a good thing that send saves the email
in the outbox..and it is also a great feature that navigation implies a
save.

Now lets consider my case. The user clicks in the window and the record is
saved out, thereby causing a permanent change to the user data. While that
change is potentially undoable, it is also unintended (I didn't say save,
print, send, or anything like that, I _clicked_ on something that wasn't
even
a button!). It is also potentially time consuming, non-reversable and
dangerous.

As I mentioned, mere navigation in a lot of software implies a save.

This is very bad UI. Period. In fact, I can think of only a few programs
that operate this way, and they too are bad. Let's consider other apps
first
though, even sticking with Office...

Hum...I mentioned virtually every database system, and including the
enterprise tools for sql server implies a save. You certainly have placed
you self above the whole industry on this.

Subforms look like tables (sort of). Excel works with tables. By your
logic,
it would be OK if Excel saved every time I click in another cell.

No, by my logic, you would be prompted to save each row INTO the
spreadsheet. That is big difference here. Again, you are brining in the
concept of that Excel happens to be a document..and not a bunch of separate
records here.

Lets further clear this. When you move to a new record in
the sub-frm, the WHOLE DATABASE IS NOT SAVED. So, NO ONE is suggestion that
the WHOLE database and ALL RECORDS be saved. We are talking about ONE
record. So, no, saving 10,000 rows of data in a spreadsheet due to clicking
on another cell is absurd. That is crazy..and no one would suggest saving
10,000 rows...would you?
However, you seem to be suggesting that moving down one row should case a
prompt? Fact is a
row navigation in Excel does not case a prompt, and nor does it in ms-access
(or, most database systems in this case). Neither Excel, or ms-access cause
all rows of data
to be saved by a simple navigation. So, if a simple click in Excel case the
whole document to
be saved, you could be saving THOUSANDS and THOUSANDS of rows back to disk.
This
not even close to being sensible here.a.nd further that is not what happens!
However, ONE row
certainly gets saved to disk in ms-access due to ravaging. And my MAIN
POINT is that
NEITHER Excel, or ms-access prompt you!Again, I rest my case on this one!

Subforms also look like "subdocuments". Excel has these too, as
worksheets.
Again, I think you'd agree that Excel should not save every time you click
between sheets.

As mentioned, it is silly to apply a document system concept to that of a
database where each row is a separate piece of data. If you could save
part of a Excel sheet..then now we have to deal with save prompts!
If excel allowed saving of each individual cell to disk..then what would
you suggest? Again, the issue here is stupid and annoying save
prompts..and also that of user actions.

Of course these programs don't work this way, because it's a bad idea.

Well, Foxpro, dataese, ms-access, sql enterprise tools and just about every
data program on the planet does work this way.
So, you are in VERY lone company on this issue.

Access's surreptitious saves are also a bad idea for the same reasons. We
live with them because that made it easier to write Access, and, let's
face
it, Access is pretty kick-ass for development so we all put up with it.

Lets be careful here. There is the issue if implied saves is different then
that of having a roll back...or a un-do. Fact is virtually the vast majority
of
data programs have implied saves due to navigation. The whole software
industry
for the last 20 years is on my side on this issue. You have every right to
express your option here..but the whole computer industry for 20+ years
simply don't agree with you. Further, as I said, even MORE software is
adopting implied behaviors to day.

However, where I do agree with you is that we need the ability to bail out
the changes to a form + sub-form combo. It is difficult to wrap the form
in a transaction (you can do this by the way!). However,that is COMPLETE
different issue then implied saves..which the whole database industry has
had
for 20+ years.

Note that in order to solve this, MS has spent a considerable amount of
effort on ADO.NET in order to avoid these issues by working in a
completely
disconnected fashion and only saving when the user deliberately calls
.update. Unfortunately this hasn't rolled into Access yet (any likely
won't).

Disconnect reodrdsets is one way. However, we don't need the new ado model
to have a commit, or rollback to solve this problem. You can also use
un-bound forms (that is what most VBers use). Or, you simply
make a copy of the data (which the .net ado in effect does).
And, further, stuff like record navigation again can still imply save
when you use ado.net. It will be up to the developer in this case!
Again, you are confusing the issue of implied saves with that of having
a commit..or rollback here, or very simply a COPY of the data! Just
because some software uses ado.net..this does NOT mean that
navigation will not save data.

Further, MS-access forms don't come with a save button, nor are users told
to use one. As a general issue, you don't see save buttons in ms-access
applies. They are not needed.

click on row != save changes

Well, you better go talk to the designers of dBase, FoxPro, Reflex, sql
enterprise tools..and a whole lot more to see what they had to say on this!
Sorry..but navigation does imply saves...and has for 20+ years...

 

[Albert D. Kallal]

By the way...looking at my post..it really don't help me or you very much!

My apologies for such along post (perhaps some would say rant).

Once again. my apologies.

Fact is, both you and I need a good solution to roll out changes to a form +
sub-form Really, that is both yours and my goal.

I enjoy the debate we are having..but really, me an you just want to get our
jobs done! I always feel a bit guilty when I spend too much time getting off
track!..and trying hard to make some point abut saves etc.

I shall promise you that when I have a good working solution to this
problem.I will post a solution. I am busy in the middle of project right
now..but when I can...I will endeavors to share with you a good solution (I
don't have one in my big bag of code right now...but do need one!!).

About the best soltion right now is if you hve a split database, is to then
you pull the data into temp tables in the front end. Allow edits...and then
send the data back. Not too clean..but that is start.

Futhter, if you like me advoied navagaion butitons for hte "main" form..then
this should work well. If you need navagation in the main form..then this
becomes more diffcult.

 

[Jamie Collins]

You seem to be confused about my Access complaint. I am not suggesting that
Access prompt for changes (the assumption you make repeatedly below), I am
suggesting that Access simply not save unless the action is "save" or some
similar action that the user specifically requested.

I sympathize. One the reasons I do not use MS Access for a front end
application is its 'bound and always connected' model which seems to
come as default, compulsory even.

I prefer the 'unbound and disconnected' approach. For example, I may
have a DataGrid on a Userform in Excel populated with data from a Jet
(MS Access) database using a disconnected ADO recordset with a
client-side cursor and batch-optimistic locking. Because it is bound,
changes to data in the grid are propagated to the recordset. However,
because the recordset is disconnected, the database is not updated
until I reconnect and issue an UpdateBatch (and then in a transaction
in case I want to rollback).

As I say, I don't know MS Access but could you not adopt the same
approach i.e. use a client-side cursor and batch optimistic locking,
set your recordset's Connection property to nothing. I think it
unlikely MS Access would reconnect and issue an update every time you
changed a row in the recordset/form.

Jamie.

--