VB calculation on form

  • Thread starter Thread starter Annie
  • Start date Start date
A

Annie

Hi. I'm just not getting this. I have VB code on a
subform BucksMember to calculate curTotal (not a field,
just a calculation). Individuals is the main form. I
want curTotal calculation from the subform to appear as
the value for a field on Individuals- this field is
RunningTotal. I think there are 2 or 3 steps in need to
go through:

1) in the subform (BucksMember) code add:
forms!Individuals.RunningTotal = curTotal

2) on the mainform (Individuals) I have the unbound field
RunningTotal. Do I leave it unbound, or will it have a
control source? If I leave it blank, no value appears in
this field so it isn't getting the information it needs
from BucksMember VB curTotal calculation.

3) in the mainform (Individuals) code do I need to do
anything?

I can't seem to get the calculation based on the subform
to appear as a field on my mainform. I don't think I
have a syntax issue, but I do think I may not have all of
the steps right. Details are much appreciated. Thanks,
Annie
 
Interesting!
I just stuck it in my existing subprocess. Obviously, I'm
a terrible at code. How do I make the code run? Do I
need another subprocess of some sort on my subform? Thank
you for the help. Annie
 
What is your "existing subprocess"?
The code at the top of the procedure probably starts with
Private Sub ...
although Private may be Public, and Sub may be Function.
Can you post that entire line?
 
I think I understand what you're saying. I stuck the code
in
Private Sub BucksNumber_BeforeUpdate(Cancel As Integer)

This subprocess calculates curTotal before the current
BucksNumber entry (since BucksNumber can be negative and I
don't want current BucksNumber to be absolute value
greater than curTotal).

So, I now tried to stick
forms!Individuals.RunningTotal = curTotal
Private Sub Form_Open(Cancel As Integer)
figuring that on open, I'd like to see the total, but of
course this sub doesn't know curTotal. So I left it back
in BucksNubmer_BeforeUpdate where it isn't working. How
do I put forms!Individuals.RunningTotal = curTotal in a
subprocess that will not only recognize curTotal but will
do something? Thanks, Annie
Here's the code (inelegant as it may be):
Private Sub BucksNumber_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim curTotal As Currency
Dim strMsg As String 'MsgBox message.
Dim bWarn As Boolean 'Flag to warn user.
Dim curBucksNumber As Currency
Dim curITABucks As Currency
Dim curAbsBucksNumber As Currency

strWhere = "[ContactID] = " & Nz([ContactID], 0)

curBucksNumber = Nz(DSum("BucksNumber", "BucksMember",
strWhere), 0)
curITABucks = Nz(DLookup("SumOfITABucks", "ITABucks",
strWhere), 0)
curTotal = curBucksNumber + curITABucks
curAbsBucksNumber = Abs(Me!BucksNumber)

[Forms]![Individuals]![RunningTotal] = curTotal

If ((curTotal) < (curAbsBucksNumber)) Or ((Me!
BucksNumber) < -30) Then
Cancel = True
strMsg = strMsg & "Member does not have enough
Bucks to 'cash in' that many." & vbCrLf
End If
Call CancelOrWarn(Cancel, bWarn, strMsg)
End Sub
 
You're right - Form_Open isn't a good place to do this.
BucksNumber_BeforeUpdate will only run if you've made a change to
BucksNumber -
is that what you want?

Annie said:
I think I understand what you're saying. I stuck the code
in
Private Sub BucksNumber_BeforeUpdate(Cancel As Integer)

This subprocess calculates curTotal before the current
BucksNumber entry (since BucksNumber can be negative and I
don't want current BucksNumber to be absolute value
greater than curTotal).

So, I now tried to stick
forms!Individuals.RunningTotal = curTotal
Private Sub Form_Open(Cancel As Integer)
figuring that on open, I'd like to see the total, but of
course this sub doesn't know curTotal. So I left it back
in BucksNubmer_BeforeUpdate where it isn't working. How
do I put forms!Individuals.RunningTotal = curTotal in a
subprocess that will not only recognize curTotal but will
do something? Thanks, Annie
Here's the code (inelegant as it may be):
Private Sub BucksNumber_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim curTotal As Currency
Dim strMsg As String 'MsgBox message.
Dim bWarn As Boolean 'Flag to warn user.
Dim curBucksNumber As Currency
Dim curITABucks As Currency
Dim curAbsBucksNumber As Currency

