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

kosciost used Ask the Experts™
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?

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The master field is usually bound to a field in a recordset, therefore for a new record in that recordset there's no value for the master field and - in the usual run of things - there's nothing to display in the subform. The default value is just that until the new record enters edit mode.
What records are there to display when the master record doesn't yet exist?


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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial