Calendar Form 3

[edioguardi]

Hi- My company hired a programmer to enhance our scheduling program to include attendance tracking. To try and enhance our ability to use this information I have succesfully linked the main tables to an access database.
This is what he had
NAME DATE LETTERID
nnnn
mmmm
nnn There is no primary key defined- multiple entries
nnn for each employee-
nnnn
mm
mmmm

I need to be able to sort the data in a calendar type form.
Like so:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 (days in a month)
Dec.
Jan. LETTERID as data
Feb.
(Months)

I used an expression to extract the Date parts hoping I could use those to determine the appropriate form field to view the Letter ID.

Here's what it looks like:

NAME DATE LETTERID DATEPARTDAY DATEPARTMONTH NOTES
nnnn 1/6/01 v 6 1 blahblah
mmm 1/13/01 LO 13 1 blahblah
nnnn 1/9/01 S 9 1 blahblah

What's the "easiest" way I can do this without creating more tables? Working with Access2000

I'm barely experience with Access so step by step will help the most- but I would appreciate any type of direction you might be able to give me. Thanks in advance.
Elizabeth

 

[RickSpr]

This is tricky, but it might not be too hard--provided this is really what you want. The reason I say this is that I'm not sure your "calendar" (actually, it's more like a scatter chart) makes sense.

What would you get it the same date appears several times in your table? You might then have three different LETTERIDs to put in one cell in your chart.

I could assume you know this never happens, and go on to describe a way you could do it, but the description is rather complex, and I'd rather avoid working it out possibly for nothing.

If you're sure the dates will never repeat (and if so, why not make the date your key?), I'll need some more info. Do the LETTERIDs in the chart have to be editable? If so, that suggests 366 separate text boxes! Not only would that take a long time to set up, and 1000 lines of code to fill, it would probably perform rather badly! But if it's for display only, we can use one big multi-line text box (with Locked set to Yes) and just calculate the position to plug the LETTERID into, based on your date parts.

Also, will the months always start with December and the days with 1, as shown in your specimen? And what's the maximum length of LETTERID? Rick Sprague

 

[edioguardi]

OK-
I want this to be for view only- the FoxPro program is where the information is being input. I'm guessing from what you've said so far this will be easier.

But of course there can be multiple LetterID's for each datein the main linked table- These will be limited to specific combinations ie: FM (FMLA) and WC (Worker's Comp) or S (Sick Leave) and WC. FM (FMLA) And(Sick Leave). We will need to know by looking at the entire form that someone was out on WC but if they're also using their FMLA- The FM should appear in the calandar. I was hoping I might be able to get those dates to appear in a memo box or Notes Section on the Form. I'm flexible on how that gets done- Not too sure what I can and Can't do there- my test environment does not have duplicate dates per person

LetterID is 1 or 2 characters