strWhere = "[ContactID] = " & Nz([ContactID], 0)

curBucksNumber = Nz(DSum("BucksNumber", "BucksMember",
strWhere), 0)
curITABucks = Nz(DLookup("SumOfITABucks", "ITABucks",
strWhere), 0)
curTotal = curBucksNumber + curITABucks
curAbsBucksNumber = Abs(Me!BucksNumber)

[Forms]![Individuals]![RunningTotal] = curTotal

If ((curTotal) < (curAbsBucksNumber)) Or ((Me!
BucksNumber) < -30) Then
Cancel = True
strMsg = strMsg & "Member does not have enough
Bucks to 'cash in' that many." & vbCrLf
End If
Call CancelOrWarn(Cancel, bWarn, strMsg)
End Sub

-----Original Message-----
What is your "existing subprocess"?
The code at the top of the procedure probably starts with
Private Sub ...
although Private may be Public, and Sub may be Function.
Can you post that entire line?




.
Click to expand...
Click to expand...
 
-----Original Message-----
You're right - Form_Open isn't a good place to do this.
BucksNumber_BeforeUpdate will only run if you've made a change to
BucksNumber -
is that what you want?

Annie said:
I think I understand what you're saying. I stuck the code
in
Private Sub BucksNumber_BeforeUpdate(Cancel As Integer)

This subprocess calculates curTotal before the current
BucksNumber entry (since BucksNumber can be negative and I
don't want current BucksNumber to be absolute value
greater than curTotal).

So, I now tried to stick
forms!Individuals.RunningTotal = curTotal
Private Sub Form_Open(Cancel As Integer)
figuring that on open, I'd like to see the total, but of
course this sub doesn't know curTotal. So I left it back
in BucksNubmer_BeforeUpdate where it isn't working. How
do I put forms!Individuals.RunningTotal = curTotal in a
subprocess that will not only recognize curTotal but will
do something? Thanks, Annie
Here's the code (inelegant as it may be):
Private Sub BucksNumber_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim curTotal As Currency
Dim strMsg As String 'MsgBox message.
Dim bWarn As Boolean 'Flag to warn user.
Dim curBucksNumber As Currency
Dim curITABucks As Currency
Dim curAbsBucksNumber As Currency

strWhere = "[ContactID] = " & Nz([ContactID], 0)

curBucksNumber = Nz(DSum ("BucksNumber", "BucksMember",
strWhere), 0)
curITABucks = Nz(DLookup ("SumOfITABucks", "ITABucks",
strWhere), 0)
curTotal = curBucksNumber + curITABucks
curAbsBucksNumber = Abs(Me!BucksNumber)

[Forms]![Individuals]![RunningTotal] = curTotal

If ((curTotal) < (curAbsBucksNumber)) Or ((Me!
BucksNumber) < -30) Then
Cancel = True
strMsg = strMsg & "Member does not have enough
Bucks to 'cash in' that many." & vbCrLf
End If
Call CancelOrWarn(Cancel, bWarn, strMsg)
End Sub

-----Original Message-----
What is your "existing subprocess"?
The code at the top of the procedure probably starts with
Private Sub ...
although Private may be Public, and Sub may be Function.
Can you post that entire line?

Interesting!
I just stuck it in my existing subprocess.
Click to expand...
Obviously,
I'm
a terrible at code. How do I make the code run? Do I
need another subprocess of some sort on my subform? Thank
you for the help. Annie

-----Original Message-----
You haven't said what causes the code in Step 1 to run.

Hi. I'm just not getting this. I have VB code on a
subform BucksMember to calculate curTotal (not a field,
just a calculation). Individuals is the main
Click to expand...
form.
I
want curTotal calculation from the subform to
Click to expand...
appear
as
the value for a field on Individuals- this field is
RunningTotal. I think there are 2 or 3 steps in need to
go through:

1) in the subform (BucksMember) code add:
forms!Individuals.RunningTotal = curTotal

2) on the mainform (Individuals) I have the unbound
field
RunningTotal. Do I leave it unbound, or will it have a
control source? If I leave it blank, no value appears
in
this field so it isn't getting the information it needs
from BucksMember VB curTotal calculation.

