Using a Yes/No field to trigger a calculation in another field - J

J

JohnW

I have a billing form that was created from a query based on the table that all
these fields are from. In this billing query is where all these calculations
are happening so they can be displayed on the billing form.

TotalTuitions is a field that exists in my main table and it is calculated
in the billing query by adding (4) other tuition type fields.

MultiClassDisc is a field in my main table and is in the billing query.

MultiClassTrigger is a field in my main table that works from a Yes/No combo
box. This will also be in the billing query.

I am tryin to use the MultiClassTrigger
field to force the calculation of the MultiClassDisc field. When the Trigger
field is clicked to Yes I need it to take TotalTuitions*.1 to calculate the
10% discount. When it is clicked No I want MultiClassDisc to equal zero.

I have tried the following IIF statement as an expression in the
MultiClassDisc field in the billing query but this will work the first time
that Yes is selected in the Trigger field but then the value will stay when I
change the Trigger field back to No. With No selected it should change the
discount field back to zero.

MultiClassDisc:IIf([MultiClassTrigger]=Yes,[TotalTuitions]*0.1,0)

I have also tried to put this IIF statement in the AfterUpdate event in the
MultiClassDisc field on the form that comes from the billing query.

I am having no luck. Any suggestions anyone? Thanks
 
D

Danny

In the AfterUpdate event for the MultiClassTrigger field, try this:

If MultiClassTrigger = -1 then

MultiClassDisc = TotalTuitions*.1

Else

MultiClassDisc = 0

End If


JohnW said:
I have a billing form that was created from a query based on the table that all
these fields are from. In this billing query is where all these calculations
are happening so they can be displayed on the billing form.

TotalTuitions is a field that exists in my main table and it is calculated
in the billing query by adding (4) other tuition type fields.

MultiClassDisc is a field in my main table and is in the billing query.

MultiClassTrigger is a field in my main table that works from a Yes/No combo
box. This will also be in the billing query.

I am tryin to use the MultiClassTrigger
field to force the calculation of the MultiClassDisc field. When the Trigger
field is clicked to Yes I need it to take TotalTuitions*.1 to calculate the
10% discount. When it is clicked No I want MultiClassDisc to equal zero.

I have tried the following IIF statement as an expression in the
MultiClassDisc field in the billing query but this will work the first time
that Yes is selected in the Trigger field but then the value will stay when I
change the Trigger field back to No. With No selected it should change the
discount field back to zero.

MultiClassDisc:IIf([MultiClassTrigger]=Yes,[TotalTuitions]*0.1,0)

I have also tried to put this IIF statement in the AfterUpdate event in the
MultiClassDisc field on the form that comes from the billing query.

I am having no luck. Any suggestions anyone? Thanks
 
J

JohnW

Danny

I copied your code into the afterupdate event for the MultClassTrigger field
but nothing happens when I click either Yes or No in that field. I copied it
just like you have it, would that make a difference??

Thanks

--
JCW


Danny said:
In the AfterUpdate event for the MultiClassTrigger field, try this:

If MultiClassTrigger = -1 then

MultiClassDisc = TotalTuitions*.1

Else

MultiClassDisc = 0

End If


JohnW said:
I have a billing form that was created from a query based on the table that all
these fields are from. In this billing query is where all these calculations
are happening so they can be displayed on the billing form.

TotalTuitions is a field that exists in my main table and it is calculated
in the billing query by adding (4) other tuition type fields.

MultiClassDisc is a field in my main table and is in the billing query.

MultiClassTrigger is a field in my main table that works from a Yes/No combo
box. This will also be in the billing query.

I am tryin to use the MultiClassTrigger
field to force the calculation of the MultiClassDisc field. When the Trigger
field is clicked to Yes I need it to take TotalTuitions*.1 to calculate the
10% discount. When it is clicked No I want MultiClassDisc to equal zero.

I have tried the following IIF statement as an expression in the
MultiClassDisc field in the billing query but this will work the first time
that Yes is selected in the Trigger field but then the value will stay when I
change the Trigger field back to No. With No selected it should change the
discount field back to zero.

MultiClassDisc:IIf([MultiClassTrigger]=Yes,[TotalTuitions]*0.1,0)

I have also tried to put this IIF statement in the AfterUpdate event in the
MultiClassDisc field on the form that comes from the billing query.

I am having no luck. Any suggestions anyone? Thanks
 
D

Danny

Did you copy it into the VBA code window, or into the property sheet for the
MultiClassTrigger control? In the property sheet, for this control, for the
After Update event property, you should see "[Event Procedure]", not the code
itself. If the code itself is in there, remove it. Then click on the "..."
button to the right of the property line for After Update, and select "Code
Builder" in the pop up dialogue. Then the VBA window will open. Paste the
code I sent in between the "Private Sub..." and "End Sub" lines.

JohnW said:
Danny

I copied your code into the afterupdate event for the MultClassTrigger field
but nothing happens when I click either Yes or No in that field. I copied it
just like you have it, would that make a difference??

Thanks

--
JCW


Danny said:
In the AfterUpdate event for the MultiClassTrigger field, try this:

If MultiClassTrigger = -1 then

MultiClassDisc = TotalTuitions*.1

Else

MultiClassDisc = 0

End If


JohnW said:
I have a billing form that was created from a query based on the table that all
these fields are from. In this billing query is where all these calculations
are happening so they can be displayed on the billing form.

TotalTuitions is a field that exists in my main table and it is calculated
in the billing query by adding (4) other tuition type fields.

MultiClassDisc is a field in my main table and is in the billing query.

MultiClassTrigger is a field in my main table that works from a Yes/No combo
box. This will also be in the billing query.

I am tryin to use the MultiClassTrigger
field to force the calculation of the MultiClassDisc field. When the Trigger
field is clicked to Yes I need it to take TotalTuitions*.1 to calculate the
10% discount. When it is clicked No I want MultiClassDisc to equal zero.

I have tried the following IIF statement as an expression in the
MultiClassDisc field in the billing query but this will work the first time
that Yes is selected in the Trigger field but then the value will stay when I
change the Trigger field back to No. With No selected it should change the
discount field back to zero.

MultiClassDisc:IIf([MultiClassTrigger]=Yes,[TotalTuitions]*0.1,0)

I have also tried to put this IIF statement in the AfterUpdate event in the
MultiClassDisc field on the form that comes from the billing query.

I am having no luck. Any suggestions anyone? Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top