Setting RecordSource in a subform based on a field in the subform
Hello,
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.
James
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.
James
ste5an
Use the query wizard. There use the Generate.. (the wand button) to create a condition wich points to your control on the parent form.
The simplest setup:
1. Base each subform on a query if it is not already.
2. In each of those queries, add a criteria on the date field of:
=Forms![frmCalendar]![<nam
3. In the AfterUpdate event of the controls on the parent form with the date, requery the subform:
Me![<subformcontrolName>].
This is only one way to do this. There are many.
Jim.
1. Base each subform on a query if it is not already.
2. In each of those queries, add a criteria on the date field of:
=Forms![frmCalendar]![<nam
3. In the AfterUpdate event of the controls on the parent form with the date, requery the subform:
Me![<subformcontrolName>].
This is only one way to do this. There are many.
Jim.
Just Refresh the Subforms after the user changes the Date in the main form:
Me.SubformControl1.Form.Re
Me.SubformControl2.Form.Re
I tend to do it like this:
Me.SubformControl1.Form.Re
Not sure why ... I recall running into issues with the refresh method, and adopted the Recordsource method.
Me.SubformControl1.Form.Re
Me.SubformControl2.Form.Re
I tend to do it like this:
Me.SubformControl1.Form.Re
Not sure why ... I recall running into issues with the refresh method, and adopted the Recordsource method.
ASKER
Thanks for the responses. Thanks to your suggestions, I've been able to get it working but I think I'm being overly complex so I'll ask for a bit more clarification.
There is a date field (StartDate) on the parent form (frmCalendar) that holds the date for the first column.
Then I have seven subforms (frmCalendar_Day1, frmCalendar_Day2, etc.) that each have a field called ColumnDate. On frmCalendar_Day1, ColumnDate should be the same as StartDate on the parent form. On frmCalendar_Day2, ColumnDate should be StartDate+1, etc.
What I've done now is to put seven date fields on the parent field (one corresponding to each of the columns). I've also created seven different queries with each one pointing to one of those date fields in the query criteria.
Is this the best way to do it or is there a way I can do it with just one underlying query?
Thanks again.
James
There is a date field (StartDate) on the parent form (frmCalendar) that holds the date for the first column.
Then I have seven subforms (frmCalendar_Day1, frmCalendar_Day2, etc.) that each have a field called ColumnDate. On frmCalendar_Day1, ColumnDate should be the same as StartDate on the parent form. On frmCalendar_Day2, ColumnDate should be StartDate+1, etc.
What I've done now is to put seven date fields on the parent field (one corresponding to each of the columns). I've also created seven different queries with each one pointing to one of those date fields in the query criteria.
Is this the best way to do it or is there a way I can do it with just one underlying query?
Thanks again.
James
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<<There is a date field (StartDate) on the parent form (frmCalendar) that holds the date for the first column.>>
If you have only one date field, then each of the queries could use an offset without needing a separate date field for each on the parent form:
=Forms![frmCalendar]![<nam
=Forms![frmCalendar]![<nam
=Forms![frmCalendar]![<nam
etc.
That's one way to simplify it.
and as gustav said, you could use the link master and child fields. I suggested the other method though as you have a little more control over things.
You mentioned you had a variety of buttons on the main form to control the subforms and it wasn't clear if you had one date vs 7 on the main form.
The master/child links works great when it's a straight parent/child relationship, but usually when you cobble an interface together for something like this (a calendar) or you have an odd situation (like you want to control transacting or change record sources on the fly), you come out ahead if you avoid them.
But as I mentioned in my comment, there's certainly more than one way to do this.
Another would have been to have only one subform and actually change it's recordsource as you moved through the days, but that would assume only one day visible at a time, and it wasn't clear if that was the case or not.
Nothing wrong though with anything that's been suggested.
Jim.
If you have only one date field, then each of the queries could use an offset without needing a separate date field for each on the parent form:
=Forms![frmCalendar]![<nam
=Forms![frmCalendar]![<nam
=Forms![frmCalendar]![<nam
etc.
That's one way to simplify it.
and as gustav said, you could use the link master and child fields. I suggested the other method though as you have a little more control over things.
You mentioned you had a variety of buttons on the main form to control the subforms and it wasn't clear if you had one date vs 7 on the main form.
The master/child links works great when it's a straight parent/child relationship, but usually when you cobble an interface together for something like this (a calendar) or you have an odd situation (like you want to control transacting or change record sources on the fly), you come out ahead if you avoid them.
But as I mentioned in my comment, there's certainly more than one way to do this.
Another would have been to have only one subform and actually change it's recordsource as you moved through the days, but that would assume only one day visible at a time, and it wasn't clear if that was the case or not.
Nothing wrong though with anything that's been suggested.
Jim.
ASKER
This is excellent. As you said, it requires no code. It also only requires the one main query and one subform (that I use seven times). So now when I have to make a change to the layout of a column, I only need to change it in one place rather than duplicating my changes in seven subforms.
Thanks everyone!
Thanks everyone!
You are welcome!
/gustav
/gustav