Putting code in a subform?

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

Try:
Call Me.Sub1.Form.Test_Click

Alternatively:
Call Form_Sub1.Test_Click

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Maury Markowitz" wrote in
message
...
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" wrote:
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

"Maury Markowitz" wrote in
message news:74CC66C8-2602-4686-A2CD-

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.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.attcanada.net/~kallal.msn

"Albert D. Kallal" wrote:
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

"Maury Markowitz" wrote in
message ...


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).


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.

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!!


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.attcanada.net/~kallal.msn

"Albert D. Kallal" wrote:
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

"Maury Markowitz" wrote in
message news:EC24ED2C-945C-4163-9843-

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).

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

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....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.attcanada.net/~kallal.msn

"Albert D. Kallal" wrote:

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

"Maury Markowitz" wrote in
message ...
"Albert D. Kallal" wrote:

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.

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...00000057.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....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.attcanada.net/~kallal.msn