DSum Executing Too Late

[Rob]

I have had a problem for nearly a week now, and I am unable to decipher
it myself. The problem I am running in to, is that the DSum that my
button calls for, is not calculated until the current record (on a
continuous form) is defocused.

What I need is a way to force the DSum to update before the data is
sent to the table. I'm unsure of why it's not doing it currently, but
as it stands, the DSum will only visibly update on the form if I
defocus the current record.

Here is the code that I am using, any help would be greatly
appreciated.

------
Private Sub Command12_Click()
Dim rsCurr As DAO.Recordset
Dim strSQL As String
Dim nHours As Integer
Dim nMinutes As Integer
Dim nTotal As Date
Dim nXfer As Integer

Returned.Value = Time
nTotal = CheckedOut.Value - Returned.Value
nHours = DatePart("h", nTotal)
nMinutes = DatePart("n", nTotal)
nXfer = (nHours * 60) + nMinutes

TotalTime.Value = nXfer

TotalMinBox.Value = DSum("TotalTime", "LearnerActivity", "[IDNumber]="
& IDNumber)

strSQL = "SELECT TotalMinutes " & "FROM LearnerInformation " & "WHERE
IDNumber = " & Me.IDNumber
Set rsCurr = CurrentDb().OpenRecordset(strSQL)
If Not rsCurr.EOF Then
rsCurr.Edit
rsCurr![TotalMinutes] = TotalMinBox.Value
rsCurr.Update
End If

End Sub
--------

P.S., I did have a previous topic going where I posted this issue,
however the title was the name of the problem I had prior to this. I
hope reposting this as a more relevant term will not be troublesome.

Thanks again.

 

[Douglas J. Steele]

Why are you trying to store a calculated value? That's seldom a good thing
to do.

 

[Rob]

I felt the need to store a calculated value because I felt like having
a calculated total of the hours spent logged in would be convenient for
future reports and/or just basic data browsing. I could have the total
just recalculated every time, but I have a fear that there will be a
time where I will need to have this data stored under the user's
profile, and I just wanted to cover my bases.

Why are you trying to store a calculated value? That's seldom a good thing
to do.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have had a problem for nearly a week now, and I am unable to decipher
it myself. The problem I am running in to, is that the DSum that my
button calls for, is not calculated until the current record (on a
continuous form) is defocused.

What I need is a way to force the DSum to update before the data is
sent to the table. I'm unsure of why it's not doing it currently, but
as it stands, the DSum will only visibly update on the form if I
defocus the current record.

Here is the code that I am using, any help would be greatly
appreciated.

------
Private Sub Command12_Click()
Dim rsCurr As DAO.Recordset
Dim strSQL As String
Dim nHours As Integer
Dim nMinutes As Integer
Dim nTotal As Date
Dim nXfer As Integer

Returned.Value = Time
nTotal = CheckedOut.Value - Returned.Value
nHours = DatePart("h", nTotal)
nMinutes = DatePart("n", nTotal)
nXfer = (nHours * 60) + nMinutes

TotalTime.Value = nXfer

TotalMinBox.Value = DSum("TotalTime", "LearnerActivity", "[IDNumber]="
& IDNumber)

strSQL = "SELECT TotalMinutes " & "FROM LearnerInformation " & "WHERE
IDNumber = " & Me.IDNumber
Set rsCurr = CurrentDb().OpenRecordset(strSQL)
If Not rsCurr.EOF Then
rsCurr.Edit
rsCurr![TotalMinutes] = TotalMinBox.Value
rsCurr.Update
End If

End Sub
--------

P.S., I did have a previous topic going where I posted this issue,
however the title was the name of the problem I had prior to this. I
hope reposting this as a more relevant term will not be troublesome.

Thanks again.

 

[Douglas J. Steele]

As fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed, making
the value WRONG."

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I felt the need to store a calculated value because I felt like having
a calculated total of the hours spent logged in would be convenient for
future reports and/or just basic data browsing. I could have the total
just recalculated every time, but I have a fear that there will be a
time where I will need to have this data stored under the user's
profile, and I just wanted to cover my bases.

Why are you trying to store a calculated value? That's seldom a good
thing
to do.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have had a problem for nearly a week now, and I am unable to decipher
it myself. The problem I am running in to, is that the DSum that my
button calls for, is not calculated until the current record (on a
continuous form) is defocused.

What I need is a way to force the DSum to update before the data is
sent to the table. I'm unsure of why it's not doing it currently, but
as it stands, the DSum will only visibly update on the form if I
defocus the current record.

Here is the code that I am using, any help would be greatly
appreciated.

------
Private Sub Command12_Click()
Dim rsCurr As DAO.Recordset
Dim strSQL As String
Dim nHours As Integer
Dim nMinutes As Integer
Dim nTotal As Date
Dim nXfer As Integer

Returned.Value = Time
nTotal = CheckedOut.Value - Returned.Value
nHours = DatePart("h", nTotal)
nMinutes = DatePart("n", nTotal)
nXfer = (nHours * 60) + nMinutes

TotalTime.Value = nXfer

TotalMinBox.Value = DSum("TotalTime", "LearnerActivity", "[IDNumber]="
& IDNumber)

strSQL = "SELECT TotalMinutes " & "FROM LearnerInformation " & "WHERE
IDNumber = " & Me.IDNumber
Set rsCurr = CurrentDb().OpenRecordset(strSQL)
If Not rsCurr.EOF Then
rsCurr.Edit
rsCurr![TotalMinutes] = TotalMinBox.Value
rsCurr.Update
End If

End Sub
--------

P.S., I did have a previous topic going where I posted this issue,
however the title was the name of the problem I had prior to this. I
hope reposting this as a more relevant term will not be troublesome.

