Only re-calculate in VBA, if date has changed

[Duncan Edment]

A form in my database shows a date, a start time and an end time.

In the OnCurrent event, I have VBA code to calculate several values based on the
recorded times in the database. However, if I move to the next record--which
might hold the same date as the previous record--it re-calculates the values.
What I want it to do, is only re-calculate if it has to, i.e. if the date has
changed.

For example, an entry may be:

12/08/04 09:00 13:50

When the form loads, several calculated fields display time values based upon
hours for the current day & the current week. If I move to the next record, it
may display the following:

12/08/04 13:50 17:00

Obviously, I don't want to re-calculate the time values, as nothing has changed.
Moving to the next record may display:

11/08/04 09:00 17:00

At this point, I would want it to re-calculate the value for the daily figure,
as we have moved to a different day. Moving to the next record again, may
display:

05/08/04 09:00 12:10

Here, I would want it to not only re-calculate the daily figure, but also the
weekly figure, as we have moved to a new day and a new week.

Is this possible, and if so, how?

Many thanks for your help & regards

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.

 

[Adrian Tofan]

An idea it would be to use 3 static variables in On Current event and save
DayDate, StartTime, EndTime.
When you move to a different record - compare the previous DayDate, and
both times with the new values - and based on your business rules, perform
which calculations are needed - and at the end , refresh those 3 variables.

 

[Wayne Morgan]

You could use Static variables in the Current event to "remember" the last
values. Compare that to the current values and skip the calculations if they
match (If...Then statement). I don't know if this will save you much time
though.

 

[Duncan Edment]

OK guys, I've created my static value for the date the entry was recorded and
modified my code, so that if the static date value and the displayed date value
on screen do not match, my code will go ahead and re-calculate my three
calculated controls.

Everything works fine, until I move from one record to another, where the date
is the same. I get the text "Calculating..." on my status bar and the three
calculated controls go blank until their value is re-calculated! However,
looking at my code--and tracing it--these values should not be getting
calculated.

What gives?

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.

 

[Wayne Morgan]

What is in the Control Source of the controls? Do you have an equation in
them? If so, you are doing the work twice, once in the Control Source and
once in VBA.

 

[Duncan Edment]

Wayne,

Yes, the controls do have a ControlSource in them. For example, one of them has
the control source:

=diff2dates("hn",7.4/24,[txtHoursWorkedDaily]-([txtHoursOTDaily]*2),True)

This shows them how many hours they are up / down in a 7.4 hour working day.
However, the values of txtHoursWorkedDaily & txtHoursOTDaily are calculated in
VB and the results placed in unbound controls on the form. Using the Static
variable as you suggested, I only calculate these VB values if the date changes.
So, if my first record shows today's date, 14/08/04, it calculates the values in
VB and places the results in several unbound controls. If I move to the next
record, which also displays today's date, the values in the unbound controls are
not recalculated. However, the control with the ControlSource mentioned above
appears to recalculate. Why? Nothing has changed.

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.

 

[Wayne Morgan]

If you put an equation in the control source, it will recalculate each time
you move to a different record. In fact, it will usually recalculate if you
change the value in another contol refered to in the equation, even without
moving to a different record. The only way around this would be to have the
VBA in the form's Current event and have it assign a value to the textbox.
If you had a long, complicated calculation in the VBA, it may be quicker to
preempt it by using the static variables. But, as stated in my first
message, I was not sure that it would save you a lot of time. If the
equation is short, as this one is, it would take about as long to do the
checking of the static variables as it would to just redo the calculation.

 

[Duncan Edment]

Thanks for your help Wayne. Looks like I'll just have to put up with
re-calculation.

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.

 

[Duncan Edment]

Wayne,

If I remove the ControlSource from the three calculated
controls and directly calculate the values in VB and set
the fields to the values, I still get the "Calculating..."
legend on the status bar. The "Calculating..." text
appears there long after the values have been calculated
and placed in the controls. There are no other calculated
controls on the form.

Why is this, please?

Duncan

 

[Wayne Morgan]

I don't get that. Do you have calculations in the query that feeds the
report? If so, it is probably coming from there.

 

[Duncan Edment]

Wayne,

The RecordSource for the form is based on a query, but there are no calculations
in it.

All I have now, are six controls which display information from the
RecordSource, and three calculated controls. These calculated controls are all
unbound, and have their values calculated and assigned to the controls, directly
in VB. However, when a record is displayed, I still get the "Calculating..."
text in the status bar. This also appears when I move to another record, even
although there are no actual calculations on the form.

Any ideas?

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.

 

[Wayne Morgan]

The only idea I have is to try another, new form as a test and see if you
still get the same thing. As I said, I tested here and didn't get that. I
even tried forcing the VBA to take longer in case it was going too quickly,
but I still didn't see that.

 

[Wayne Morgan]

I though of another possibility. Do you have any filters or sorting on the
form that may be doing some calculating?

 

[Duncan Edment]

Wayne,

Nope, no filter or sorting on the form. However, the form has a RecordSource of
a query, which does include the command to sort the records in descending date
order and ascending time order. Could this be causing it?

Duncan

--
Newsgroups are like one big sandbox that all of us
UseNet kiddies play in with peace & harmony.

Spammers, Cross-Posters, and Lamers are the
people that pee in our big sandbox.

 

[Wayne Morgan]

I wouldn't expect a sort to cause it. Do you get it if you just open the
query or only when the form is opened? I'm wondering if Access thinks there
is still something there, even though you've removed it, and it is running
the calculation anyway.