Parent & Child Form Refresh/Requery Not working

[Jerry Maiapu]

Hi all,
I have a child and parent forms. The record source of the sub-form is query containing aggregate functions.

A combo box in the parent form filters the sub-form results based on value (name) selected.

Some values of the sub-form are accessed and displayed on the main form; A calculation is done (using values of the sub form)and final result is displayed on main form.

When clicked on a button on the main form the result is inserted into a temp table to replace an existing value. (The sub-form 's recordsource, I mentioned earlier is a query based on this temp table)

So basically after new calculated value is inserted into
the table the current calculated values on main form would change obviously when sub-form is refreshed.
Problem:
That is not happening. (Value not updated on form when requeried)

I tried

        If rs!EmployeeId = Me.EmployeeId And rs!DateWorked = Me!txtdate Then
         rs.Edit
         rs!Reset_Value = Me.[B]txtfinalcredit[/B]'[I]inserting calculated value[/I]
         rs.Update
      End If
     

 MsgBox "Credit reseted Successfully", vbOKOnly + vbInformation, "Sucessful"
[B]DoCmd.Requery "mysubform"[/B][I]'requery subform[/I]
[B]Me.Requery[/B][I]'requery all controls[/I]

Replacing line 10 & 11 with this does not work as well:

[B]Me.Refresh [/B]

It only works (value is updated)when I select the same value using the combobox.

E.I After update event of combo box fires but nothing of above seem to work.

Hope I am contextualizing .

Someone with a better idea can help me out

[Stewart Ross]

Hi Jerry. Have you tried requerying the subform's instance that is attached to the main form rather than requerying the subform itself? The subform is a control of the main form, and I suspect that requerying the subform itself as you do at line 10 will not update the recordset instance on your main form.

The syntax in place of line 10 in your code would be something like

Me![Mainformname]![SubformControlName].Form.Requery

If you find that this does not work, you could try dynamically replacing the recordsource of the subform in place of the requery:

Me![Mainformname]![SubformControlName].Form.Recordsource = "Select * from [YourTable]"

You may well then have to requery the main form to reconnect the two, though.

The Refresh method does not cope with additions or deletions, whereas requery does; Refresh is intended for use where users may be changing existing record values in a multi-user environment.

-Stewart

[Jerry Maiapu]

Stewart,
I just made a change to your solution and bingo it worked.Thanks so much.

Changed

[B]Me[/B]![Mainformname]![SubformControlName].Form.Requery

to:

[B]Forms[/B]![Mainformname]![SubformControlName].Form.Requery

just replaced Me by Forms.
NB: With Me it gave error

[zbuttsaab]

Stewart,
I just made a change to your solution and bingo it worked.Thanks so much.

Changed

[B]Me[/B]![Mainformname]![SubformControlName].Form.Requery

to:

[B]Forms[/B]![Mainformname]![SubformControlName].Form.Requery

just replaced Me by Forms.
NB: With Me it gave error

No good deed goes unrewarded: the final solution worked for me also: each new subtotal on each purchase order line is reflected in the purchase order total. Thank you guys.