3) in the mainform (Individuals) code do I need to do
anything?

I can't seem to get the calculation based on the subform
to appear as a field on my mainform. I don't think I
have a syntax issue, but I do think I may not have all
of
the steps right. Details are much appreciated. Thanks,
Annie


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
When my form/subform opens, I want to see curTotal (VB
calc) in RunningTotal (mainform field). RunningTotal will
be the curTotal before any entries happen in this session.

That way, the user will know whether or not there are
enough "bucks" to engage in this session's entry (not
really that important if this session's entry is not
negative- as in are there enough "bucks earned to
justify "cashing-in" the amount in BucksNumber?- and I do
have code to give a message that when shouldn't "cash-in"
that many but thought it would be nice to show the running
total).
On save of this session, it would be nice if RunningTotal
would reflect curTotal plus this session's BuckNumber so
that the user doesn't have to manually refresh the field
and will be able to see up-to-the minute RunningTotal.

Thanks for hanging in there through my explanations! Can
this be done? Annie
-----Original Message-----
You're right - Form_Open isn't a good place to do this.
BucksNumber_BeforeUpdate will only run if you've made a change to
BucksNumber -
is that what you want?

Annie said:
I think I understand what you're saying. I stuck the code
in
Private Sub BucksNumber_BeforeUpdate(Cancel As Integer)

This subprocess calculates curTotal before the current
BucksNumber entry (since BucksNumber can be negative and I
don't want current BucksNumber to be absolute value
greater than curTotal).

So, I now tried to stick
forms!Individuals.RunningTotal = curTotal
Private Sub Form_Open(Cancel As Integer)
figuring that on open, I'd like to see the total, but of
course this sub doesn't know curTotal. So I left it back
in BucksNubmer_BeforeUpdate where it isn't working. How
do I put forms!Individuals.RunningTotal = curTotal in a
subprocess that will not only recognize curTotal but will
do something? Thanks, Annie
Here's the code (inelegant as it may be):
Private Sub BucksNumber_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim curTotal As Currency
Dim strMsg As String 'MsgBox message.
Dim bWarn As Boolean 'Flag to warn user.
Dim curBucksNumber As Currency
Dim curITABucks As Currency
Dim curAbsBucksNumber As Currency

strWhere = "[ContactID] = " & Nz([ContactID], 0)

curBucksNumber = Nz(DSum ("BucksNumber", "BucksMember",
strWhere), 0)
curITABucks = Nz(DLookup ("SumOfITABucks", "ITABucks",
strWhere), 0)
curTotal = curBucksNumber + curITABucks
curAbsBucksNumber = Abs(Me!BucksNumber)

[Forms]![Individuals]![RunningTotal] = curTotal

If ((curTotal) < (curAbsBucksNumber)) Or ((Me!
BucksNumber) < -30) Then
Cancel = True
strMsg = strMsg & "Member does not have enough
Bucks to 'cash in' that many." & vbCrLf
End If
Call CancelOrWarn(Cancel, bWarn, strMsg)
End Sub

-----Original Message-----
What is your "existing subprocess"?
The code at the top of the procedure probably starts with
Private Sub ...
although Private may be Public, and Sub may be Function.
Can you post that entire line?

Interesting!
I just stuck it in my existing subprocess.
Click to expand...
Obviously,
I'm
a terrible at code. How do I make the code run? Do I
need another subprocess of some sort on my subform? Thank
you for the help. Annie

-----Original Message-----
You haven't said what causes the code in Step 1 to run.

Hi. I'm just not getting this. I have VB code on a
subform BucksMember to calculate curTotal (not a field,
just a calculation). Individuals is the main
Click to expand...
form.
I
want curTotal calculation from the subform to
Click to expand...
appear
as
the value for a field on Individuals- this field is
RunningTotal. I think there are 2 or 3 steps in need to
go through:

1) in the subform (BucksMember) code add:
forms!Individuals.RunningTotal = curTotal

2) on the mainform (Individuals) I have the unbound
field
RunningTotal. Do I leave it unbound, or will it have a
control source? If I leave it blank, no value appears
in
this field so it isn't getting the information it needs
from BucksMember VB curTotal calculation.

