Minimizing Apparent Recalculation

[Sprinks]

I'm using a data input form that resembles an Excel spreadsheet that has 14
columns representing 2 weeks of data, which I later post to normalized
tables. Unbound controls at the end of each row and column calculate totals,
which I force by a Me.Recalc in the AfterUpdate event of each input box.

For a given entry, only the given column and row's totals change, but the
Recalc temporarily blanks all other totals as well, before displaying the
"new" calculated values. This is inelegant; I would prefer it to work just
like Excel, where the only the cells with new values change.

Does anyone know how I can achieve this?

Thank you.

Sprinks

 

[Pat Hartman\(MVP\)]

Me.Recalc says to recalc the entire form. That's why it blanks. You can
recalc individual fields - Me.SomeField.Recalc. If you move the
calculations to the query, you won't need to recalc at all. Access will
take care of everything. Or if you move the calculation to the control
source, Access will also take care of everything:
=some calculation

 

[Klatuu]

If this is just visual thing and you want it to look better, you can turn off
screen udating prior to the recalc and turn it back on after.
Application.Echo False
'Do the Recalc
Application.Echo True

You may have to do a repaint, but I am not sure without testing it.
Me.Repaint

 

[Sprinks]

Pat,

I could not get Me.SomeField.Recalc to work; it said "Method or data member
not found." The Help implied that it was a method of the Form object only.
I am using Access 2003 SP2. However, the query approach you suggested is
superior anyway, and have gotten part of it to work the way I want, but would
appreciate help with the rest.

I have a main form based on a dummy table called Timesheet:

Timesheet
----------------
StaffID Integer (Primary Key--Foreign Key to Staff)
PeriodStartDate Date/Time

I have two subforms based on queries of the same table, TimesheetDetail.
One is for projects we bill to Clients, the other for Administrative
activities.

