Requery a subform from another subform

[Guest]

I have two subforms (both continuous forms), within my main data entry form.
Let's say that they're called SF1 and SF2.

When the user selects a record in subform SF1, I want to requery the other
subform SF2 so that it's recordset will refresh (and essentially filter based
on the current selection in SF1).

Right now, when I open the main form, SF2 is filtered based on the first
record in SF1.

Think of the main form being the client info, subform SF1 being a list of
all projects for that client, and SF2 being the task list for the currently
selected project.

I've tried using macro commands activated by a control or form event, but
nothing seems to work.

I would prefer to do this with macros as I'm more familiar with those, but
if it can't be done, I have dabbled a bit with modules and would be willing
to try.

Thanks for your help.

 

[Marshall Barton]

I have two subforms (both continuous forms), within my main data entry form.
Let's say that they're called SF1 and SF2.

When the user selects a record in subform SF1, I want to requery the other
subform SF2 so that it's recordset will refresh (and essentially filter based
on the current selection in SF1).

Right now, when I open the main form, SF2 is filtered based on the first
record in SF1.

Think of the main form being the client info, subform SF1 being a list of
all projects for that client, and SF2 being the task list for the currently
selected project.

I've tried using macro commands activated by a control or form event, but
nothing seems to work.

I would prefer to do this with macros as I'm more familiar with those, but
if it can't be done, I have dabbled a bit with modules and would be willing

Can't help you with macros, but the logic is so simple that
you should be able to deal with it yourself.

Create an invisible text box named txtLinkSF2 in the main
form's header section. Use SF1's Current event to set the
text box's value to SF1's PK field's value. In VBA, it
would just be:
Me.txtLinkSF2 = Me.txtPKfield
which is probably a SetValue action in a macro.

Then, set SF2's LinkMaster property to txtLinkSF2 and set
the LinkChild property to the corresponding foreign key
field in SF2's record source table/query.

 

[Guest]

I can't seem to set the LinkMaster property to anything other than a field
from the main form's underlying table, so I can't set it to a text box
control. I'm trying to do this from the subform's properties box.

If I set LinkMaster.value = "txtLinkSF2" using code or the SetValue macro
action, will this work?

 

[Guest]

I just found the solution:

For the subform SF1 'OnCurrent' event, use the SetValue action to set the
subform SF2 RecordSource property. Even though I'm just resetting the
recordsource to the same query every time, this forces the subform SF2 to
requery itself.

 

[Marshall Barton]

That will indeed force a requery, but it sure is the long
way around. Why not just Requery SF2?

I don't understand why you're having trouble setting the
Link Master property, but that's not what I was suggesting.
The Link Master/CHild properties should be set once at
design time, not while the program is running. The only
thing to set in the SF1's Current event is the hidden text
box.

I should have mentioned that the LinkMaster/Child approach
requires that SF2's record source query should **not**
filter for the desired record, the Link properties take care
of that automatically.