Populate Multiple Subforms in a Tab Control in VBA (1 Viewer)

[Wayne]

I run a small trucking company. I have a table for orders (Deliveries). I
know that grouping on a form (like on a report) is not possible, however, is
it possible to use a tab control on a form, with 7 tabs, one for each day of
the wek, and to populate each page based on the week selected on the opening form (i.e. all Mondays jobs appear on the subform on the Monday tab, and so, on and so on...). The opening form would have either a calendar control to select the week of the year or a date picker.

My idea would be to create a select query (the same one) for each of the 7 subforms (all sub-forms would be the same), but with the selection criteria being fed by the day of the week from 1 to 7, so that each subform on each daily tab contains only the jobs for that day on the tab. Day 1 (Sunday) would be on tab Page 0, Day 2 (Monday) would be on Tab Page 1, and so on...

I know that in VBA you can select a calendar week by number, or by current week, and each weekday by number, but I'm not sure how to write the code to do this. Can anyone shed some light on this?

Wayne

 

[John Big Booty]

Have a look at the various available Date Manipulation function available here.

What you could do is have an unbound subform on your main form, that is populated via a dynamic SQL statement. This SQL would be built dependant on the Button(s) that the user clicks.

 

[John Big Booty]

OK; I've had a play in my sand box DB, and come up with the attached it uses a list box to display the results, and a simple query.

You should be able to take it from here

Note: The text boxes are visible only for illustrative purposes, you could simply hide them, in your working DB.

 

[Wayne]

Thanks John. This will work nicely. I really appreciate the help.