Generic command to force requery for subforms? 2

[VBAjedi]

I'm sure this has been answered before but I can't find it and the Object model isn't making the answer obvious to me either...

Is there syntax to force a requery of all subforms on a form (without referring to them explicitly by name)? I want to put that in the AfterUpdate event of a textbox on the parent form. Something simple and generic like:

Form.Subforms.Requery

Loosely related question: is it good practice to use the value of a text box on a form in a query for a subform? i.e. using the following in the query criteria::

=[Forms]![Dashboard]![TasksDueByDate]

It works, I'm just wondering what happens if the form with the text box isn't open (which could happen if I use this subform on another form). Does this cause potential problems, or is it the accepted way to pass a user-supplied value from a form to a subform query?

VBAjedi

 

[kjv1611]

I guess you could loop through the subforms, refreshing them... therefore only requiring one requry statement using a variable for the subform controls.

 

[MajP]

Untested, but here is the general idea assuming subforms are not nested.

Dim ctrl as access.control
For each ctrl in me.controls
If ctrl.controlType = acsubform then
Ctrl.requery
End if
Next ctrl

Why would you not link the subform by TasksDueDate, instead of filter? It is not a bad practice, but why not use the power of a subform to filter on the main form. Then you can use the subform as a mainform without changing the query.

 

[TheAceMan1]

How are ya VBAjedi . . .

[blue] ... is it good practice to use the value of a text box on a form [purple]in a query for a subform[/purple]?[/blue]

[ol][li]If the textbox is on an independent form, then that form needs to be open before the subform opens.[/li]
[li]If the subform is on a mainform then it won't work as [purple]subforms open from the innermost nested subform out to the mainform[/purple]. In short ... subforms on a mainform open 1st.[/li][/ol]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[VBAjedi]

TheAceMan1: Thanks - that's helpful info! The query is for a subform on an unlinked main form (which doesn't have a dataset associated because it's just a "dashboard" used to display a collection of pivotcharts on subforms. The only thing on the main form are the criteria text boxes used to narrow the results displayed in the subform charts.

MajP: That code works - thanks! Regarding your question: I hadn't thought of linking the subform to TasksDueDate because I didn't know it was possible to link between unbound forms (and I realize that in my original post I didn't specify that the subform and parent form are unlinked). Is that possible?

Thanks again - stars for both of you!


VBAjedi

 

[MajP]

Yes. Matter of fact you can link a subform to a control as well. Even if the control is unbound. This is useful. For example you can put a combo on an unbound main form, and a subform. Link the subform to the combo, and if you change the value of the combo the records in the subform filter to the value in the combo. This is one of the fastest ways to design a "search" form. I never build forms with navigation buttons visible, and this is usually the main way I provide navigation.

This allows one of my other favorite tricks, synchronized subforms. You can put two continous subforms on a main form. Lets say subform equipment and subform parts. You click on a piece of equipment and it shows the related parts. To do this put a hidden textbox on the main form. Link the parts subform to the textbox. In the equipment on current event change the value of the textbox. Voila.

 

[VBAjedi]

I like it... how do I go about playing with linking subforms to unbound controls on the mainform? The wizard doesn't want to help me.

Is it as simple as typing a reference to the mainform combo box into the "Link Master Fields" box of the subform's properties?

Also, in this case I'm wanting the subform query to return all records where "DueDate" < the user-supplied date in the mainform text box. Is linking still a viable approach when you need a logical evaluation as opposed to a static key/value match (as the typical form/subform link generally uses)?

VBAjedi

 

[MajP]

Yes the wizard will not let you do it, so hit cancel.
linkMasterField:[comboName]
linkChildFields:[yourSubformFieldName]

For the second question, unfortunately not. The subform linking only does exact matches. That would be a nice feature.