3) in the mainform (Individuals) code do I need to do
anything?

I can't seem to get the calculation based on the subform
to appear as a field on my mainform. I don't think I
have a syntax issue, but I do think I may not have all
of
the steps right. Details are much appreciated. Thanks,
Annie


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Annie said:
When my form/subform opens, I want to see curTotal (VB
calc) in RunningTotal (mainform field). RunningTotal will
be the curTotal before any entries happen in this session.

That way, the user will know whether or not there are
enough "bucks" to engage in this session's entry (not
really that important if this session's entry is not
negative- as in are there enough "bucks earned to
justify "cashing-in" the amount in BucksNumber?- and I do
have code to give a message that when shouldn't "cash-in"
that many but thought it would be nice to show the running
total).
On save of this session, it would be nice if RunningTotal
would reflect curTotal plus this session's BuckNumber so
that the user doesn't have to manually refresh the field
and will be able to see up-to-the minute RunningTotal.

Thanks for hanging in there through my explanations! Can
this be done? Annie
-----Original Message-----
You're right - Form_Open isn't a good place to do this.
BucksNumber_BeforeUpdate will only run if you've made a change to
BucksNumber -
is that what you want?

Annie said:
I think I understand what you're saying. I stuck the code
in
Private Sub BucksNumber_BeforeUpdate(Cancel As Integer)

This subprocess calculates curTotal before the current
BucksNumber entry (since BucksNumber can be negative and I
don't want current BucksNumber to be absolute value
greater than curTotal).

So, I now tried to stick
forms!Individuals.RunningTotal = curTotal
Private Sub Form_Open(Cancel As Integer)
figuring that on open, I'd like to see the total, but of
course this sub doesn't know curTotal. So I left it back
in BucksNubmer_BeforeUpdate where it isn't working. How
do I put forms!Individuals.RunningTotal = curTotal in a
subprocess that will not only recognize curTotal but will
do something? Thanks, Annie
Here's the code (inelegant as it may be):
Private Sub BucksNumber_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim curTotal As Currency
Dim strMsg As String 'MsgBox message.
Dim bWarn As Boolean 'Flag to warn user.
Dim curBucksNumber As Currency
Dim curITABucks As Currency
Dim curAbsBucksNumber As Currency

strWhere = "[ContactID] = " & Nz([ContactID], 0)

curBucksNumber = Nz(DSum ("BucksNumber", "BucksMember",
strWhere), 0)
curITABucks = Nz(DLookup ("SumOfITABucks", "ITABucks",
strWhere), 0)
curTotal = curBucksNumber + curITABucks
curAbsBucksNumber = Abs(Me!BucksNumber)

[Forms]![Individuals]![RunningTotal] = curTotal

If ((curTotal) < (curAbsBucksNumber)) Or ((Me!
BucksNumber) < -30) Then
Cancel = True
strMsg = strMsg & "Member does not have enough
Bucks to 'cash in' that many." & vbCrLf
End If
Call CancelOrWarn(Cancel, bWarn, strMsg)
End Sub


-----Original Message-----
What is your "existing subprocess"?
The code at the top of the procedure probably starts with
Private Sub ...
although Private may be Public, and Sub may be Function.
Can you post that entire line?

Interesting!
I just stuck it in my existing subprocess. Obviously,
I'm
a terrible at code. How do I make the code run? Do I
need another subprocess of some sort on my subform?
Thank
you for the help. Annie

-----Original Message-----
You haven't said what causes the code in Step 1 to run.

message
Hi. I'm just not getting this. I have VB code on a
subform BucksMember to calculate curTotal (not a
field,
just a calculation). Individuals is the main form.
I
want curTotal calculation from the subform to appear
as
the value for a field on Individuals- this field is
RunningTotal. I think there are 2 or 3 steps in
need to
go through:

1) in the subform (BucksMember) code add:
forms!Individuals.RunningTotal = curTotal

2) on the mainform (Individuals) I have the unbound
field
RunningTotal. Do I leave it unbound, or will it
have a
control source? If I leave it blank, no value
appears
in
this field so it isn't getting the information it
needs
from BucksMember VB curTotal calculation.

3) in the mainform (Individuals) code do I need to do
anything?