TimeSheetDetail
--------------------------
TSDetailID AutoNumber (PK)
StaffID Integer (Foreign Key to Timesheet and Staff)
ProjectNumber Text (Foreign Key to Projects--Text because it contains
leading 0's)
DayHours1 Integer
DayHours2 Integer
....(other days in the 2-week period beginning with Timesheet.PeriodStartDate)
DayHours14 Integer

This non-normalized structure is to make data entry easy. Each PM can enter
his hours just by moving up and down the form. When the period is complete,
the user presses a "Post" button, which posts the data to a normalized
TimeRecords table, clears the dummy record (except for the Project names),
and resets the PeriodStartDate.

TimeRecords
---------------------
TimeRecordID AutoNumber (PK)
StaffID Integer
ProjectNumber Text
WorkDate Date/Time
Hours Integer

The top part of the form looks something this:

Name: Sprinks
Period Starting: 2/26/06

Project 2/26 2/27 2/28 .... 3/12 Total
Zoo Aviary 3 4 5 0
12
Free Library of Phila. 5 2 3 8 18

The total column was added, based on your suggestion as a calculated field
in the underlying query:
Nz([DayHours1])+Nz([DayHours2])+...Nz([DayHours14]). As the user enters or
changes data, the total changes without any code executed or flickering,
which is great; it feels just like the spreadsheet that preceded it.

I would also like to provide column totals. Based on the above data they
would be:
8 6 8 8
30

For this I created a Totals query as the basis for a third subform, linked
by the StaffID:

SELECT
TimeSheetDetail.StaffID,
Sum(TimeSheetDetail.DayHours1) AS SumOfDayHours1,
Sum(TimeSheetDetail.DayHours2) AS SumOfDayHours2,
Sum(TimeSheetDetail.DayHours3) AS SumOfDayHours3,
Sum(TimeSheetDetail.DayHours4) AS SumOfDayHours4,
Sum(TimeSheetDetail.DayHours5) AS SumOfDayHours5,
Sum(TimeSheetDetail.DayHours6) AS SumOfDayHours6,
Sum(TimeSheetDetail.DayHours7) AS SumOfDayHours7,
Sum(TimeSheetDetail.DayHours8) AS SumOfDayHours8,
Sum(TimeSheetDetail.DayHours9) AS SumOfDayHours9,
Sum(TimeSheetDetail.DayHours10) AS SumOfDayHours10,
Sum(TimeSheetDetail.DayHours11) AS SumOfDayHours11,
Sum(TimeSheetDetail.DayHours12) AS SumOfDayHours12,
Sum(TimeSheetDetail.DayHours13) AS SumOfDayHours13,
Sum(TimeSheetDetail.DayHours14) AS SumOfDayHours14,
Sum(Nz([DayHours1])+Nz([DayHours2])+Nz([DayHours3])+Nz([DayHours4])+Nz([DayHours5])+Nz([DayHours6])+Nz([DayHours7])+Nz([DayHours8])+Nz([DayHours9])+Nz([DayHours10])+Nz([DayHours11])+Nz([DayHours12])+Nz([DayHours13])+Nz([DayHours14])) AS RT
FROM TimeSheetDetail
GROUP BY TimeSheetDetail.StaffID;

The problem is that the totals do not update unless I leave the current main
form record (which the user will not be able to do because their access will
be restricted to their own timesheet). The following code also failed:

Me.Parent.Form![DayHoursTotal].Requery

where DayHoursTotal is the name of the subform control on the main form.

Do you have any suggestions?

Thank you.

Sprinks

 

[Sprinks]

Klatuu,

Thank you for the suggestion; it improves it.

Sprinks

 

[Pat Hartman\(MVP\)]

No query is necessary. You can add a footer to the subform and 14 controls
on the footer.

=Sum(DayHours1)
=Sum(DayHours2)
.....

This method will update the sums when the user leaves the current record to
go to a new record in the subform or clicks back into the mainform.

Pat,

I could not get Me.SomeField.Recalc to work; it said "Method or data
member
not found." The Help implied that it was a method of the Form object
only.
I am using Access 2003 SP2. However, the query approach you suggested is
superior anyway, and have gotten part of it to work the way I want, but
would
appreciate help with the rest.

I have a main form based on a dummy table called Timesheet:

Timesheet
----------------
StaffID Integer (Primary Key--Foreign Key to Staff)
PeriodStartDate Date/Time

I have two subforms based on queries of the same table, TimesheetDetail.
One is for projects we bill to Clients, the other for Administrative
activities.

TimeSheetDetail
--------------------------
TSDetailID AutoNumber (PK)
StaffID Integer (Foreign Key to Timesheet and Staff)
ProjectNumber Text (Foreign Key to Projects--Text because it contains
leading 0's)
DayHours1 Integer
DayHours2 Integer
...(other days in the 2-week period beginning with
Timesheet.PeriodStartDate)
DayHours14 Integer

This non-normalized structure is to make data entry easy. Each PM can
enter
his hours just by moving up and down the form. When the period is
complete,
the user presses a "Post" button, which posts the data to a normalized
TimeRecords table, clears the dummy record (except for the Project names),
and resets the PeriodStartDate.

TimeRecords
---------------------
TimeRecordID AutoNumber (PK)
StaffID Integer
ProjectNumber Text
WorkDate Date/Time
Hours Integer

The top part of the form looks something this:

Name: Sprinks
Period Starting: 2/26/06

Project 2/26 2/27 2/28 .... 3/12
Total
Zoo Aviary 3 4 5 0
12
Free Library of Phila. 5 2 3 8
18

The total column was added, based on your suggestion as a calculated field
in the underlying query:
Nz([DayHours1])+Nz([DayHours2])+...Nz([DayHours14]). As the user enters
or
changes data, the total changes without any code executed or flickering,
which is great; it feels just like the spreadsheet that preceded it.

I would also like to provide column totals. Based on the above data they
would be:
8 6 8 8
30

For this I created a Totals query as the basis for a third subform, linked
by the StaffID:

SELECT
TimeSheetDetail.StaffID,
Sum(TimeSheetDetail.DayHours1) AS SumOfDayHours1,
Sum(TimeSheetDetail.DayHours2) AS SumOfDayHours2,
Sum(TimeSheetDetail.DayHours3) AS SumOfDayHours3,
Sum(TimeSheetDetail.DayHours4) AS SumOfDayHours4,
Sum(TimeSheetDetail.DayHours5) AS SumOfDayHours5,
Sum(TimeSheetDetail.DayHours6) AS SumOfDayHours6,
Sum(TimeSheetDetail.DayHours7) AS SumOfDayHours7,
Sum(TimeSheetDetail.DayHours8) AS SumOfDayHours8,
Sum(TimeSheetDetail.DayHours9) AS SumOfDayHours9,
Sum(TimeSheetDetail.DayHours10) AS SumOfDayHours10,
Sum(TimeSheetDetail.DayHours11) AS SumOfDayHours11,
Sum(TimeSheetDetail.DayHours12) AS SumOfDayHours12,
Sum(TimeSheetDetail.DayHours13) AS SumOfDayHours13,
Sum(TimeSheetDetail.DayHours14) AS SumOfDayHours14,
Sum(Nz([DayHours1])+Nz([DayHours2])+Nz([DayHours3])+Nz([DayHours4])+Nz([DayHours5])+Nz([DayHours6])+Nz([DayHours7])+Nz([DayHours8])+Nz([DayHours9])+Nz([DayHours10])+Nz([DayHours11])+Nz([DayHours12])+Nz([DayHours13])+Nz([DayHours14]))
AS RT
FROM TimeSheetDetail
GROUP BY TimeSheetDetail.StaffID;

The problem is that the totals do not update unless I leave the current
main
form record (which the user will not be able to do because their access
will
be restricted to their own timesheet). The following code also failed:

Me.Parent.Form![DayHoursTotal].Requery

where DayHoursTotal is the name of the subform control on the main form.

Do you have any suggestions?

Thank you.

Sprinks

Me.Recalc says to recalc the entire form. That's why it blanks. You can
recalc individual fields - Me.SomeField.Recalc. If you move the
calculations to the query, you won't need to recalc at all. Access will
take care of everything. Or if you move the calculation to the control
source, Access will also take care of everything:
=some calculation

 

[Sprinks]

Pat,

This is the approach I'm currently using, I just wish it updated the sums
automatically. Thanks for your help.

Sprinks

No query is necessary. You can add a footer to the subform and 14 controls
on the footer.

=Sum(DayHours1)
=Sum(DayHours2)
.....

This method will update the sums when the user leaves the current record to
go to a new record in the subform or clicks back into the mainform.

Pat,

I could not get Me.SomeField.Recalc to work; it said "Method or data
member
not found." The Help implied that it was a method of the Form object
only.
I am using Access 2003 SP2. However, the query approach you suggested is
superior anyway, and have gotten part of it to work the way I want, but
would
appreciate help with the rest.

I have a main form based on a dummy table called Timesheet:

Timesheet
----------------
StaffID Integer (Primary Key--Foreign Key to Staff)
PeriodStartDate Date/Time

I have two subforms based on queries of the same table, TimesheetDetail.
One is for projects we bill to Clients, the other for Administrative
activities.

TimeSheetDetail
--------------------------
TSDetailID AutoNumber (PK)
StaffID Integer (Foreign Key to Timesheet and Staff)
ProjectNumber Text (Foreign Key to Projects--Text because it contains
leading 0's)
DayHours1 Integer
DayHours2 Integer
...(other days in the 2-week period beginning with
Timesheet.PeriodStartDate)
DayHours14 Integer

This non-normalized structure is to make data entry easy. Each PM can
enter
his hours just by moving up and down the form. When the period is
complete,
the user presses a "Post" button, which posts the data to a normalized
TimeRecords table, clears the dummy record (except for the Project names),
and resets the PeriodStartDate.

TimeRecords
---------------------
TimeRecordID AutoNumber (PK)
StaffID Integer
ProjectNumber Text
WorkDate Date/Time
Hours Integer

The top part of the form looks something this:

Name: Sprinks
Period Starting: 2/26/06

Project 2/26 2/27 2/28 .... 3/12
Total
Zoo Aviary 3 4 5 0
12
Free Library of Phila. 5 2 3 8
18

The total column was added, based on your suggestion as a calculated field
in the underlying query:
Nz([DayHours1])+Nz([DayHours2])+...Nz([DayHours14]). As the user enters
or
changes data, the total changes without any code executed or flickering,
which is great; it feels just like the spreadsheet that preceded it.

I would also like to provide column totals. Based on the above data they
would be:
8 6 8 8
30

For this I created a Totals query as the basis for a third subform, linked
by the StaffID:

SELECT
TimeSheetDetail.StaffID,
Sum(TimeSheetDetail.DayHours1) AS SumOfDayHours1,
Sum(TimeSheetDetail.DayHours2) AS SumOfDayHours2,
Sum(TimeSheetDetail.DayHours3) AS SumOfDayHours3,
Sum(TimeSheetDetail.DayHours4) AS SumOfDayHours4,
Sum(TimeSheetDetail.DayHours5) AS SumOfDayHours5,
Sum(TimeSheetDetail.DayHours6) AS SumOfDayHours6,
Sum(TimeSheetDetail.DayHours7) AS SumOfDayHours7,
Sum(TimeSheetDetail.DayHours8) AS SumOfDayHours8,
Sum(TimeSheetDetail.DayHours9) AS SumOfDayHours9,
Sum(TimeSheetDetail.DayHours10) AS SumOfDayHours10,
Sum(TimeSheetDetail.DayHours11) AS SumOfDayHours11,
Sum(TimeSheetDetail.DayHours12) AS SumOfDayHours12,
Sum(TimeSheetDetail.DayHours13) AS SumOfDayHours13,
Sum(TimeSheetDetail.DayHours14) AS SumOfDayHours14,
Sum(Nz([DayHours1])+Nz([DayHours2])+Nz([DayHours3])+Nz([DayHours4])+Nz([DayHours5])+Nz([DayHours6])+Nz([DayHours7])+Nz([DayHours8])+Nz([DayHours9])+Nz([DayHours10])+Nz([DayHours11])+Nz([DayHours12])+Nz([DayHours13])+Nz([DayHours14]))
AS RT
FROM TimeSheetDetail
GROUP BY TimeSheetDetail.StaffID;

The problem is that the totals do not update unless I leave the current
main
form record (which the user will not be able to do because their access
will
be restricted to their own timesheet). The following code also failed:

Me.Parent.Form![DayHoursTotal].Requery

where DayHoursTotal is the name of the subform control on the main form.

Do you have any suggestions?

Thank you.

Sprinks

Me.Recalc says to recalc the entire form. That's why it blanks. You can
recalc individual fields - Me.SomeField.Recalc. If you move the
calculations to the query, you won't need to recalc at all. Access will
take care of everything. Or if you move the calculation to the control
source, Access will also take care of everything:
=some calculation

I'm using a data input form that resembles an Excel spreadsheet that
has
14
columns representing 2 weeks of data, which I later post to normalized
tables. Unbound controls at the end of each row and column calculate
totals,
which I force by a Me.Recalc in the AfterUpdate event of each input
box.

For a given entry, only the given column and row's totals change, but
the
Recalc temporarily blanks all other totals as well, before displaying
the
"new" calculated values. This is inelegant; I would prefer it to work
just
like Excel, where the only the cells with new values change.

Does anyone know how I can achieve this?

Thank you.

Sprinks

 

[Pat Hartman\(MVP\)]

It does update the sums automatically. Access just can't update them until
the record is saved since the sums are based on the recordset. Until the
record is saved, the data is only in temporary storage on the form. It is
not in the recordset. If you want to put a save button on the subform, you
could have the user force Access to save the record when they want it saved.

DoCmd.RunCommand acCmdSaveRecord

Pat,

This is the approach I'm currently using, I just wish it updated the sums
automatically. Thanks for your help.

Sprinks

No query is necessary. You can add a footer to the subform and 14
controls
on the footer.

=Sum(DayHours1)
=Sum(DayHours2)
.....

This method will update the sums when the user leaves the current record
to
go to a new record in the subform or clicks back into the mainform.

Pat,

I could not get Me.SomeField.Recalc to work; it said "Method or data
member
not found." The Help implied that it was a method of the Form object
only.
I am using Access 2003 SP2. However, the query approach you suggested
is
superior anyway, and have gotten part of it to work the way I want, but
would
appreciate help with the rest.

I have a main form based on a dummy table called Timesheet:

Timesheet
----------------
StaffID Integer (Primary Key--Foreign Key to Staff)
PeriodStartDate Date/Time

I have two subforms based on queries of the same table,
TimesheetDetail.
One is for projects we bill to Clients, the other for Administrative
activities.

TimeSheetDetail
--------------------------
TSDetailID AutoNumber (PK)
StaffID Integer (Foreign Key to Timesheet and Staff)
ProjectNumber Text (Foreign Key to Projects--Text because it contains
leading 0's)
DayHours1 Integer
DayHours2 Integer
...(other days in the 2-week period beginning with
Timesheet.PeriodStartDate)
DayHours14 Integer

This non-normalized structure is to make data entry easy. Each PM can
enter
his hours just by moving up and down the form. When the period is
complete,
the user presses a "Post" button, which posts the data to a normalized
TimeRecords table, clears the dummy record (except for the Project
names),
and resets the PeriodStartDate.

TimeRecords
---------------------
TimeRecordID AutoNumber (PK)
StaffID Integer
ProjectNumber Text
WorkDate Date/Time
Hours Integer

The top part of the form looks something this:

Name: Sprinks
Period Starting: 2/26/06

Project 2/26 2/27 2/28 .... 3/12
Total
Zoo Aviary 3 4 5 0
12
Free Library of Phila. 5 2 3 8
18

The total column was added, based on your suggestion as a calculated
field
in the underlying query:
Nz([DayHours1])+Nz([DayHours2])+...Nz([DayHours14]). As the user
enters
or
changes data, the total changes without any code executed or
flickering,
which is great; it feels just like the spreadsheet that preceded it.

I would also like to provide column totals. Based on the above data
they
would be:
8 6 8
8
30

For this I created a Totals query as the basis for a third subform,
linked
by the StaffID:

SELECT
TimeSheetDetail.StaffID,
Sum(TimeSheetDetail.DayHours1) AS SumOfDayHours1,
Sum(TimeSheetDetail.DayHours2) AS SumOfDayHours2,
Sum(TimeSheetDetail.DayHours3) AS SumOfDayHours3,
Sum(TimeSheetDetail.DayHours4) AS SumOfDayHours4,
Sum(TimeSheetDetail.DayHours5) AS SumOfDayHours5,
Sum(TimeSheetDetail.DayHours6) AS SumOfDayHours6,
Sum(TimeSheetDetail.DayHours7) AS SumOfDayHours7,
Sum(TimeSheetDetail.DayHours8) AS SumOfDayHours8,
Sum(TimeSheetDetail.DayHours9) AS SumOfDayHours9,
Sum(TimeSheetDetail.DayHours10) AS SumOfDayHours10,
Sum(TimeSheetDetail.DayHours11) AS SumOfDayHours11,
Sum(TimeSheetDetail.DayHours12) AS SumOfDayHours12,
Sum(TimeSheetDetail.DayHours13) AS SumOfDayHours13,
Sum(TimeSheetDetail.DayHours14) AS SumOfDayHours14,
Sum(Nz([DayHours1])+Nz([DayHours2])+Nz([DayHours3])+Nz([DayHours4])+Nz([DayHours5])+Nz([DayHours6])+Nz([DayHours7])+Nz([DayHours8])+Nz([DayHours9])+Nz([DayHours10])+Nz([DayHours11])+Nz([DayHours12])+Nz([DayHours13])+Nz([DayHours14]))
AS RT
FROM TimeSheetDetail
GROUP BY TimeSheetDetail.StaffID;

The problem is that the totals do not update unless I leave the current
main
form record (which the user will not be able to do because their access
will
be restricted to their own timesheet). The following code also failed:

Me.Parent.Form![DayHoursTotal].Requery

where DayHoursTotal is the name of the subform control on the main
form.

Do you have any suggestions?

Thank you.

Sprinks

:

Me.Recalc says to recalc the entire form. That's why it blanks. You
can
recalc individual fields - Me.SomeField.Recalc. If you move the
calculations to the query, you won't need to recalc at all. Access
will
take care of everything. Or if you move the calculation to the
control
source, Access will also take care of everything:
=some calculation

I'm using a data input form that resembles an Excel spreadsheet that
has
14
columns representing 2 weeks of data, which I later post to
normalized
tables. Unbound controls at the end of each row and column
calculate
totals,
which I force by a Me.Recalc in the AfterUpdate event of each input
box.

For a given entry, only the given column and row's totals change,
but
the
Recalc temporarily blanks all other totals as well, before
displaying
the
"new" calculated values. This is inelegant; I would prefer it to
work
just
like Excel, where the only the cells with new values change.

Does anyone know how I can achieve this?

Thank you.

Sprinks

 

[Sprinks]

Thanks, Pat; your earlier post gave me a clue. I figured from what you said
that I could force a recalculation by saving the current record, which does
work, however, it falls short of the goal of mimicking an Excel spreadsheet,
as the screen "blinks" during the recalculation.

I tried using RecordsetClone to manually calculate the affected total also
(in the control's AfterUpdate event procedure), but it doesn't operate on the
newly entered value, but rather the record as it existed b4 entry.

This situation is frustrating, but seems typical of Access--this is a
non-standard, a-normalized approach. Any well-managed business operates
80/20; devising its "systems" that achieve economies of scale for the product
lines that represent 80% of their core business. This application falls in
the 20%, and seemingly, there is no good approach--the underlying attitude
being, "If you want it to look and behave like a spreadsheet, use Excel."

But...if you have any additional ideas, I'll be grateful.

Sprinks

It does update the sums automatically. Access just can't update them until
the record is saved since the sums are based on the recordset. Until the
record is saved, the data is only in temporary storage on the form. It is
not in the recordset. If you want to put a save button on the subform, you
could have the user force Access to save the record when they want it saved.

DoCmd.RunCommand acCmdSaveRecord

Pat,

This is the approach I'm currently using, I just wish it updated the sums
automatically. Thanks for your help.

Sprinks

No query is necessary. You can add a footer to the subform and 14
controls
on the footer.

=Sum(DayHours1)
=Sum(DayHours2)
.....

This method will update the sums when the user leaves the current record
to
go to a new record in the subform or clicks back into the mainform.

Pat,

I could not get Me.SomeField.Recalc to work; it said "Method or data
member
not found." The Help implied that it was a method of the Form object
only.
I am using Access 2003 SP2. However, the query approach you suggested
is
superior anyway, and have gotten part of it to work the way I want, but
would
appreciate help with the rest.

I have a main form based on a dummy table called Timesheet:

Timesheet
----------------
StaffID Integer (Primary Key--Foreign Key to Staff)
PeriodStartDate Date/Time

I have two subforms based on queries of the same table,
TimesheetDetail.
One is for projects we bill to Clients, the other for Administrative
activities.

TimeSheetDetail
--------------------------
TSDetailID AutoNumber (PK)
StaffID Integer (Foreign Key to Timesheet and Staff)
ProjectNumber Text (Foreign Key to Projects--Text because it contains
leading 0's)
DayHours1 Integer
DayHours2 Integer
...(other days in the 2-week period beginning with
Timesheet.PeriodStartDate)
DayHours14 Integer

This non-normalized structure is to make data entry easy. Each PM can
enter
his hours just by moving up and down the form. When the period is
complete,
the user presses a "Post" button, which posts the data to a normalized
TimeRecords table, clears the dummy record (except for the Project
names),
and resets the PeriodStartDate.

TimeRecords
---------------------
TimeRecordID AutoNumber (PK)
StaffID Integer
ProjectNumber Text
WorkDate Date/Time
Hours Integer

The top part of the form looks something this:

Name: Sprinks
Period Starting: 2/26/06

Project 2/26 2/27 2/28 .... 3/12
Total
Zoo Aviary 3 4 5 0
12
Free Library of Phila. 5 2 3 8
18

The total column was added, based on your suggestion as a calculated
field
in the underlying query:
Nz([DayHours1])+Nz([DayHours2])+...Nz([DayHours14]). As the user
enters
or
changes data, the total changes without any code executed or
flickering,
which is great; it feels just like the spreadsheet that preceded it.

I would also like to provide column totals. Based on the above data
they
would be:
8 6 8
8
30

For this I created a Totals query as the basis for a third subform,
linked
by the StaffID:

SELECT
TimeSheetDetail.StaffID,
Sum(TimeSheetDetail.DayHours1) AS SumOfDayHours1,
Sum(TimeSheetDetail.DayHours2) AS SumOfDayHours2,
Sum(TimeSheetDetail.DayHours3) AS SumOfDayHours3,
Sum(TimeSheetDetail.DayHours4) AS SumOfDayHours4,
Sum(TimeSheetDetail.DayHours5) AS SumOfDayHours5,
Sum(TimeSheetDetail.DayHours6) AS SumOfDayHours6,
Sum(TimeSheetDetail.DayHours7) AS SumOfDayHours7,
Sum(TimeSheetDetail.DayHours8) AS SumOfDayHours8,
Sum(TimeSheetDetail.DayHours9) AS SumOfDayHours9,
Sum(TimeSheetDetail.DayHours10) AS SumOfDayHours10,
Sum(TimeSheetDetail.DayHours11) AS SumOfDayHours11,
Sum(TimeSheetDetail.DayHours12) AS SumOfDayHours12,
Sum(TimeSheetDetail.DayHours13) AS SumOfDayHours13,
Sum(TimeSheetDetail.DayHours14) AS SumOfDayHours14,
Sum(Nz([DayHours1])+Nz([DayHours2])+Nz([DayHours3])+Nz([DayHours4])+Nz([DayHours5])+Nz([DayHours6])+Nz([DayHours7])+Nz([DayHours8])+Nz([DayHours9])+Nz([DayHours10])+Nz([DayHours11])+Nz([DayHours12])+Nz([DayHours13])+Nz([DayHours14]))
AS RT
FROM TimeSheetDetail
GROUP BY TimeSheetDetail.StaffID;

The problem is that the totals do not update unless I leave the current
main
form record (which the user will not be able to do because their access
will
be restricted to their own timesheet). The following code also failed:

Me.Parent.Form![DayHoursTotal].Requery

where DayHoursTotal is the name of the subform control on the main
form.

Do you have any suggestions?

Thank you.

Sprinks

:

Me.Recalc says to recalc the entire form. That's why it blanks. You
can
recalc individual fields - Me.SomeField.Recalc. If you move the
calculations to the query, you won't need to recalc at all. Access
will
take care of everything. Or if you move the calculation to the
control
source, Access will also take care of everything:
=some calculation

I'm using a data input form that resembles an Excel spreadsheet that
has
14
columns representing 2 weeks of data, which I later post to
normalized
tables. Unbound controls at the end of each row and column
calculate
totals,
which I force by a Me.Recalc in the AfterUpdate event of each input
box.

For a given entry, only the given column and row's totals change,
but
the
Recalc temporarily blanks all other totals as well, before
displaying
the
"new" calculated values. This is inelegant; I would prefer it to
work
just
like Excel, where the only the cells with new values change.

Does anyone know how I can achieve this?

Thank you.

Sprinks

 

[Pat Hartman\(MVP\)]

What can I say - if you want it to look and act like a spreadsheet, use
Excel. Relational databases are very different from spreadsheets. One
thing that causes many problems (including yours) is that when working with
a spreadsheet, the data and presentation layers are merged. i.e.. what you
see in the spreadsheet is what is stored in the file. With a database, the
presentation and data layers are completely separate. Data is stored in
tables and is presented in forms and reports. Just because you see
something on the form doesn't mean that it has been saved to the table and
that is what is causing your confusion and unhappiness. The RecordSetClone
for a form is the data from one or more tables, not the data from one or
more tables PLUS what is visible on a form. A record must be saved in order
to be accessible via the RecordSetClone.

Thanks, Pat; your earlier post gave me a clue. I figured from what you
said
that I could force a recalculation by saving the current record, which
does
work, however, it falls short of the goal of mimicking an Excel
spreadsheet,
as the screen "blinks" during the recalculation.

I tried using RecordsetClone to manually calculate the affected total also
(in the control's AfterUpdate event procedure), but it doesn't operate on
the
newly entered value, but rather the record as it existed b4 entry.

This situation is frustrating, but seems typical of Access--this is a
non-standard, a-normalized approach. Any well-managed business operates
80/20; devising its "systems" that achieve economies of scale for the
product
lines that represent 80% of their core business. This application falls
in
the 20%, and seemingly, there is no good approach--the underlying attitude
being, "If you want it to look and behave like a spreadsheet, use Excel."

But...if you have any additional ideas, I'll be grateful.

Sprinks

It does update the sums automatically. Access just can't update them
until
the record is saved since the sums are based on the recordset. Until the
record is saved, the data is only in temporary storage on the form. It
is
not in the recordset. If you want to put a save button on the subform,
you
could have the user force Access to save the record when they want it
saved.

DoCmd.RunCommand acCmdSaveRecord

Pat,

This is the approach I'm currently using, I just wish it updated the
sums
automatically. Thanks for your help.

Sprinks


:

No query is necessary. You can add a footer to the subform and 14
controls
on the footer.

=Sum(DayHours1)
=Sum(DayHours2)
.....

This method will update the sums when the user leaves the current
record
to
go to a new record in the subform or clicks back into the mainform.

Pat,

I could not get Me.SomeField.Recalc to work; it said "Method or data
member
not found." The Help implied that it was a method of the Form
object
only.
I am using Access 2003 SP2. However, the query approach you
suggested
is
superior anyway, and have gotten part of it to work the way I want,
but
would
appreciate help with the rest.

I have a main form based on a dummy table called Timesheet:

Timesheet
----------------
StaffID Integer (Primary Key--Foreign Key to Staff)
PeriodStartDate Date/Time

I have two subforms based on queries of the same table,
TimesheetDetail.
One is for projects we bill to Clients, the other for Administrative
activities.

TimeSheetDetail
--------------------------
TSDetailID AutoNumber (PK)
StaffID Integer (Foreign Key to Timesheet and Staff)
ProjectNumber Text (Foreign Key to Projects--Text because it
contains
leading 0's)
DayHours1 Integer
DayHours2 Integer
...(other days in the 2-week period beginning with
Timesheet.PeriodStartDate)
DayHours14 Integer

This non-normalized structure is to make data entry easy. Each PM
can
enter
his hours just by moving up and down the form. When the period is
complete,
the user presses a "Post" button, which posts the data to a
normalized
TimeRecords table, clears the dummy record (except for the Project
names),
and resets the PeriodStartDate.

TimeRecords
---------------------
TimeRecordID AutoNumber (PK)
StaffID Integer
ProjectNumber Text
WorkDate Date/Time
Hours Integer

The top part of the form looks something this:

Name: Sprinks
Period Starting: 2/26/06

Project 2/26 2/27 2/28 .... 3/12
Total
Zoo Aviary 3 4 5
0
12
Free Library of Phila. 5 2 3 8
18

The total column was added, based on your suggestion as a calculated
field
in the underlying query:
Nz([DayHours1])+Nz([DayHours2])+...Nz([DayHours14]). As the user
enters
or
changes data, the total changes without any code executed or
flickering,
which is great; it feels just like the spreadsheet that preceded it.

I would also like to provide column totals. Based on the above data
they
would be:
8 6 8
8
30

For this I created a Totals query as the basis for a third subform,
linked
by the StaffID:

SELECT
TimeSheetDetail.StaffID,
Sum(TimeSheetDetail.DayHours1) AS SumOfDayHours1,
Sum(TimeSheetDetail.DayHours2) AS SumOfDayHours2,
Sum(TimeSheetDetail.DayHours3) AS SumOfDayHours3,
Sum(TimeSheetDetail.DayHours4) AS SumOfDayHours4,
Sum(TimeSheetDetail.DayHours5) AS SumOfDayHours5,
Sum(TimeSheetDetail.DayHours6) AS SumOfDayHours6,
Sum(TimeSheetDetail.DayHours7) AS SumOfDayHours7,
Sum(TimeSheetDetail.DayHours8) AS SumOfDayHours8,
Sum(TimeSheetDetail.DayHours9) AS SumOfDayHours9,
Sum(TimeSheetDetail.DayHours10) AS SumOfDayHours10,
Sum(TimeSheetDetail.DayHours11) AS SumOfDayHours11,
Sum(TimeSheetDetail.DayHours12) AS SumOfDayHours12,
Sum(TimeSheetDetail.DayHours13) AS SumOfDayHours13,
Sum(TimeSheetDetail.DayHours14) AS SumOfDayHours14,
Sum(Nz([DayHours1])+Nz([DayHours2])+Nz([DayHours3])+Nz([DayHours4])+Nz([DayHours5])+Nz([DayHours6])+Nz([DayHours7])+Nz([DayHours8])+Nz([DayHours9])+Nz([DayHours10])+Nz([DayHours11])+Nz([DayHours12])+Nz([DayHours13])+Nz([DayHours14]))
AS RT
FROM TimeSheetDetail
GROUP BY TimeSheetDetail.StaffID;

The problem is that the totals do not update unless I leave the
current
main
form record (which the user will not be able to do because their
access
will
be restricted to their own timesheet). The following code also
failed:

Me.Parent.Form![DayHoursTotal].Requery

where DayHoursTotal is the name of the subform control on the main
form.

Do you have any suggestions?

Thank you.

Sprinks

:

Me.Recalc says to recalc the entire form. That's why it blanks.
You
can
recalc individual fields - Me.SomeField.Recalc. If you move the
calculations to the query, you won't need to recalc at all. Access
will
take care of everything. Or if you move the calculation to the
control
source, Access will also take care of everything:
=some calculation

I'm using a data input form that resembles an Excel spreadsheet
that
has
14
columns representing 2 weeks of data, which I later post to
normalized
tables. Unbound controls at the end of each row and column
calculate
totals,
which I force by a Me.Recalc in the AfterUpdate event of each
input
box.

For a given entry, only the given column and row's totals change,
but
the
Recalc temporarily blanks all other totals as well, before
displaying
the
"new" calculated values. This is inelegant; I would prefer it to
work
just
like Excel, where the only the cells with new values change.

Does anyone know how I can achieve this?

Thank you.

Sprinks