Setting RecordSource in a subform based on a field in the subform
Posted on 2014-10-08
I have a form (name = frmCalendar) that has seven subforms (one for each day of the week with the names frmCalendar_Day1, frmCalendar_Day2, etc.). Each subform has a control on it called ColumnDate. The parent form has a variety of buttons that will change the ColumnDate on each of the subforms.
I'd like each subform to display just the records that match its ColumnDate and for this to be applied immediately whenever the ColumnDate is changed.
So, for instance, the recordsource of the first subform might be something like this:
SELECT * FROM tblOrders WHERE OrderDate=ColumnDate
(I realize that's not the right syntax; I've just supplied it to give the idea of what I'm trying to accomplish.)
If the ColumnDate of each subform is changed in the parent form, how do I make the recordsource of each subform change automatically?
I'm not at liberty to change the overall design of the form - it needs to remain one main form with seven subforms. Given that restriction, what's the best way to accomplish this?
Thanks in advance.