Print Vacation Calendar by the month 4

[PlumDingo]

I have a database that currently has all Scheduled vacation for the year in a table. I have been trying to convince my co-workers to come out of Excel and use this instead. The only problem is that they want a report of the Scheduled vacation to come out in a monthly calender format where on each day it tells you who is off.

There are two options:

On the y axis the Employee name grouped by classification with the day of the month along the x axis and there is maybe a check off box or something next to the employee name denoting whether or not they are off and the number of hours.

Another option would be to have an actual calendar print out (a traditional monthly calendar) that would list only the people who are off for that day in the box.

The size of the report is not realy a constriction because currently we print out our Excel calendar on an E sized drawing paper.

I have looked at the link:

http://www.invisibleinc.com/divFiles.cfm?divDivID=4

and it is concerned with appointments and on a weekly basis. My use is not really for appointments but mostly scheduling purposes to let us supervisors know what resources we have available that day.

Is any of this possible.

Please let me know if you need more information on the database. My baby is crying and I have to go and get him.

Thanks,

FJ

 

[dhookom]

The link included in your posting has samples for a monthly calendar that should work. One of the samples is appointments but there should also be a calendar style report. If it isn't in there, I know I put one in the Corp Tech Demos. The calendar displays delivery dates from the northwind database.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Glad2000]

PlumDingo,
the link you have is marvelous!!!
Go to Corp Tech Demos and download Corp Tech Demos. Then when you open it and the Main Menu (Business Results Demos Menu) appears, choose Calendar Format Report button and you will see what you are looking for. Think your days off like appointments. That is all.
And thank Duane for all these excellent reports.

 

[PlumDingo]

Thank you guys so much. That demo has really helped me out. I was even able to figure out how to configure the whole thing for a C sized drawing. Here is my final question though, I need to word wrap the information. Otherwise around the holidays when we have a lot of people off, the list would be outrageous! Is there a way that I can create columns for each date so that when the number of people in the first column hits 6 for example it starts writing the 7th person on column two and then if necessary column three. I hope this is possible, otherwise, I am going to have a huge sheet.

Thanks for your help!

An aside: Is there a way to word wrap the Code Editor window so that when it reaches the end of the window it doesn't keep going but automatically goes to a new line keeping the code intact?


France
Just trying to get by.

 

[dhookom]

Which solution did you end up using? Do you have a calendar with subreports or what?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[PlumDingo]

I have a calendar with subreports. I incorporated the calendar demo that prints the
orders table on a month calendar format. So now I have a month with a list in each box that lists the person(s) on vacation that day.

France
Just trying to get by.

 

[dhookom]

You can use a multiple column subreport. It would be very difficult to have this subreport print "down then across". It is much easier to print "across then down". You could print down then across if you set the height of the subreport control on the main report and didn't allow it to grow.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[PlumDingo]

Okay across then down is perfectly fine. Stupid question, I just learned how to manage subreports with your demo. How do I do a multiple column subreport?

France
Just trying to get by.

 

[dhookom]

Open the subreport in design view and select File|Page Setup and enter the property values required.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[PlumDingo]

Thanks that works perfectly!!

France
Just trying to get by.

 

[PlumDingo]

Duane,

I got the calendar to work with the colums, I just have one more question. I am having trouble with the lines printing properly. I really want the square to be the same size regardless of what information is printed out so that it will more look like a uniform calendar. With the information being printed out in columns, I should have enough space on my D sized sheet. My problem is this, I am unable to get the bottom line to print at the bottom of the sheet. U have a line that it in the footer that is supposed to print mo matter what but it doesn't seem to work. It looks like a calendar but the bottom line does not print. My code looks like this:


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim lngMaxHeight As Long
Dim i As Integer
lngMaxHeight = Me.txtDay0.Height 'set the max height to be the minimum height of the txt box

'Go through and find the max height of the output so that all the data will fit in the box
For i = 0 To 6
If lngMaxHeight < Me.txtDay0.Height + Me("subVacation Schedule" & i).Height Then
lngMaxHeight = Me.txtDay0.Height * 1 + Me("subVacation Schedule" & i).Height
End If
Next

'Now make all the boxes the same height if there is no data.
If lngMaxHeight = Me.txtDay0.Height Then
lngMaxHeight = Me.txtDay0.Height * 12
Else
lngMaxHeight = Me.txtDay0.Height * 10 + lngMaxHeight
End If

'Draw a line at the far most right of the form first so that I can use a loop to
'draw the lines along the left of the text boxes on the form
Me.Line (Me.Width, 0)-(Me.Width, lngMaxHeight)

'Draw a line starting at the the same height as the day name on the left of the txt box
'and with a height as tall as the lngMaxHeight determined from above.
'Since this will be a line not a rectangle the x coordinate will be the same for both.
For i = 0 To 6
Me.Line (Me("txtDay" & i).Left, 0)-(Me("txtDay" & i).Left, lngMaxHeight)
Next

End Sub



France
Just trying to get by.

 

[PlumDingo]