I can't seem to get the calculation based on the
subform
to appear as a field on my mainform. I don't think I
have a syntax issue, but I do think I may not have
all
of
the steps right. Details are much appreciated.
Thanks,
Annie


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Sorry for the empty post -

Here's what I'd do:
In your main form (Individuals), write a function like this:
(Watch for line wrapping if you're cutting and pasting)
Private Function curTotal(ContactID As Integer) as Currency
Dim strWhere As String
Dim curBucksNumber As Currency
Dim curITABucks As Currency

strWhere = "[ContactID] = " & Nz(ContactID, 0)

curBucksNumber = Nz(DSum("BucksNumber", "BucksMember",
strWhere), 0)
curITABucks = Nz(DLookup("SumOfITABucks", "ITABucks",
strWhere), 0)
curTotal = curBucksNumber + curITABucks

End Function

Now, you can set the ControlSource of your [Running Total] textbox to this:
=curTotal([ContactID])
That should display correctly when you first open the form, and also any
time you move to a different contact (record) on your main form.

It remains, then, to recalculate this whenever a record changes on the
subform.
(This includes adding a new record.)
I think the best place to do this is in the subform's Form_AfterUpdate
event.
One line of code should do it:
Me.Parent![Running Total].Requery

At least that's my understanding of how it should work.
Give it a try and let me know.



Annie said:
When my form/subform opens, I want to see curTotal (VB
calc) in RunningTotal (mainform field). RunningTotal will
be the curTotal before any entries happen in this session.

That way, the user will know whether or not there are
enough "bucks" to engage in this session's entry (not
really that important if this session's entry is not
negative- as in are there enough "bucks earned to
justify "cashing-in" the amount in BucksNumber?- and I do
have code to give a message that when shouldn't "cash-in"
that many but thought it would be nice to show the running
total).
On save of this session, it would be nice if RunningTotal
would reflect curTotal plus this session's BuckNumber so
that the user doesn't have to manually refresh the field
and will be able to see up-to-the minute RunningTotal.

Thanks for hanging in there through my explanations! Can
this be done? Annie
-----Original Message-----
You're right - Form_Open isn't a good place to do this.
BucksNumber_BeforeUpdate will only run if you've made a change to
BucksNumber -
is that what you want?

Annie said:
I think I understand what you're saying. I stuck the code
in
Private Sub BucksNumber_BeforeUpdate(Cancel As Integer)

This subprocess calculates curTotal before the current
BucksNumber entry (since BucksNumber can be negative and I
don't want current BucksNumber to be absolute value
greater than curTotal).

So, I now tried to stick
forms!Individuals.RunningTotal = curTotal
Private Sub Form_Open(Cancel As Integer)
figuring that on open, I'd like to see the total, but of
course this sub doesn't know curTotal. So I left it back
in BucksNubmer_BeforeUpdate where it isn't working. How
do I put forms!Individuals.RunningTotal = curTotal in a
subprocess that will not only recognize curTotal but will
do something? Thanks, Annie
Here's the code (inelegant as it may be):
Private Sub BucksNumber_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim curTotal As Currency
Dim strMsg As String 'MsgBox message.
Dim bWarn As Boolean 'Flag to warn user.
Dim curBucksNumber As Currency
Dim curITABucks As Currency
Dim curAbsBucksNumber As Currency

strWhere = "[ContactID] = " & Nz([ContactID], 0)

curBucksNumber = Nz(DSum ("BucksNumber", "BucksMember",
strWhere), 0)
curITABucks = Nz(DLookup ("SumOfITABucks", "ITABucks",
strWhere), 0)
curTotal = curBucksNumber + curITABucks
curAbsBucksNumber = Abs(Me!BucksNumber)

[Forms]![Individuals]![RunningTotal] = curTotal

If ((curTotal) < (curAbsBucksNumber)) Or ((Me!
BucksNumber) < -30) Then
Cancel = True
strMsg = strMsg & "Member does not have enough
Bucks to 'cash in' that many." & vbCrLf
End If
Call CancelOrWarn(Cancel, bWarn, strMsg)
End Sub


-----Original Message-----
What is your "existing subprocess"?
The code at the top of the procedure probably starts with
Private Sub ...
although Private may be Public, and Sub may be Function.
Can you post that entire line?