Here's what I've done so far: (created just in access- haven't attempted any code yet)
Form (SadAttempt1) with Subform (Calendar)
RecordSorce for SadAttempt is parameters entered in an unbound form (Name)
The fields in the subform have control Source- LETTERID with validation Rule : "Expr1"='12' And "Expr2"='2' with the values changing according to the text box
Now the only problem is that it's not bring up the different LETTERID for each field.

I'm probably going about the wrong way- but that's where I am now- I have no problems re-doing the whole thing if need be. And I haven't attempted to create anything with the Notes field.

By goal with this form is mainly to be able to view an employees entire years attendance on one page- the programmer for the other database created a report that requires about 1 page per 6 weeks- I can use any other ideas you might have if this looks like way too much.

DateParts will exist "12,1,2,3,4...11" and 1,2,3,4,...31" Test environment currently only contains December & January dates- if neccesary to properly test this I can use last years data- just have to Query it.

Thank You
Elizabeth

 

[RickSpr]

I'm afraid I'm having a very hard time understanding your description of what you've done with forms. For example, you said your SadAttempt RecordSource is parameters entered in another form, but that doesn't make sense--RecordSource can only be the name of a table or query, or an SQL SELECT statement. Also, you say the controls on the subform have validation rules, but earlier you said this form was not for user input, in which case the validation rules are irrelevant. (And if those rules are coded the way you say they are, they could never work anyway.)

You may need a bit more study of the basics of forms and form properties before you tackle this. The forms in the NorthWind database are good examples to help you with this. I wouldn't recommend trying to get this basic understanding here on Tek-Tips, because of the length of time between questions and replies.

Let me see if I understand your goal correctly. You want to select all the rows for a particular employee from your tracking table, and from that data generate a chart showing, for each month and day in a 12-month period, any LetterID codes found in the data. The chart will have month on one (the vertical) axis and day on the other. The row/column intersection for each month and day may contain up to 2 LetterID codes, each of which can be 1 or 2 characters.

My overall plan would be to have a single form with a Combo Box containing a list of all employees, and a multi-line text box to hold the chart grid. Label controls on the top and left of the text box would identify the dates and months. Once an employee is chosen, VBA code would read all the rows for that employee, and enter the codes into the appropriate positions in the text box. Does that sound like it would work? There was something else about a memo box containing dates, but I didn't understand what you meant with that.

One other thing I'm not clear on: Will the tracking data always cover a 12-month period that starts with December? Or does the length of the period vary and/or start with some other month? Rick Sprague

 

[edioguardi]

OK so let's scrap most of what I've done- I have a form with the labels already set up except days & months are reversed- Days in verical columns, months in horizontal rows.

I like your idea- and this would allow for 2 codes to appear for a single date?- if so GREAT!

Other than the calendar itself I will need a "notes section" which would consist of text strings from all of the existing attendance entries (ie-
NAME DATE LETTERID DATEPARTM DATEPARTD NOTES
nnnn 12/12/00 M 12 12 12/12/00-was Out-missed by company)

The dates will always be December to end of November.

Thank You so much for helping me with this- I really appreciate it.
ELizabeth

 

[RickSpr]

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:

    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) <> &quot;&quot; Then  ' if any code to show
            ' Calculate row and column from date
            row = DatePart(&quot;d&quot;, rst!Date) - 1
            col = DatePart(&quot;m&quot;, 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 &amp; rst!LetterID &amp; &quot;     &quot;, 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) <> &quot;&quot; Then
            strNotes = strNotes &amp; rst!Notes &amp; 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

 

[edioguardi]

Thank you so much Rick-
Ran into a problem- After inserting the code I tried to view the form in "form View" and got the illegal operation error message: MSACCESS caused an invalid page fault in module MSACCESS.EXE at ....
I can view it in design, but even if I try to delete the event procedure from the properties window and then try to switch views it still crashes Access.
Any ideas?
Elizabeth

 

[RickSpr]

If this recurs, I think your database has gotten damaged. Try this:

Create a new blank database. Choose File>Get External Data>Import... from the menu. Select the damaged database and click Import. In the Import Objects dialog, click each tab in turn and select all the objects (use the Select All button). Then click the Options button to extend the box, and check the Import/Export Specs check box. Finally, click OK to import everything from the old database into the new one.

When you're done, try it out again. You may have repaired the problem.

If that doesn't work, try this:

Open the Calendar form in Design View. Select all the controls by drawing a "rubber rectangle" around them. Copy them to the clipboard. Create a new, blank form and paste all the controls in it. Set the form's properties to match what was in the old Calendar form. Go back to the old form and copy all the code in the form module to the clipboard. Then paste it into the form module for the new form. Compile, save, and test the new form. If it works, delete the old form. That will probably fix it. Rick Sprague

 

[edioguardi]

I was getting errors regarding the Notes text box- so I removed all code regarding that and got the calendar set up- That parts working perfect- Thank you.
I tried to reinsert the txtNotes code and am now getting an error that there is no "Do" statement for the Loop near the end of the entire code - Can you help?

 

[edioguardi]

OK- I rearranged some of the code like so:
' Shut down the recordset
rst.Close
Set rst = Nothing
Set db = Nothing

' Place results in form
TxtGrid = strGrid
txtNotes = strNotes
End If
Loop ' until EOF (end of rows)
End Sub

That has stoped the debug/compile errors. but now it's giving me a run-time error:
Invalid use of Null- in this line of code:
If Trim$(rst!Notes) <> &quot;&quot; Then - this was the original reason why I couldn't get the Notes Box to work the first time. I'll keep trying but let me know if you can help! Thank you
Elizabeth

 

[RickSpr]

Elizabeth,

I copied the code from my post above and pasted it back into the code module I used to test it out. It worked fine. I can only imagine that your error on the Loop statement was a result of you making a mistake while copying it by hand--quite a laborious process! Did you realize you could just copy it to the clipboard and paste it in your event procedure?

I did have a bug in my code, however, and it's in the same &quot;If Trim$(rst!Notes) <> &quot;&quot; Then&quot; statement that's giving you trouble now. That statement won't work right if the Notes field is empty. In my test data, I had filled in all the Notes fields, so I didn't detect the bug while testing. The statement should read:

    If Trim$(rst!Notes &amp; &quot;&quot;) <> &quot;&quot; Then

