Hmm...you're leaving it to me to create the whole thing for you. Well, here goes:
I'll assume:
1. Your tracking table is named TrackingTable,
2. Its fields are named Name, Date, LetterID, and Notes,
3. Date is a date/time field and the others are text,
4. The table only contains dates from one December to November period (let me know if this isn't true), and
5. The month name labels are across the top of the table, and the day labels are down the left side.
First, set the following form properties:
Record Source: (blank)
Record Selectors: No
Navigation Buttons: No
Add a combo box control and set the following properties:
Name: cboEmplName
Control Source: (blank)
Row Source Type: Table/Query
Row Source: SELECT DISTINCT Name FROM TrackingTable ORDER BY Name;
Limit To List: Yes
Add a text box control, delete its label, and position and resize it to fill the calendar area under the months. We'll have to resize it later when we see where the LetterIDs end up. Set the following properties:
Name: txtGrid
Control Source: (blank)
Enabled: No
Locked: Yes (or No if you prefer)
Tab Stop: No
Font Name: Courier New
Add another text box control and set its properties as follows:
Name: txtNotes
Control Source: (blank)
Enabled: No
Locked: Yes (or No if you prefer)
Tab Stop: No
Save the form as frmCalendar (SadAttempt1 is too pessimistic!).
Click on cboEmplName, then in the Properties scroll down to the After Update line, right click in the box besides it and choose Build.... This will open a code module with a few lines of code in it. The cursor is between the Private Sub line and the End Sub line.
At this point, if you're using Access 2000, choose Tools>References... from the menu. This will open a References dialog box with a list of items and check boxes in it. The checked items are listed at the top, then the unchecked items are listed alphabetically. If the checked items include one starting with "Microsoft ActiveX Data Objects", remove the check mark. Next scroll down the list and find "Microsoft DAO 3.6 Object Library" and set the check mark on that one. Click the OK button. Then go back to the code module.
Copy and paste the following code between the Private Sub cboEmplName_AfterUpdate and End Sub lines:
Code:
Const SQLTemplate = "SELECT Date, LetterID, Notes FROM TrackingTable WHERE Name = "
Const CellSize = 5 ' size of one cell in grid
Const RowSize = 12 * CellSize + 2 ' size of one line in grid
Const GridSize = 31 * RowSize - 2 ' size of grid, no CrLf on last line
Dim strSQL As String ' SQL query for recordset
Dim db As Database, rst As Recordset
Dim strGrid As String ' string in which grid is built
Dim strCell As String ' working string for one cell
Dim strNotes As String ' text to display in txtNotes control
Dim row As Integer, col As Integer ' grid "array indexes"
Dim i As Integer
' If no employee name chosen, clear grid and notes and exit
If IsNull(cboEmplName) Then
txtGrid = ""
txtNotes = ""
Exit Sub
End If
' Initialize the calendar grid
strGrid = String$(GridSize, " ")
For i = RowSize - 1 To GridSize Step RowSize
Mid$(strGrid, i, 2) = vbCrLf ' put CrLf at end of each line except last
Next i
'---------------------------------------------------
' Delete the following lines after resizing txtGrid
strNotes = "XXXX XXXX XXXX XXXX XXXX XXXX "
strNotes = strNotes & strNotes & vbCrLf
strGrid = ""
For i = 1 To 31
strGrid = strGrid & strNotes
Next i
strGrid = Left$(strGrid, GridSize)
strNotes = ""
'---------------------------------------------------
' Open the recordset and prepare to read data
strSQL = SQLTemplate & "'" & cboEmplName & "'"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
Do Until rst.EOF
' Process LetterID in each row for employee
If Trim$(rst!LetterID) <> "" Then ' if any code to show
' Calculate row and column from date
row = DatePart("d", rst!Date) - 1
col = DatePart("m", rst!Date) Mod 12
' Extract the current contents of the cell
strCell = Trim$(Mid$(strGrid, row * RowSize + col * CellSize + 1, CellSize))
' If current letter id is not already in cell,
If InStr(1, strCell, rst!LetterID) = 0 Then
' Append letter id to cell and pad to cell size
strCell = Left$(strCell & rst!LetterID & " ", CellSize)
' Put cell back into grid
Mid$(strGrid, row * RowSize + col * CellSize + 1, CellSize) = strCell
End If
End If
' Accumulate any non-blank notes
If Trim$(rst!Notes) <> "" Then
strNotes = strNotes & rst!Notes & vbCrLf
End If
' Move to the next row for this employee
rst.MoveNext
Loop ' until EOF (end of rows)
' Shut down the recordset
rst.Close
Set rst = Nothing
Set db = Nothing
' Place results in form
txtGrid = strGrid
txtNotes = strNotes
Ok, you're done. Choose Debug>Compile from the menu to make sure there are no syntax errors. Then go back to the form, save it, and switch it to Form View. Choose an employee from the combo box. You should see a lot of Xs, and either some white space at the end of each line, or some shorter lines of Xs.
You'll have to go back and forth between Design View and Form View while you get the text box sized right. If you have some short lines, make it wider. If you have too much white space on the right side of every line, make it narrower. You need to size it so that there's just one character's worth of white space on the right of each line. It's pretty wide. If you don't have room on the screen to make the text box (and form) wide enough, you might have to change the text box's Font Size property to a smaller number. (If you do that, you'll probably need to do it for the month labels as well.) I'm using 1024x768 video resolution, and my text box came out 4.5417" high and 4.4167" wide.
Once you've minimized the white space on the ends of the lines, start adjusting the height so you have just enough room for 31 lines of Xs. When you get that fixed, you can line up your month and day labels. Each unit of Xs horizontally is the space reserved for one month.
After you get the text box sized, go back to the code module. Remove the section between the lines, the part marked "Delete the following lines after resizing txtGrid". Save it, go back to the form, and test it out. I think you'll have what you want.
I'll check back in case you have any problems. Rick Sprague
as string literal delimiters, so one way to fix it is to replace each apostrophe in a string that winds up as SQL code with a quote.