Interesting!
I just stuck it in my existing subprocess. Obviously,
I'm
a terrible at code. How do I make the code run? Do I
need another subprocess of some sort on my subform?
Thank
you for the help. Annie

-----Original Message-----
You haven't said what causes the code in Step 1 to run.

message
Hi. I'm just not getting this. I have VB code on a
subform BucksMember to calculate curTotal (not a
field,
just a calculation). Individuals is the main form.
I
want curTotal calculation from the subform to appear
as
the value for a field on Individuals- this field is
RunningTotal. I think there are 2 or 3 steps in
need to
go through:

1) in the subform (BucksMember) code add:
forms!Individuals.RunningTotal = curTotal

2) on the mainform (Individuals) I have the unbound
field
RunningTotal. Do I leave it unbound, or will it
have a
control source? If I leave it blank, no value
appears
in
this field so it isn't getting the information it
needs
from BucksMember VB curTotal calculation.

3) in the mainform (Individuals) code do I need to do
anything?

I can't seem to get the calculation based on the
subform
to appear as a field on my mainform. I don't think I
have a syntax issue, but I do think I may not have
all
of
the steps right. Details are much appreciated.
Thanks,
Annie


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
You are FABULOUS!! That did it! And I don't know how you
figured it out "long distance". I really appreciate you
taking the time to understand the problem and then
explaining the solution so well. Yippee! Now I
understand the the calculation needed to be performed on
the Parent record, not the subform. And I understand
private functions better. Thanks so much! Annie
-----Original Message-----
Sorry for the empty post -

Here's what I'd do:
In your main form (Individuals), write a function like this:
(Watch for line wrapping if you're cutting and pasting)
Private Function curTotal(ContactID As Integer) as Currency
Dim strWhere As String
Dim curBucksNumber As Currency
Dim curITABucks As Currency

strWhere = "[ContactID] = " & Nz(ContactID, 0)

curBucksNumber = Nz(DSum("BucksNumber", "BucksMember",
strWhere), 0)
curITABucks = Nz(DLookup("SumOfITABucks", "ITABucks",
strWhere), 0)
curTotal = curBucksNumber + curITABucks

End Function

Now, you can set the ControlSource of your [Running Total] textbox to this:
=curTotal([ContactID])
That should display correctly when you first open the form, and also any
time you move to a different contact (record) on your main form.

It remains, then, to recalculate this whenever a record changes on the
subform.
(This includes adding a new record.)
I think the best place to do this is in the subform's Form_AfterUpdate
event.
One line of code should do it:
Me.Parent![Running Total].Requery

At least that's my understanding of how it should work.
Give it a try and let me know.



Annie said:
When my form/subform opens, I want to see curTotal (VB
calc) in RunningTotal (mainform field). RunningTotal will
be the curTotal before any entries happen in this session.

That way, the user will know whether or not there are
enough "bucks" to engage in this session's entry (not
really that important if this session's entry is not
negative- as in are there enough "bucks earned to
justify "cashing-in" the amount in BucksNumber?- and I do
have code to give a message that when shouldn't "cash- in"
that many but thought it would be nice to show the running
total).
On save of this session, it would be nice if RunningTotal
would reflect curTotal plus this session's BuckNumber so
that the user doesn't have to manually refresh the field
and will be able to see up-to-the minute RunningTotal.

Thanks for hanging in there through my explanations! Can
this be done? Annie
-----Original Message-----
You're right - Form_Open isn't a good place to do this.
BucksNumber_BeforeUpdate will only run if you've made a change to
BucksNumber -
is that what you want?

I think I understand what you're saying. I stuck the code
in
Private Sub BucksNumber_BeforeUpdate(Cancel As Integer)

This subprocess calculates curTotal before the current
BucksNumber entry (since BucksNumber can be negative and I
don't want current BucksNumber to be absolute value
greater than curTotal).

