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?