Tab control with one subform on five tabs

[Guest]

I'm attempting to create a time entry form using one main form, a tab control
on the main form, and the same subform on all five tabs of the tab control
(Mon, Tues, Wed etc.).
I want to filter the recordsource of the subform by a date in VBA. There
are five date fields on the main form which calculate the Monday through
Friday dates of the week in which the user enters a date in the main date
field on the main form. The five subsequent subforms are then intended to
match those dates so users can enter time information.
How do I update the recordsource for the subform based on which tab control
is selected? So if a user selects the Tuesday tab, the Tuesday date in the
'Tuesday calculated field' is used to filter the subform's recordset. I'm
guessing that a global variable and possibly a module are involved, but I
don't know how it should flow. Thanks for the help.

Melanie

 

[Graham Mandeno]

Hi Melanie

You should use the LinkMasterFields/LinkChildFields properties of the
subform control and then the filtering will be automatic.

LinkMasterFields should be the name of a *control* on your main form which
contains the selected date.

LinkChildFields should be the name of the *field* in your subform's
recordsource that contains the date you want to filter by.

I don't know if you have anything else (besides the subforms) on your tab
control, but if not then you couold dispense with the tab control entirely
and use an option group (opgDayOfWeek) with one button for each day
(Monday=0, Tuesday=1, etc). Then, add two hidden textboxes to your form:
txtMondaysDate and txtSelectedDate. Set the ControlSource of
txtSelectedDate to:
=[txtMondaysDate] + [opgDayOfWeek]
Also, set LinkMasterFields for the subform to be txtSelectedDate.

When the week is selected, calculate Monday's date and store it in
txtMondaysDate. txtSelectedDate will automatically change as you click
different option buttons.

If you really do need a tab control, then use a similar technique, but use
the page index to adjust the date. Make the tab control transparent and
place the subform on top of it (not on any particular page).

 

[Guest]

Graham,

Thanks for the helpful advice! I do believe my form is working using the
option group. I appreciate your detailed notes, too.

I have another question: If a user wants to enter a new record, is there a
way to default the date field on the continuous subform to equal the date for
that day? So if I want to enter a record on 12/29/04, the Wednesday radio
button is selected, and 12/29/04 is defaulted on my new record.

Thanks,
Melanie

Hi Melanie

You should use the LinkMasterFields/LinkChildFields properties of the
subform control and then the filtering will be automatic.

LinkMasterFields should be the name of a *control* on your main form which
contains the selected date.

LinkChildFields should be the name of the *field* in your subform's
recordsource that contains the date you want to filter by.

I don't know if you have anything else (besides the subforms) on your tab
control, but if not then you couold dispense with the tab control entirely
and use an option group (opgDayOfWeek) with one button for each day
(Monday=0, Tuesday=1, etc). Then, add two hidden textboxes to your form:
txtMondaysDate and txtSelectedDate. Set the ControlSource of
txtSelectedDate to:
=[txtMondaysDate] + [opgDayOfWeek]
Also, set LinkMasterFields for the subform to be txtSelectedDate.

When the week is selected, calculate Monday's date and store it in
txtMondaysDate. txtSelectedDate will automatically change as you click
different option buttons.

If you really do need a tab control, then use a similar technique, but use
the page index to adjust the date. Make the tab control transparent and
place the subform on top of it (not on any particular page).
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm attempting to create a time entry form using one main form, a tab
control
on the main form, and the same subform on all five tabs of the tab control
(Mon, Tues, Wed etc.).
I want to filter the recordsource of the subform by a date in VBA. There
are five date fields on the main form which calculate the Monday through
Friday dates of the week in which the user enters a date in the main date
field on the main form. The five subsequent subforms are then intended to
match those dates so users can enter time information.
How do I update the recordsource for the subform based on which tab
control
is selected? So if a user selects the Tuesday tab, the Tuesday date in
the
'Tuesday calculated field' is used to filter the subform's recordset. I'm
guessing that a global variable and possibly a module are involved, but I
don't know how it should flow. Thanks for the help.

Melanie

 

[Guest]

Off the top of my head, I would recommend chaning your tab control to a
option group, actually you could probably make it look alot similar to a tab
by placing the group buttons horizontally across the form instead of
vertically like most forms use it.

Other than doing that, I would have to see what you are trying to do - can
you do a screen capture and send to my email rolaaus@[d.e-l-e.t.e.]1net.gr