So, I now tried to stick
forms!Individuals.RunningTotal = curTotal
Private Sub Form_Open(Cancel As Integer)
figuring that on open, I'd like to see the total, but of
course this sub doesn't know curTotal. So I left it back
in BucksNubmer_BeforeUpdate where it isn't working. How
do I put forms!Individuals.RunningTotal = curTotal in a
subprocess that will not only recognize curTotal but will
do something? Thanks, Annie
Here's the code (inelegant as it may be):
Private Sub BucksNumber_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim curTotal As Currency
Dim strMsg As String 'MsgBox message.
Dim bWarn As Boolean 'Flag to warn user.
Dim curBucksNumber As Currency
Dim curITABucks As Currency
Dim curAbsBucksNumber As Currency

strWhere = "[ContactID] = " & Nz([ContactID], 0)

curBucksNumber = Nz(DSum ("BucksNumber", "BucksMember",
strWhere), 0)
curITABucks = Nz(DLookup ("SumOfITABucks", "ITABucks",
strWhere), 0)
curTotal = curBucksNumber + curITABucks
curAbsBucksNumber = Abs(Me!BucksNumber)

[Forms]![Individuals]![RunningTotal] = curTotal

If ((curTotal) < (curAbsBucksNumber)) Or ((Me!
BucksNumber) < -30) Then
Cancel = True
strMsg = strMsg & "Member does not have enough
Bucks to 'cash in' that many." & vbCrLf
End If
Call CancelOrWarn(Cancel, bWarn, strMsg)
End Sub


-----Original Message-----
What is your "existing subprocess"?
The code at the top of the procedure probably starts with
Private Sub ...
although Private may be Public, and Sub may be Function.
Can you post that entire line?

Interesting!
I just stuck it in my existing subprocess. Obviously,
I'm
a terrible at code. How do I make the code run?
Click to expand...
Do
I
need another subprocess of some sort on my subform?
Thank
you for the help. Annie

-----Original Message-----
You haven't said what causes the code in Step 1
Click to expand...
to
run.
message
Hi. I'm just not getting this. I have VB code
Click to expand...
on
a
subform BucksMember to calculate curTotal (not a
field,
just a calculation). Individuals is the main form.
I
want curTotal calculation from the subform to appear
as
the value for a field on Individuals- this
Click to expand...
field
is
RunningTotal. I think there are 2 or 3 steps in
need to
go through:

1) in the subform (BucksMember) code add:
forms!Individuals.RunningTotal = curTotal

2) on the mainform (Individuals) I have the unbound
field
RunningTotal. Do I leave it unbound, or will it
have a
control source? If I leave it blank, no value
appears
in
this field so it isn't getting the information it
needs
from BucksMember VB curTotal calculation.

3) in the mainform (Individuals) code do I need to do
anything?

I can't seem to get the calculation based on the
subform
to appear as a field on my mainform. I don't think I
have a syntax issue, but I do think I may not have
all
of
the steps right. Details are much appreciated.
Thanks,
Annie


.



.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Glad to have been of service.
I can well remember when I was asking questions like yours.
Keep at it, and in a while you may be answering others' questions, too!

- Turtle

Annie said:
You are FABULOUS!! That did it! And I don't know how you
figured it out "long distance". I really appreciate you
taking the time to understand the problem and then
explaining the solution so well. Yippee! Now I
understand the the calculation needed to be performed on
the Parent record, not the subform. And I understand
private functions better. Thanks so much! Annie
-----Original Message-----
Sorry for the empty post -

Here's what I'd do:
In your main form (Individuals), write a function like this:
(Watch for line wrapping if you're cutting and pasting)
Private Function curTotal(ContactID As Integer) as Currency
Dim strWhere As String
Dim curBucksNumber As Currency
Dim curITABucks As Currency

strWhere = "[ContactID] = " & Nz(ContactID, 0)

curBucksNumber = Nz(DSum("BucksNumber", "BucksMember",
strWhere), 0)
curITABucks = Nz(DLookup("SumOfITABucks", "ITABucks",
strWhere), 0)
curTotal = curBucksNumber + curITABucks

End Function

Now, you can set the ControlSource of your [Running Total] textbox to this:
=curTotal([ContactID])
That should display correctly when you first open the form, and also any
time you move to a different contact (record) on your main form.

It remains, then, to recalculate this whenever a record changes on the
subform.
(This includes adding a new record.)
I think the best place to do this is in the subform's Form_AfterUpdate
event.
One line of code should do it:
Me.Parent![Running Total].Requery

At least that's my understanding of how it should work.
Give it a try and let me know.



Annie said:
When my form/subform opens, I want to see curTotal (VB
calc) in RunningTotal (mainform field). RunningTotal will
be the curTotal before any entries happen in this session.

That way, the user will know whether or not there are
enough "bucks" to engage in this session's entry (not
really that important if this session's entry is not
negative- as in are there enough "bucks earned to
justify "cashing-in" the amount in BucksNumber?- and I do
have code to give a message that when shouldn't "cash- in"
that many but thought it would be nice to show the running
total).
On save of this session, it would be nice if RunningTotal
would reflect curTotal plus this session's BuckNumber so
that the user doesn't have to manually refresh the field
and will be able to see up-to-the minute RunningTotal.

