Update a field which has a running total

[Gordon]

On my form I have a text box for quantity issued (txtQuantityIssued)
and then right after that an unbound text box (txt365Total) which has
a calculated value for the total of the quantity issued in the
previous 12 months.

I want txt365Total to be updated after a value is entered for
txtQuantityIssued. The only way I can manage to populate txt365Total
is if I put the following code in the form's On Current event:

Dim strStartDate As String, strEndDate As String
strStartDate = Me!txtStartDate
strEndDate = Date

Me!txt365Total = DSum("[fldQuantityIssued]", "[tblCompTicketIssues]",
"[fldIssueDate] between # " & strStartDate & "# and #" & strEndDate &
"#")

Note that strStartDate is a hidden text box on the form which gets it
value from =(Date()-365)

Whilst the above code correctly calculates the running total for
quantity issued, it only updates after the record is saved. If I put
the code in the AfterUpdate event of txtQuantityIssued, no value
appears at all.

Help.

Gordon

 

[John Smith]

You need the code in the current event to give you the initial value and also
in the AfterUpdate of the Form (not of the control) to account for the newly
saved record.

HTH
John
##################################
Don't Print - Save trees

 

[Gordon]

You need the code in the current event to give you the initial value and also
in the AfterUpdate of the Form (not of the control) to account for the newly
saved record.

HTH
John
##################################
Don't Print - Save trees

On my form I have a text box for quantity issued (txtQuantityIssued)
and then right after that an unbound text box (txt365Total) which has
a calculated value for the total of the quantity issued in the
previous 12 months.

I want txt365Total to be updated after a value is entered for
txtQuantityIssued. The only way I can manage to populate txt365Total
is if I put the following code in the form's On Current event:

Dim strStartDate As String, strEndDate As String
strStartDate = Me!txtStartDate
strEndDate = Date

Me!txt365Total = DSum("[fldQuantityIssued]", "[tblCompTicketIssues]",
"[fldIssueDate] between # " & strStartDate & "# and #" & strEndDate &
"#")

Note that strStartDate is a hidden text box on the form which gets it
value from =(Date()-365)

Whilst the above code correctly calculates the running total for
quantity issued, it only updates after the record is saved.  If I put
the code in the AfterUpdate event of txtQuantityIssued, no value
appears at all.

Gordon- Hide quoted text -

- Show quoted text -

Thanks for the reply John but unless I've misunderstood that doesn't
immediately update the running total which only gets updated when the
user moves to another record. I want to be able to use that running
total to warn the user if the total reaches a certain value but that
is no good if the user has already moved to another record. Is there
no way the running total can be updated immediately after the update
of txtQuantityIssued?

Gordon

 

[Dennis]

You have to "trigger" the field update somehow. That can only be done using
an event for the control in question. You could technically do it in an On
Exit event I suppose, but you can't do it on an On Change event. That's
because every time you type a character, that event fires. So if you entered
"100" into the control, the update would start with "1", then "10", then
"100". That would mean you'd execute the query/lookup each time you typed a
character.

The only way to avoid that is to use the On Exit event, which would update
the value when you tabbed out of that field. That would allow the update to
occur prior to posting an add/update.

Just a few random thoughts....

You need the code in the current event to give you the initial value and also
in the AfterUpdate of the Form (not of the control) to account for the newly
saved record.

HTH
John
##################################
Don't Print - Save trees

On my form I have a text box for quantity issued (txtQuantityIssued)
and then right after that an unbound text box (txt365Total) which has
a calculated value for the total of the quantity issued in the
previous 12 months.

I want txt365Total to be updated after a value is entered for
txtQuantityIssued. The only way I can manage to populate txt365Total
is if I put the following code in the form's On Current event:

Dim strStartDate As String, strEndDate As String
strStartDate = Me!txtStartDate
strEndDate = Date

Me!txt365Total = DSum("[fldQuantityIssued]", "[tblCompTicketIssues]",
"[fldIssueDate] between # " & strStartDate & "# and #" & strEndDate &
"#")

Note that strStartDate is a hidden text box on the form which gets it
value from =(Date()-365)

Whilst the above code correctly calculates the running total for
quantity issued, it only updates after the record is saved. If I put
the code in the AfterUpdate event of txtQuantityIssued, no value
appears at all.

Gordon- Hide quoted text -

- Show quoted text -

Thanks for the reply John but unless I've misunderstood that doesn't
immediately update the running total which only gets updated when the
user moves to another record. I want to be able to use that running
total to warn the user if the total reaches a certain value but that
is no good if the user has already moved to another record. Is there
no way the running total can be updated immediately after the update
of txtQuantityIssued?

Gordon

 

[John Smith]

You are using DSum to calculate the total. This adds the records in the
database, but the figure that you have just entered will not be in the
database until you have saved the record, this is the point when AfterUpdate
fires. If you want to check that a limit has not been exceeded before the
record has been saved then you would need code in the BeforeUpdate of the
control along the lines of:

If Me!txt365Total + Me!txtQuantityIssued > SomeValue Then
MsgBox "Too much Issued"
Me!txtQuantityIssued.Undo ' If you want to remove the amount entered
Cancel = True
End If

HTH
John
##################################
Don't Print - Save trees

You need the code in the current event to give you the initial value and also
in the AfterUpdate of the Form (not of the control) to account for the newly
saved record.

HTH
John
##################################
Don't Print - Save trees

