Schedule Report... maybe?

[BusDriver3]

Hello,
I am trying to create a report in Access that that is based on a starting date and an ending date. Ultimately the report will depict the work-center personnel schedule. The information will be entered via a form and the finished product/report would look like the attached file. Does anyone if this is even possible or how to do it? Any help with this would be great. Thanks
Please see attached file.

[rpeare]

Here's a simple example, it won't work if you put in a non 31 day month but the very simplest thing to do is to have four different reports for each month type (28,29,30,31) then just figure out how many days the current month has before you open a report and open the appropriate report. I'm not going to mess around with deleting controls etc if you want to go that far you can.

[BusDriver3]

Wow ! This is great !! Thanks ... I tried to add a new person via the table but when I did and ran the macro it cleared the tblReportSetup table...

Question: how does the frmReportSetup know which ID to update?

[June7]

Very nice rpeare. I am sure could be coded to handle the varying length months and need only one report object but would be a lot more complicated.

BusDriver3, the tblReportSetup is a temp table, meaning it retains data as needed only for the procedure and is purged each time the procedure runs. Anytime records are added to the permanent tables, this procedure will have to be run again so the temp table will update. I tested adding another record to tblPeople and tblSchedule and ran the procedure. It works. Safest thing to do is have the procedure run as part of the procedure that opens the report. Open report by button click on a form.

Note that this is VBA, not a macro.

This will not prevent records in tblSchedule with overlapping dates for the same person.

[rpeare]

June did a good job of describing so I'll leave it there, I just think I forgot to add the 'open report' command after running the macro that's attached to the button (I can't recall at the moment and I'm on my way to bed so I'm not going to look!) If you just add a line of code at the very end of the ON CLICK event code that's

docmd.openreport <insert report name and preview/print option>

then it should be seamless to the end user as to what's going on.

[rpeare]

one other thing

Question: how does the frmReportSetup know which ID to update?

It doesn't know which ones to update, in order to create a line for all of your drivers (which I assumed you wanted rather than only those who have time off during the given month) you basically have to create a record for every driver for every day of the month. From there it's a simple crosstab query to do the bulk of the work, though as june pointed out it will *not* work if you have a person who has an overlapping day off type.

[BusDriver3]

This works, thanks for your help on this!

[June7]

Hate to throw a wrench into the works but I did some more testing. I picked date range of 8/2/2011 to 8/31/2011 and the code fails. The range must include or extend beyond the first and last days of month. Also, I entered a record in tblSchedule with range of 8/15/2011 to 9/5/2011 and then selected report range 8/1/2011 to 9/1/2011. The report shows an OffTimeCode for day 1 but since none of the days show the month, it is not clear that this report is crossing months.

Suggest instead of having user select date range just have them select a month then in code construct the date range to be the first and last days of that month.

Check out this revision. It will handle all months and only one report object needed. Wasn't as more complicated as I thought it would be.

Textbox conditional formatting can handle only 3 conditions (EDIT: Access 2010 expanded to 50 conditions), which is fine for 3 OffTime types. But if you have more, suggest simply displaying the textbox value and have the legend show codes instead of colors.

If you split the project, temp table should be in the frontend, not a linked table.

If you need something more complex, such as allowing for ranges crossing months, suggest Google search for some projects already built. Might find a freebie out there. This report is like a project management scheduler. I know specialized (and probably expensive - SAP is one) software exists for that, an example might be MS Project (never worked with). I also see Excel used for this.

[joncage]

Hello, I opened your bus example. very nice. I don't seem to see any code on the report that would instruct the text box to color itself anywhere depending on the data, 1,2 or 3. I created a 4th category but number 4 just shows up in txt box? How did you "color" textbox backround based on data values? I suppose I could write a simple function during the onformat event but I did not see where you did this anywhere...HOW did you do that?

Cheers
joncage

Here's a simple example, it won't work if you put in a non 31 day month but the very simplest thing to do is to have four different reports for each month type (28,29,30,31) then just figure out how many days the current month has before you open a report and open the appropriate report. I'm not going to mess around with deleting controls etc if you want to go that far you can.

[June7]

Conditional Formatting. http://www.techonthenet.com/access/f...ond_format.php

Limited to 3 conditions until Access 2010

[joncage]

Wow...been programming in access for a long time and never used that before...thx!

Conditional Formatting. http://www.techonthenet.com/access/f...ond_format.php

Limited to 3 conditions until Access 2010

[rpeare]

2 years old and still getting traction!

I went back and looked at the original example and there are a bunch of overlooked completely! what a sloppy example who the hell wrote that thing anyway!

[June7]

What happened to the attachments? Now I find myself wanting to see this example db again and can't find it. Was hoping to download.

[rpeare]

Without the name I don't know. Is there a way to tell the date on your attachments, You can sort by the date, but I don't see a way to view the actual date. If I can see that I can likely pull it up, I don't think I've ever deleted attachments.

edit
nm got it looking now

[rpeare]

Sorry june, looks like older attachments are deleted by the board. I don't have anything prior to calendar year 2012. Was there something specific to work out?