That won't fix your first problem, the missing &quot;Do&quot; statement, though. That can only have come from a keying error. Rearranging the code to put the Loop statement at the end might prevent the compile error, but the code won't work right with it there. I recommend that you copy my code and paste it, replacing the entire event procedure you have now, then modify the statement above and try again.

Incidentally, my bug should not have caused the Invalid Page Fault message you got. It should have just given you a runtime error instead. It's probable that my bug just happened to reveal a bug in Access that caused the page fault.

Sorry I wasn't there to help during the day Thursday, but I should be able to check in during the day tomorrow. Rick Sprague

 

[edioguardi]

I've got one last question-
I'd like to add the corresponding DATE for every note in the Notes box. I played a little and have figured out how to get todays date to show, but not the corresponding date.

 

[RickSpr]

Easy! Change this line (inside the last If statement):

    strNotes = strNotes & rst!Notes & vbCrLf

If you want a full date in your locale's default format, change it to:

    strNotes = strNotes & rst!Date & ": " & rst!Notes & vbCrLf

If you just want month and day, change it to:

    strNotes = strNotes & Format$rst!Date, "mm/dd") & ": " & rst!Notes & vbCrLf

So did you get the rest working, I hope?

Rick Sprague

 

[edioguardi]

Everything is PERFECT! Thank you so much-
As soon as I reinserted the Notes box code, I got errors on the &quot;Loop adjustment&quot; I had to do earlier, so I fixed that and the including the date to the Notes section worked perfectly. Thank you again.

Oh yeah, the wierd thing about that loop error earlier- is that I typed it the first time I tried, but went back to copy &amp; paste (figuring it was a typo) and still got the same error. But it works now and I'm very grateful!

Elizabeth

 

[edioguardi]

Hi again Rick or whoever else may be able to help.

The calendar Rick helped me create works great except for 2 things.

1. One of the employees has an apostrophy in their last name, this is causing an error in the code when selecting his name. How can I fix this.

2. When I try to print the form- it gets all screwed up. My letter ID's do not stay in the same place in the grid and therefor are not aligned with the Month/Day labels. Is there anything I can do about this?

Then, there was one other thing I wanted to find out if I could do- through reading other posts I probably can't, but it's worth a shot! Can I change the background color for weekends only? Thanks in advance for any help!

 

[RickSpr]

I'll assume the apostrophe problem is occurring in evaluating SQL statements. SQL accepts either apostrophes or quotes (&quot 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.

However, VBA uses quotes as string literal delimiters, so you have to double the quotes when they're in a VBA string literal. Each pair of quotes in a VBA string gets turned into a single quote character. For example, &quot;abc&quot;&quot;def&quot; will come out of VBA as abc&quot;def.

So if you have a VBA expression that's building an SQL statement, such as
&quot;WHERE EmployeeName = '&quot; & txtEmplName & &quot;'&quot;
You can replace the apostrophes with pairs of quotes to get
&quot;WHERE EmployeeName = &quot;&quot;&quot; & txtEmplName & &quot;&quot;&quot;&quot;
This should solve your problem, unless there is a quote character in somebody's name, but that's not likely.

For your second question, I'm afraid there's no easy way. All the data is in a single text box, and a text box can only have one background color. You'd have to do something tricky like using separate list boxes for the weekends and figuring out which list box a number is supposed to go in. That would get pretty complicated. Sorry. Rick Sprague

 

[edioguardi]

Thanks Rick you're a doll! I had assumed I wouldn't have much luck with the second one. Thanks again
Elizabeth

 

[RickSpr]

Hey, Elizabeth, something just occurred to me. Maybe you can set the calendar text box's background to transparent, and have a couple of colored filled rectangles behind it to show through? Rick Sprague

 

[edioguardi]

That sounds like an idea-I'll try it.

By the way the tip about using &quot;&quot;&quot; instead of &quot;'&quot;, it's not producing an error, but it's not pulling the LetterID or Notes into the form. Any other ideas?

Thanks,
Elizabeth

 

[RickSpr]

Wasn't it working before? Or are you saying it doesn't for the that specific employee who has an apostrophe in his/her name?

I don't remember enough of your application to begin to figure out what's wrong. I'm sure it doesn't have anything to do with the quote/apostrophe problem. Maybe you could send it to me so I could figure it out. My email address is handlbar@broughton-sys.com. Rick Sprague