Thanks for hanging in there through my explanations! Can
this be done? Annie

-----Original Message-----
You're right - Form_Open isn't a good place to do this.
BucksNumber_BeforeUpdate will only run if you've made a
change to
BucksNumber -
is that what you want?

I think I understand what you're saying. I stuck the
code
in
Private Sub BucksNumber_BeforeUpdate(Cancel As Integer)

This subprocess calculates curTotal before the current
BucksNumber entry (since BucksNumber can be negative
and I
don't want current BucksNumber to be absolute value
greater than curTotal).

So, I now tried to stick
forms!Individuals.RunningTotal = curTotal
Private Sub Form_Open(Cancel As Integer)
figuring that on open, I'd like to see the total, but of
course this sub doesn't know curTotal. So I left it
back
in BucksNubmer_BeforeUpdate where it isn't working. How
do I put forms!Individuals.RunningTotal = curTotal in a
subprocess that will not only recognize curTotal but
will
do something? Thanks, Annie
Here's the code (inelegant as it may be):
Private Sub BucksNumber_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim curTotal As Currency
Dim strMsg As String 'MsgBox message.
Dim bWarn As Boolean 'Flag to warn user.
Dim curBucksNumber As Currency
Dim curITABucks As Currency
Dim curAbsBucksNumber As Currency

strWhere = "[ContactID] = " & Nz([ContactID], 0)

curBucksNumber = Nz(DSum
("BucksNumber", "BucksMember",
strWhere), 0)
curITABucks = Nz(DLookup
("SumOfITABucks", "ITABucks",
strWhere), 0)
curTotal = curBucksNumber + curITABucks
curAbsBucksNumber = Abs(Me!BucksNumber)

[Forms]![Individuals]![RunningTotal] = curTotal

If ((curTotal) < (curAbsBucksNumber)) Or ((Me!
BucksNumber) < -30) Then
Cancel = True
strMsg = strMsg & "Member does not have enough
Bucks to 'cash in' that many." & vbCrLf
End If
Call CancelOrWarn(Cancel, bWarn, strMsg)
End Sub


-----Original Message-----
What is your "existing subprocess"?
The code at the top of the procedure probably starts
with
Private Sub ...
although Private may be Public, and Sub may be
Function.
Can you post that entire line?

message
Interesting!
I just stuck it in my existing subprocess.
Obviously,
I'm
a terrible at code. How do I make the code run? Do
I
need another subprocess of some sort on my subform?
Thank
you for the help. Annie

-----Original Message-----
You haven't said what causes the code in Step 1 to
run.

message
Hi. I'm just not getting this. I have VB code on
a
subform BucksMember to calculate curTotal (not a
field,
just a calculation). Individuals is the main
form.
I
want curTotal calculation from the subform to
appear
as
the value for a field on Individuals- this field
is
RunningTotal. I think there are 2 or 3 steps in
need to
go through:

1) in the subform (BucksMember) code add:
forms!Individuals.RunningTotal = curTotal

2) on the mainform (Individuals) I have the
unbound
field
RunningTotal. Do I leave it unbound, or will it
have a
control source? If I leave it blank, no value
appears
in
this field so it isn't getting the information it
needs
from BucksMember VB curTotal calculation.

3) in the mainform (Individuals) code do I need
to do
anything?

I can't seem to get the calculation based on the
subform
to appear as a field on my mainform. I don't
think I
have a syntax issue, but I do think I may not have
all
of
the steps right. Details are much appreciated.
Thanks,
Annie


.



.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top