On my form I have a text box for quantity issued (txtQuantityIssued)
and then right after that an unbound text box (txt365Total) which has
a calculated value for the total of the quantity issued in the
previous 12 months.
I want txt365Total to be updated after a value is entered for
txtQuantityIssued. The only way I can manage to populate txt365Total
is if I put the following code in the form's On Current event:
Dim strStartDate As String, strEndDate As String
strStartDate = Me!txtStartDate
strEndDate = Date
Me!txt365Total = DSum("[fldQuantityIssued]", "[tblCompTicketIssues]",
"[fldIssueDate] between # " & strStartDate & "# and #" & strEndDate &
"#")
Note that strStartDate is a hidden text box on the form which gets it
value from =(Date()-365)
Whilst the above code correctly calculates the running total for
quantity issued, it only updates after the record is saved. If I put
the code in the AfterUpdate event of txtQuantityIssued, no value
appears at all.
Help.
Gordon- Hide quoted text -

- Show quoted text -

Thanks for the reply John but unless I've misunderstood that doesn't
immediately update the running total which only gets updated when the
user moves to another record. I want to be able to use that running
total to warn the user if the total reaches a certain value but that
is no good if the user has already moved to another record. Is there
no way the running total can be updated immediately after the update
of txtQuantityIssued?

Gordon

 

[Gordon]

You are using DSum to calculate the total.  This adds the records in the
database, but the figure that you have just entered will not be in the
database until you have saved the record, this is the point when AfterUpdate
fires.  If you want to check that a limit has not been exceeded before the
record has been saved then you would need code in the BeforeUpdate of the
control along the lines of:

If Me!txt365Total + Me!txtQuantityIssued > SomeValue Then
   MsgBox "Too much Issued"
   Me!txtQuantityIssued.Undo    ' If you want to remove the amount entered
   Cancel = True
End If

HTH
John
##################################
Don't Print - Save trees

You need the code in the current event to give you the initial value and also
in the AfterUpdate of the Form (not of the control) to account for thenewly
saved record.
HTH
John
##################################
Don't Print - Save trees
Gordon wrote:
On my form I have a text box for quantity issued (txtQuantityIssued)
and then right after that an unbound text box (txt365Total) which has
a calculated value for the total of the quantity issued in the
previous 12 months.
I want txt365Total to be updated after a value is entered for
txtQuantityIssued. The only way I can manage to populate txt365Total
is if I put the following code in the form's On Current event:
Dim strStartDate As String, strEndDate As String
strStartDate = Me!txtStartDate
strEndDate = Date
Me!txt365Total = DSum("[fldQuantityIssued]", "[tblCompTicketIssues]",
"[fldIssueDate] between # " & strStartDate & "# and #" & strEndDate &
"#")
Note that strStartDate is a hidden text box on the form which gets it
value from =(Date()-365)
Whilst the above code correctly calculates the running total for
quantity issued, it only updates after the record is saved.  If I put
the code in the AfterUpdate event of txtQuantityIssued, no value
appears at all.
Help.
Gordon- Hide quoted text -
- Show quoted text -

Thanks for the reply John but unless I've misunderstood that doesn't
immediately update the running total which only gets updated when the
user moves to another record.  I want to be able to use that running
total to warn the user if the total reaches a certain value but that
is no good if the user has already moved to another record. Is there
no way the running total can be updated immediately after the update
of txtQuantityIssued?

Gordon- Hide quoted text -

- Show quoted text -

Hi John,

Sorry for the delay in coming back on this. Your solution works a
treat - just what I wanted.

Many thanks

Gordon

 

[John Smith]

Not a problem, glad it helped!

John
##################################
Don't Print - Save trees

You are using DSum to calculate the total. This adds the records in the
database, but the figure that you have just entered will not be in the
database until you have saved the record, this is the point when AfterUpdate
fires. If you want to check that a limit has not been exceeded before the
record has been saved then you would need code in the BeforeUpdate of the
control along the lines of:

If Me!txt365Total + Me!txtQuantityIssued > SomeValue Then
MsgBox "Too much Issued"
Me!txtQuantityIssued.Undo ' If you want to remove the amount entered
Cancel = True
End If

HTH
John
##################################
Don't Print - Save trees

On 30 Apr, 15:11, John Smith
<johnDOTsmithATbromleyhospitalsDOTnhsDOTuk> wrote:
You need the code in the current event to give you the initial value and also
in the AfterUpdate of the Form (not of the control) to account for the newly
saved record.
HTH
John
##################################
Don't Print - Save trees
Gordon wrote:
On my form I have a text box for quantity issued (txtQuantityIssued)
and then right after that an unbound text box (txt365Total) which has
a calculated value for the total of the quantity issued in the
previous 12 months.
I want txt365Total to be updated after a value is entered for
txtQuantityIssued. The only way I can manage to populate txt365Total
is if I put the following code in the form's On Current event:
Dim strStartDate As String, strEndDate As String
strStartDate = Me!txtStartDate
strEndDate = Date
Me!txt365Total = DSum("[fldQuantityIssued]", "[tblCompTicketIssues]",
"[fldIssueDate] between # " & strStartDate & "# and #" & strEndDate &
"#")
Note that strStartDate is a hidden text box on the form which gets it
value from =(Date()-365)
Whilst the above code correctly calculates the running total for
quantity issued, it only updates after the record is saved. If I put
the code in the AfterUpdate event of txtQuantityIssued, no value
appears at all.
Help.
Gordon- Hide quoted text -
- Show quoted text -
Thanks for the reply John but unless I've misunderstood that doesn't
immediately update the running total which only gets updated when the
user moves to another record. I want to be able to use that running
total to warn the user if the total reaches a certain value but that
is no good if the user has already moved to another record. Is there
no way the running total can be updated immediately after the update
of txtQuantityIssued?
Gordon- Hide quoted text -

- Show quoted text -

Hi John,

Sorry for the delay in coming back on this. Your solution works a
treat - just what I wanted.

Many thanks

Gordon