Form displaying 365-day calendar (1 Viewer)

I have a table with daily entries for employees. I want to display a 12-month calendar and highlight each day that contains an entry in the table.

I considered 365 textboxes which is just wrong.
I considered 12 subforms. I'm not sure about child forms.
I considered inserted an Excel object but don't see that as an option.

With the weekday(txtStartDate), I know what day of the week to build my calendar. I would like to dynamically build the table. Can I create labels/textboxes on the fly while reading thru my query?

How would you go about doing this?

What you are asking for is pretty complex ... but could probably be done if you wanted to grind it out.

I've attached a sample that I had gotten a hold of a long time ago. I apologize to whomever so can't give proper credit. It does a month view at a time ....

However, you might look at this to see if this is something you can live with or adapt to your purposes.

HTH,

-dK

>>I considered 365 textboxes which is just wrong.<<
Only slightly wrong in that some years will have 366 days.
But it would only be wrong after something else comes along which is better.

>>I would like to dynamically build the table.<<
I don’t think you would have to dynamically build the table.

>>Can I create labels/textboxes on the fly while reading thru my query?<<
Both yes and no. They can be built on the fly but you would be restricting the database to only be an MDB file and not an MDE file.
In order to build them on the fly you will need to go into design view and you can’t do that in an MDE file.
It is far easier to build 366 labels/text boxes in advance and only show the 29 Feb if that date is valid for that particular year.

This means that, when the Form is opened, you would need to scan through the 366 text boxes and do some formatting for each day.
Visible/enabled if not the 29 Feb, maybe a different colour for weekends/public holidays, change any information display in the label/text box based on the content of the table…that sort of thing.

You should also only have one click event that services all the labels/text boxes.
You may also need one MoseMove event for all labels/text boxes so the MouseMove displays more information than can be displayed in the label/text box.
This in turn means passing a unique key to the handler, maybe the primary key of the table.

It’s all doable but, since screen space would be restricted, it requires a little planning.

Have you got anything visual in mind that you could draw up for us to see?

What I have to work with:

archives table
SIN text - employee number
jour date - day worked
heure number - number of hours
Contains the number of hours worked each day. One day could have multiple entries for an employee.

monday table
date date
This table currently exist so I will likely use it. Simply a list of every Monday.

