requery one subform from another

[andy1968]

Working in Access 2010.

I have an unbound main form that I use to recall data from tables.

I have 4 subforms on the from in a tabcontrol.

The subforms data are all linked properly.

I have the following code to requery 3 of the subforms when one of the other forms changes records:

Private Sub Form_Current()
Me.Parent.[frmUpdateWarrantyLinks].Form.Requery
Me.Parent.[frmUpdateWarrantyNotes].Form.Requery
Me.Parent.[frmUpdateWarrantySupplier].Form.Requery
End Sub

The code works fine except when I open the main form for the first time.

Then I get a Run-time error '2455":

You entered an expression that has an invalid reference to the property Form/Report.

Any ideas?

 

[Galaxiom]

Another subforms have not loaded before the one of the Current Events fires.

Have the subforms set flags on the parent form during their Load event and test them before firing the Requery.

 

[andy1968]

Thanks Galaxiom for the quick response.

I can't figure out how to code what you are suggesting - google search did not help me either.

Andy

 

[James Dickinson]

Hi andy1968

I think you need to do your re-query in the after update event not the current event of the subform.

Current will, like you said, fire when the form opens as well as when the data changes. After Update will fire only once a record has been altered or created.

I hope this helps

 

[andy1968]

Thanks James.


Update would work, except there are some cases where I am not updating data, just looking at it going from one record to the next.



Thus no update.

 

[Galaxiom]

Actually you should not have to Requery if you have the subforms properly linked.

The main form should have a textbox with a ControlSource that reads the key field from the "master" subform. The other subformcontrols use this textbox as their LinkMasterField.

This arrangement does not exhibit the bootstrapping problem you are experiencing.

 

[James Dickinson]

OK so perhaps you need code to catch the current ID of the record you are looking at. Then you check if that ID has changed AND is not null then you can re-query your other subforms.

Option Compare Database
Option Explicit
DIM gVarCurID as variant
___________________________
Private Sub Form_Current()

if not isnull(gvarCurID) then
if gvarCurID <> me.ID then
Me.Parent.[frmUpdateWarrantyLinks].Form.Requery
Me.Parent.[frmUpdateWarrantyNotes].Form.Requery
Me.Parent.[frmUpdateWarrantySupplier].Form.Requery
end if
end if
gvarCurID = me.ID
End Sub
___________________________________

 

[andy1968]

Thanks James - I'll try something like this tomorrow at work.



Galaxiom - The subforms are not linked - they are all subforms are all subs to the main form that is unbound. It's not ideal functionally, but I like the look.

 

[June7]

Is this what you need http://www.fmsinc.com/MicrosoftAccess/Forms/Synchronize/LinkedSubforms.asp

 

[andy1968]

Thank June - works like a charm!