Link to home
Start Free TrialLog in
Avatar of kosciost
kosciost

asked on

How do I get a linked subform to filter on the default value of the master field

I have a form, with a linked subform.  The master form is populating a dropdown from a table, like this:

SELECT AwardYears.[Award Year] FROM AwardYears WHERE (((AwardYears.Proposal)=[Forms]![Transmittal Form]![Proposal number]));

And its populating its default value:
=DMin("[Award Year]","AwardYears","AwardYears.Proposal= '" & [Forms]![Transmittal Form]![Proposal number] & "'")


This dropdown works fine, selecting the existing "award years" for the "proposal" being displayed.  The subform uses a linked master and child field, which works correctly when the data is changed.

The one problem I'm having is that when I first enter the form, although the default value is populated in the "award year" dropdown, the subform displays all matching values, ignoring the dropdown until I manually make a selection in the dropdown.

If I switch the "Filter on empty master" from No to Yes in the subform, the behavior changes from displaying all records, to displaying no records until a selection is made in the dropdown.

I want the default value in "award year" to be used as the initial filter value for the subform, but I can't seem to make this happen.  I'm probably missing something simple, but I have no idea what.  Any Ideas?

Thanks!
ASKER CERTIFIED SOLUTION
Avatar of MikeToole
MikeToole
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kosciost
kosciost

ASKER

Okay, that makes an unfortunate amount of sense.  So I need to enter edit mode somehow...

Ah found it.  I needed to add:
Me.Text67.selected(1) = True

to the "on load" macros.  Now it does exactly what I want.

Thanks for your help!