Update subform based on Mainform combobox selection

I

Ian Chappel

I can't work out how to do this!

No problem after all of mainform record completed - I can Recalc. But I want
to update the subform immediately the combobox is changed, but Requery
doesn't work (unless I'm putting the command in the wrong place), and Recalc
and Refresh won't work until the Main record is complete.
 
B

Brian

If the subform has a calculated control based on the contents of the current
record on the main form, remember that its value in the table does not change
until you save the record. This can get pretty complex, but if you are
summing things, you may have to take the sum from the table, subtract the
STORED value of the entry currently open on the main form, and add in the
EDITED value of the entry currently open on the main form.

Or, you could force the record to save at various points on your main form,
but this precludes the user cancelling changes.
 
I

Ian Chappel

Thanks Brian,

The subform has a query as it's Source, and is linked to the Main form by
the data selected in the combobox. In other words, a pretty standard
One-To-Many Main-Sub form situation. I just want to Requery/Recalc/Refresh
(not sure which!) the subform as soon as the combobox is changed, before the
mainform record is completed. Problem is, that my table validation rules are
complaining that the Main record is incomplete/incorrect. So I need to
Requery/Recalc/Refresh the subform _without_ calculating or validating the
main form, presumably by the AfterUpdate or OnChange event of the combobox.

Sounds simple, but I can't seem to make it work. To be honest, I have
trouble knowing which of the Requery/Recalc/Refresh methods to use when, but
none seem to work here (unless I am using them on the wrong object?).
 
B

Brian

Is the combo box (on the main form) to which you refer bound or unbound.
Assuming that it is bound, the value of the entry in the table does not
change until the record is saved, even though you pick a different value in
the box.

If you have a child/parent link between the form & subform, the link is on
the data as it exists in the table, not the contents of the combo box been
edited (but not yet saved to the table).

Without knowing all the complexities of the child/parent relationship, etc.,
it is hard to pinpoint without quite a bit more detail. Zip & send your app
to me if you like, & I will review the details.

brainy
at
pacifier
dot
com
 
I

Ian Chappel

Thanks

I realise that the link is on the underlying tables/queries, but can't I use
the OnChange or AfterUpdate events of the combo-box to refresh the data in
the subform, using code? Although the combobox is bound, I would've thought
this should still be possible - or does the fact that it's bound throw a
spanner in the works?

I think my problem is purely about refreshing the subform without refreshing
the main form, triggered by an event of the combobox on the main form.
 
B

Brian

Here is the key, in your question: does the fact that it's bound throw a
spanner in the works?

Probably, and here is why. The form/subform link is entirely dependent up on
the SAVED value of the parent record (i.e. how it exists in the table), not
the UNSAVED EDITED value that appears in the combo box.

It is possible to make these things work by relating something on the
subform to the current value of the CONTROL instead of the current value of
that field in the current record (i.e. before the current value of the
control is saved).

However, since I don't know all the details of your form/subform structure
(or what your form is meant to accomplish), it might be faster to sort this
out by e-mail. Feel free to do so, and see my address in the prior post.
 
M

Matt K.

I found this post as I have/had similar situation as Ian...
After a lot of trial and error, I placed the following line of code in the
OnChange Event of my combobox...

Me.Refresh

This seems to work great. Now my subform is instantly updated to reflect the
change made in the combo.
 
Top