Limiting Dates on SubForms for a Weekly Project Planner

[dpimental]

I have created a scheduler of sorts for an automotive company.

What I have done is ...
Created a Main Form that has 5 subforms, each corresponding to (i.e. containing) the 5 weekdays (i.e. they have daily scheduling information).

Each subform has a half hour range (from 7:30 to 5:30), and a check box, as an indicator of more detail being present.

To see the "more detail" or add "more detail", you just double click the half hour range, and a pop up form appears on your screen.

I have developed form to build the calendar information for the weekly schedule. It builds a month of information at a time. I have one table that determines which months of which years have already been built, another table for the date / day information (date, weekdayname, week#, weekdaynumber, ect), and another table that contains the daily schedule information (half hour segments, appointment type, customer name, ect).

My dilema, is that I have setup the subform so that they run on a query based on the Day of the Week (i.e. 5 subforms, 5 days of the Week).

How do I ...

1. How can a user select the default schedule, which would be based on the current week# (which can be gotten from the curent date (using DLookup, or some other such mechanism).

2. How can a user select a different schedule based on a differnt date/week#.

3. How can I wrap this up neetly / seemlessly. David Pimental
(US, Oh)
dpimental@checkfree.com

 

[lameid]

The dateAdd funtion allows you to add and subtract (add negative values) weeks to a date. You can use the Weekday function to determine the day of the week of the first of the month and base your week number based on that. Once you know when the first week begins, a lot of arithmetic can get you there. Sometimes the first week begins on the first Monday of the month, other places the first workday of the month starts the first week and Friday ends the first week, and other places do less intuitive things.

 

[dpimental]

Thanks, I have all the date stuff.

What I need to do is alter the RecordSource of SubForms, either using the default (todays date) or a selected

And I want the users to do 1 of 2 things.

1. Open the default schedule.
This would take looking up the current date, getting the curreent week# and filtering the query for that week#.

2. Open up a specified schedule.
This would take opening a for with a combobox with a list of all the schedule dates added to the database. Then use the date selected to find the week # to filter the queries accordingly.
David Pimental
(US, Oh)
dpimental@checkfree.com

 

[lameid]

Sounds like the easiest solution is to add the week number to your subforms and include it in the child fields. In the master field, reference a text box (possibly invisible) that calculates the week number based on the date entered in a combo box whose default value is =date.

You could also have the subform's query reference dates that are calculated in text boxes on the mainform for criteria. Then you simply need to requery the forms on the afterupdate event of the combobox which incidently calculates the values for the text boxes... You'd have to calculate the first day of the week and the last day in this scenario.

 

[dpimental]

Yes, I added the "Week#" field to the master form, had to fix the relationship (one to many) and linked child and master fields together and then opened the main form with a filter of "Week = " & weeknumber.

Simple, Right?~! David Pimental
(US, Oh)
dpimental@checkfree.com