How to run a calculation if check box is NOT checked

[NerdyOne]

I have a field on a form that is calculates tax on another field called Account_Total. Right now, the calculation in the Tax field is as follows:

This is the event procedure for the form's Op Open event:

If Me.Check20 = False Then
Me.Tax = Me.Account_Total * 0.065
Else
Me.Tax = 0
End If

But when you try to open the form, it gives the error message: "You can't assign a value to this object". When you click on Debug it's highlight the line Me.Tax = Me.Account_Total * 0.065

Please provide any thoughts you may have. Thank you in advance.

 

[luceze]

First of all you probably should not call the event from the on open event of your form. It should be called from the on update event of your check box. Secondly, from you error message it appears that your tax textbox is a bound textbox. You typically would not store a calculated field in a table. You should store the value of the CheckBox in the table and run your calculations in your queries/forms/reports.

That said you should change the tax textbox to unbound and the code should update the value in the tax textbox.

HTH,
Eric

 

[NerdyOne]

Your suggestion worked great, my only problem is pulling a report. Since there are no events in a report, and I'm not good at VB, what could can I use? How do I pull the report, and depending on the state of the check box, calculate the tax field?

Also, is there a way to do this without VB? If I knew what statement I could build in expression builder, then I'd be alright.

Thanks again for the great and quick reponses!

 

[luceze]

In your report (and now that I think about it in your form also) you can perform the calculation based on the value stored in the checkbox field. When checked the value will be a -1, unchecked will be a 0. You can use this in your report or form to calculate the tax:
=iif(YourCheckField=-1,Account_Total*.065,0)

Change YourCheckField to the name of your check box field in your report. You will need to add this field to your report and set its visible property to no.

HTH,
Eric

 

[NerdyOne]

Worked like a charm! I don't have much VB experience but I do understand expression builder and after seeing your answer, it made sense! Thanks so much, Eric!

 

[luceze]

Your Welcome.

 

[fixious]

I used the below code in the OnClick event of the check box.

Private Sub Check384_Click()
If Me.Check384 = False Then
Me.YFDue = DateAdd("yyyy", 10, [Yellow Fever Taken])
Else
Me.YFDue = "Exempt"
End If
End Sub

The problem being I had to change my YF due to an unbound box in order for it to work which doesn't update each individual record. The formula works but doesn't in the same. Any ideas? Thank you