Sorry, I hit the wrong button .

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    Dim lngMaxHeight As Long
    Dim i As Integer
    lngMaxHeight = Me.txtDay0.Height 'set the max height to be the minimum height of the txt box
    
    'Go through and find the max height of the output so that all the data will fit in the box
    For i = 0 To 6
        If lngMaxHeight < Me.txtDay0.Height + Me("subVacation Schedule" & i).Height Then
            lngMaxHeight = Me.txtDay0.Height * 1 + Me("subVacation Schedule" & i).Height
        End If
    Next
    
    'Now make all the boxes the same height if there is no data.
        If lngMaxHeight = Me.txtDay0.Height Then
            lngMaxHeight = Me.txtDay0.Height * 12
        Else
            lngMaxHeight = Me.txtDay0.Height * 10 + lngMaxHeight
        End If
              
    'Draw a line at the far most right of the form first so that I can use a loop to
    'draw the lines along the left of the text boxes on the form
    Me.Line (Me.Width, 0)-(Me.Width, lngMaxHeight)
    
    'Draw a line starting at the the same height as the day name on the left of the txt box
    'and with a height as tall as the lngMaxHeight determined from above.
    'Since this will be a line not a rectangle the x coordinate will be the same for both.
    For i = 0 To 6
        Me.Line (Me("txtDay" & i).Left, 0)-(Me("txtDay" & i).Left, lngMaxHeight)
    Next
    
End Sub

I have tried to make each one the same and have tried playing with the formatting but the bottom line for the last week of the month, does not show! Can you help?

France
Just trying to get by.

 

[dhookom]

I'm not sure where your issue is. I don't recall any code like:

       If lngMaxHeight = Me.txtDay0.Height Then
            lngMaxHeight = Me.txtDay0.Height * 12
        Else
            lngMaxHeight = Me.txtDay0.Height * 10 + lngMaxHeight
        End If

I used a rectangle at the bottom of the week (detail section).

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Jedi420]

Hello,

Your Demos have really helped out alot, Duane, especially with making a Gant Chart for software bundles. I'm having trouble though incorporating the monthly calendar with the bundles. The bundles have a start date and an end date. Software projects in the database associate themselves with a particular bundle. Since the bundles deal with a date range as opposed to a specific delivery date (as in the orders table), I remove the child and master links between the Report and subreports. I want each day of the calendar to show which bundles are still going on. In the subreport's print event, I have the following code:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

Dim i As Integer

    For i = 0 To 6

     If((CDate(Reports!rptShipRequiredDates("txtDay" _ &  
          i).Value) >= txtStart.Value) And _
        (CDate(Reports!rptShipRequiredDates("txtDay" _ & 
          i).Value) <= txtFinish.Value)) Then
            Me.MoveLayout = True
            Me.PrintSection = True
        Else
           Me.MoveLayout = False
            Me.PrintSection = False
           Cancel = True
        End If
   Next i

End Sub

What happens is that, bundles end up showing up for for a whole week, when they were supposed to end the wednesday of that week of sometimes none show up at all. It seems like the above code, though I would think would just be local to itself and not affect other subreports, is being called 7 times (once for each subreport) and the me.printsections and whatnot apply to all of them as opposed to say, srptShipRequired3's code only applying to srptShipRequired3. I hope I explained this well enough for someone to get an idea of what my problem is. Any help at all would be appreciated. Thanks.

- Jedi420

A man who has risked his life knows that careers are worthless, and a man who will not risk his career has a worthless life.

 

[dhookom]

I would not touch the report code to accomplish this. I would have a table of all dates and then set up the subreport's record source to include 4 similar records if an event runs for 4 days.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[PlumDingo]

Yes Duane, I know but that does not work for me (adding a box at the end of the detail section). I added the code because I was trying to format the calendar so that each box was the same. Your code modifies the width of the box depending on how many entries you have for that day. Is there something that I can do to accomplish a uniform calendar if my code seems to be off?

France
Just trying to get by.

 

[dhookom]

Where did my calendar code modify the width of anything? The calendar report only drew lines.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Jedi420]

Duane,

Thanks so much for your help. It works perfectly now, excecpt for a rather large table full of dates and non-uniform rectangles, but hey, what can ya do? I think I can get all the rectangles to at least be square if there is no data in them. Have a star for your Access prowess, my friend!

- Jedi420

A man who has risked his life knows that careers are worthless, and a man who will not risk his career has a worthless life.

 

[PlumDingo]

I thought that the whole purpose of the code below was t find the max height and draw a line matching the maximum height of the subreport with the most data. f not that i what it did.

lngMaxHeight = Me.txtDay0.Height 'set the max height to be the minimum height of the txt box
    
    'Go through and find the max height of the output so that all the data will fit in the box
    For i = 0 To 6
        If lngMaxHeight < Me.txtDay0.Height + Me("subVacation Schedule" & i).Height Then
            lngMaxHeight = Me.txtDay0.Height * 1 + Me("subVacation Schedule" & i).Height
        End If
    Next

Maybe, I am mistaken, but you don't know how long to draw the lines without the code above. Now I am trying to set the height of the lines to be the same all the time. My box that I drew at the end of thedetail secion does not appear at the bottom of the form , however. I am at a loss.

France
Just trying to get by.

 

[dhookom]

This code should find the maximum height of any of the subreports. It doesn't do anything about width of controls. The code I questioned multiplied some heights by either 10 or 12.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.