I want to build an annual calendar for one employee. This is what I have so far:
SELECT distinct a.SIN, m.date,
(select sum(heure) from archives where nas = a.nas and jour = m.date - 1 and heure is not null group by nas, jour) AS Sunday,
(select sum(heure) from archives where nas = a.nas and jour = m.date and heure is not null group by nas, jour) AS Monday,
(select sum(heure) from archives where nas = a.nas and jour = m.date + 1 and heure is not null group by nas, jour) AS Tuesday,
(select sum(heure) from archives where nas = a.nas and jour = m.date + 2 and heure is not null group by nas, jour) AS Wednesday,
(select sum(heure) from archives where nas = a.nas and jour = m.date + 3 and heure is not null group by nas, jour) AS Thursday,
(select sum(heure) from archives where nas = a.nas and jour = m.date + 4 and heure is not null group by nas, jour) AS Friday,
(select sum(heure) from archives where nas = a.nas and jour = m.date + 5 and heure is not null group by nas, jour) AS Saturday
FROM ARCHIVES AS a, monday AS m
WHERE (((a.NAS)="123456789")
AND ((m.date) between #6/7/2009# and #6/7/2010#)
AND ((a.[jour]) Between m.date-1 And m.date+5));

Archives has over 150000 rows so this query takes 5 minutes for one person. Now that the SQL gives me what I want I working to see what the subform will look like.

As far as I can see the form would need to run the query each time it opens and each time you change employee.
If that is going to take 5 minutes then I think it would be unusable.
If you can get the query to run in 5 seconds or less and return 365/366 records, each with a date and total of hours worked on that date then we may be able to set something up.

I would suggest asking the question in the queries forum as to how to speed up the query.

But, at the moment I would not be prepared to design a form which takes 5 minutes to open.

The new table by week didn't quite do the trick. I've decided to build the form with 371 textboxes, populate the calendar based on the start date entered and then colour the calendar based on work, non-work and weekend/holidays. Reading tables from inside VBA is new to me, I found this code online. I believe my problem is from the date in the query. Anyone know why I don't get any records?

Private Sub btnGetInfo_Click()
Dim counter As Integer
Dim dayfield As String
Dim dReportStartDate As Date
Dim dReportCurrentDate As Date
Dim iWeekday As Integer
Dim lngGreen As Long, lngRed As Long, lngYellow As Long

lngGreen = RGB(0, 255, 0)
lngRed = RGB(255, 0, 0)
lngYellow = RGB(255, 255, 0)

iWeekday = weekday(txtStartDate)
dReportStartDate = Choose(iWeekday, [txtStartDate], [txtStartDate] - 1, [txtStartDate] - 2, [txtStartDate] - 3, [txtStartDate] - 4, [txtStartDate] - 5, [txtStartDate] - 6)

Dim MyDB As DAO.Database, MyRec As DAO.Recordset, MyList As String
Set MyDB = CurrentDb
For counter = 1 To 371
dayfield = "txtDay" & Format(counter, "000")
dReportCurrentDate = dReportStartDate + counter - 1
Set MyRec = MyDB.OpenRecordset("Select heure " & _
"from archives " & _
"where nas = '" & txtSIN & "' " & _
"and jour = " & Format(dReportCurrentDate, ("yyyy/mm/dd")) & " " & _
"and heure is not null;")
iWeekday = weekday(dReportCurrentDate)
If Not MyRec.EOF Then
Me(dayfield).BackColor = lngGreen ' Worked on this day
ElseIf iWeekday = 1 Or iWeekday = 7 Then
Me(dayfield).BackColor = lngYellow ' Weekend
Else
Me(dayfield).BackColor = lngRed ' No record
End If
Next counter
MyRec.Close
MyDB.Close
End Sub

I don't have time to really get involved in this topic, other than to say that dk's statement "What you are asking for is pretty complex ... " is a huge understatement! I regularly contribute to half a dozen Access forums and this question arises almost weekly at one or another of them, and I've never seen it solved to the poster's satisfaction, but hope springs eternal.

I will say this, though; you have to keep one very important thing in mind...limits!

Dynamically creating 365 labels/textboxes each time you open the form is definitely out! The reason? Aside from the mdb/mde thing already mentioned, a form in Access is limited to a lifetime maximum of 755 controls! So the third time the form would be opened, creating another 365 controls, it would bomb!

The other limit concerns the underlying Recordsource for the form. The Access limit on fields in a Recordsource is 254.

Lastly, even if it were possible to overcome these limitations, I can't imagine that a screen with 365 or more controls could possibly be used effectively by a user to do anything! I think you really need to sit back and re-think your whole approach on this thing.

Linq ;0)>

Thanks guys for the feedback. It's getting clearer every hour that this is a big task.

The user wishes to see at a glance any break in service for an employee. The original concept was to print an Excel sheet with a 365 day calendar for each employee and check each day they work.

I offered to generate a form that'll go against the database and show days worked in light green, weekends and holidays in yellow, anything in red would show a break in service which may include sick days, etc...

The current process takes 2 minutes to populate all of the textboxes. I believe I know one other way to make it go faster. Basically changing the loop above to get all records in the date range and then populate whichever days the cursor finds.

The issue right now is the date. I've created another thread to handle that question. I'll post more on this when I any make progress.

I have the date issue resolved. Here is what I have so far and my clients are estatic. SIN and name below are hidden. The first button colours the calendar. The other button numbers and places the month names based on the start date selected.



Next step is adding holidays and making a printer friendly version. This has been a great experience so far. I'm learning lots.

Don't know if you are happy with what you have got but here is a snapshot of a control that I have used in the past. It is part of a larger suite of controls (80 plus) that can be used in Access. The whole suite is a bit pricey but soon pays for itself in the variety of controls is offers. If you want to know more let me know.