Thanks again.

 

[Rob]

I see your points, and I will most likely be tweaking the operation of
this database before submitting it. However, the problem still remains
of the DSum not updating when the button is pressed. (Only when focus
is lost)

The people who will be using this application could very well be the
type of people who will become impatient and/or click-happy when they
hit submit, and nothing happens to the calculated total of minutes.

My biggest goal is to make this as simple as I can, since it is
replacing the office's existing software. Not all of the employees who
will be using this will understand that actions -are- in fact taking
place when the button is pressed, just not visible to them.

Thank you again for all of your assistance with my issues.

As fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed, making
the value WRONG."

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I felt the need to store a calculated value because I felt like having
a calculated total of the hours spent logged in would be convenient for
future reports and/or just basic data browsing. I could have the total
just recalculated every time, but I have a fear that there will be a
time where I will need to have this data stored under the user's
profile, and I just wanted to cover my bases.

Why are you trying to store a calculated value? That's seldom a good
thing
to do.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have had a problem for nearly a week now, and I am unable to decipher
it myself. The problem I am running in to, is that the DSum that my
button calls for, is not calculated until the current record (on a
continuous form) is defocused.

What I need is a way to force the DSum to update before the data is
sent to the table. I'm unsure of why it's not doing it currently, but
as it stands, the DSum will only visibly update on the form if I
defocus the current record.

Here is the code that I am using, any help would be greatly
appreciated.

------
Private Sub Command12_Click()
Dim rsCurr As DAO.Recordset
Dim strSQL As String
Dim nHours As Integer
Dim nMinutes As Integer
Dim nTotal As Date
Dim nXfer As Integer

Returned.Value = Time
nTotal = CheckedOut.Value - Returned.Value
nHours = DatePart("h", nTotal)
nMinutes = DatePart("n", nTotal)
nXfer = (nHours * 60) + nMinutes

TotalTime.Value = nXfer

TotalMinBox.Value = DSum("TotalTime", "LearnerActivity", "[IDNumber]="
& IDNumber)

strSQL = "SELECT TotalMinutes " & "FROM LearnerInformation " & "WHERE
IDNumber = " & Me.IDNumber
Set rsCurr = CurrentDb().OpenRecordset(strSQL)
If Not rsCurr.EOF Then
rsCurr.Edit
rsCurr![TotalMinutes] = TotalMinBox.Value
rsCurr.Update
End If

End Sub
--------

P.S., I did have a previous topic going where I posted this issue,
however the title was the name of the problem I had prior to this. I
hope reposting this as a more relevant term will not be troublesome.

Thanks again.

 

[Douglas J. Steele]

Since it's a bound text box, its value doesn't change until you leave the
control. Check the Help file for the difference between the Text and Value
properties of text boxes:

"While the control has the focus, the Text property contains the text data
currently in the control; the Value property contains the last saved data
for the control. When you move the focus to another control, the control's
data is updated, and the Value property is set to this new value. The Text
property setting is then unavailable until the control gets the focus again.
If you use the Save Record command on the Records menu to save the data in
the control without moving the focus, the Text property and Value property
settings will be the same."

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I see your points, and I will most likely be tweaking the operation of
this database before submitting it. However, the problem still remains
of the DSum not updating when the button is pressed. (Only when focus
is lost)

The people who will be using this application could very well be the
type of people who will become impatient and/or click-happy when they
hit submit, and nothing happens to the calculated total of minutes.

My biggest goal is to make this as simple as I can, since it is
replacing the office's existing software. Not all of the employees who
will be using this will understand that actions -are- in fact taking
place when the button is pressed, just not visible to them.

Thank you again for all of your assistance with my issues.

As fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable
calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed,
making
the value WRONG."

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I felt the need to store a calculated value because I felt like having
a calculated total of the hours spent logged in would be convenient for
future reports and/or just basic data browsing. I could have the total
just recalculated every time, but I have a fear that there will be a
time where I will need to have this data stored under the user's
profile, and I just wanted to cover my bases.



Douglas J. Steele wrote:
Why are you trying to store a calculated value? That's seldom a good
thing
to do.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have had a problem for nearly a week now, and I am unable to
decipher
it myself. The problem I am running in to, is that the DSum that my
button calls for, is not calculated until the current record (on a
continuous form) is defocused.

What I need is a way to force the DSum to update before the data is
sent to the table. I'm unsure of why it's not doing it currently,
but
as it stands, the DSum will only visibly update on the form if I
defocus the current record.

Here is the code that I am using, any help would be greatly
appreciated.

------
Private Sub Command12_Click()
Dim rsCurr As DAO.Recordset
Dim strSQL As String
Dim nHours As Integer
Dim nMinutes As Integer
Dim nTotal As Date
Dim nXfer As Integer

Returned.Value = Time
nTotal = CheckedOut.Value - Returned.Value
nHours = DatePart("h", nTotal)
nMinutes = DatePart("n", nTotal)
nXfer = (nHours * 60) + nMinutes

TotalTime.Value = nXfer

TotalMinBox.Value = DSum("TotalTime", "LearnerActivity",
"[IDNumber]="
& IDNumber)

strSQL = "SELECT TotalMinutes " & "FROM LearnerInformation " &
"WHERE
IDNumber = " & Me.IDNumber
Set rsCurr = CurrentDb().OpenRecordset(strSQL)
If Not rsCurr.EOF Then
rsCurr.Edit
rsCurr![TotalMinutes] = TotalMinBox.Value
rsCurr.Update
End If

End Sub
--------

P.S., I did have a previous topic going where I posted this issue,
however the title was the name of the problem I had prior to this. I
hope reposting this as a more relevant term will not be troublesome.

Thanks again.