Crosstab style form

C

Christy Wyatt

I want to be able to enter data for each day of the month, in a grid format.
RowHeaders would be different staff, while columnheaders would be 1-31 days
of the month. The value would be the hours for the day. Total hours for
each employee would be on the left hand side by the name. If it looked like
a Crosstab, that would be outstanding. Any ideas? I'm considering having an
employee record, with 31 fields, and link them each to the month, but that
seems like going around the outside to get to the middle.
 
A

Allen Browne

There's a few ways to approach this.

Let's assume you have a table with fields like this:
EmployeeId relates to Employee.EmployeeID
WorkDate Date/Time
Hours Number (double)

In your crosstab query, you have these fields:
TheYear: Year(WorkDate) Group By Row Heading
TheMonth: Month(WorkDate) Group By Row Heading
EmployeeId Group By Row Heading
TheDay: Day(WorkDate) Group By Column Heading
Hours Sum Value

So, on your continuous form, you have:
- a mechanism to ensure it is filtered to only one month and year,
- employees down the left, and
- 31 text boxes for the 31 days.

Now add an unbound text box with these properties:
Name txtEntry
Format General Number
Tab Stop No
Make it the same width and height as the other 31 text boxes, and set it
behind one of them (Format | Send To Back.)

In the Enter event of each of the 31 text boxes, move this text box into the
same position, copy the value into it, and SetFocus to it. The user can't
tell they are not in the actual box that's bound to a query field, but since
this is an unbound box, they can type a value.

Then in the AfterUpdate event of the unbound box, execute an Update query to
write the value back to the table. Then set the unbound box to Null, requery
the form, and find the right place again.

It does involve some code.
 
C

Christy Wyatt

I understand most of it, but don't understand how the day becomes a part of
the m/d/yy again. I also don't understand how the data is written back to
the table by way of the unbound textbox. Also, how does the textbox relocate
upon focus on the other crosstab grid location on the form.
Christy Wyatt
 
C

Christy Wyatt

I'm afraid that may be beyond my abilities at this point. It involves a
whole lot of code, and I'm not all that experienced.
 
A

Allen Browne

Christy, that part is going to involve writing some code.

Use DateSerial() to piece the date back together.
OpenRecordset() to write to the table.

Set the Left property of the text box to relocate it.
The property is in twips where 1440 twips = 1 inch.
 
C

Christy Wyatt

I'll work with it for a couple of weeks probably before I get back to tell
you if I was able to do it. I really appreciate the input.
 
Top