How to Update a form: requery, recalc, refresh from subform entry

[DustyD76 via AccessMonster.com]

I have a main form with multiple sub forms.

Form Purpose
Main form - Enter/shows product name and info
Sub form1 - Enter/shows Order details including current price
Sub form2 - runs off a query to display current product price on main form
Sub form3 - runs off a query to display current inventory & Value of the
inventory based on price
Sub form4 - runs off a query to display current inventory value of all
products added together

Now for the problem

The only way I can get sub forms 3 & 4 to show new calculations is to close
the form and re-open.

I need subforms 3 & 4 to update whenever a record is changed or added in
subform 2

Any suggestions?

 

[DustyD76 via AccessMonster.com]

Correction - now Updated (we need an edit button on here badly.)


I have a main form with multiple sub forms.

Form Purpose
Main form - Enter/shows Product name and info
Sub form1 - Enter/shows Order details including the current Inventory &
current price
Sub form2 - runs off a query to display current product price on main form
Sub form3 - runs off a query to display current inventory & Value of the
inventory based on price
Sub form4 - runs off a query to display current inventory value of all
products added together

Now for the problem

The only way I can get sub forms 3 & 4 to show new calculations is to close
the form and re-open.

I need subforms 3 & 4 to update whenever a record is changed or added in
subform 2

Any suggestions?

 

[Douglas J. Steele]

Assuming that the subform controls on the main form are named Sub3 and Sub4,
you can issue commands

Me!Sub3.Form.Requery
Me!Sub4.Form.Requery

assuming that this code is running from the module associated with the
parent form. More generically, it would be

Forms![NameOfParentForm]!Sub3.Form.Requery
Forms![NameOfParentForm]!Sub4.Form.Requery

Note that depending on how the subforms were added to the parent form, the
name of the subform control may not be the same as the name of the form
being used as a subform.

 

[DustyD76 via AccessMonster.com]

Douglas J. Steele wrote:
More generically, it would be

Forms![NameOfParentForm]!Sub3.Form.Requery
Forms![NameOfParentForm]!Sub4.Form.Requery

Note that depending on how the subforms were added to the parent form, the
name of the subform control may not be the same as the name of the form
being used as a subform.

Doug, This works partically - I now don't have to close the form - i just
have to scroll to a new record and scroll back on the main form for this to
work.

any way of having it update without scrolling off of the current record?

 

[DustyD76 via AccessMonster.com]

More generically, it would be

Forms![NameOfParentForm]!Sub3.Form.Requery
Forms![NameOfParentForm]!Sub4.Form.Requery

Note that depending on how the subforms were added to the parent form, the
name of the subform control may not be the same as the name of the form
being used as a subform.

Doug, This works (oops typing email in the middle of that response) - I now don't have to close the form - i just
have to scroll to a new record and scroll back on the main form for this to
work.

any way of having it update without scrolling off of the current record?

 

[Douglas J. Steele]

Douglas J. Steele wrote:
More generically, it would be

Forms![NameOfParentForm]!Sub3.Form.Requery
Forms![NameOfParentForm]!Sub4.Form.Requery

Note that depending on how the subforms were added to the parent form, the
name of the subform control may not be the same as the name of the form
being used as a subform.

Doug, This works partically - I now don't have to close the form - i just
have to scroll to a new record and scroll back on the main form for this
to
work.

any way of having it update without scrolling off of the current record?

In what event have you put the code?

The data won't be saved until you move off the record. You can force a save
using:

If Me.Dirty = True Then Me.Dirty = False

or

If Forms![NameOfParentForm].Dirty = True Then Forms![NameOfParentForm].Dirty
= False