Calculations in a Form

[Damo1412]

Hi,

I am having problems performing calculations in a form in an Access 2007 database.

The form is used to generate invoices and sales receipts. At the bottom of the form there are 3 text boxes: "Sub-total" (Text128), "VAT" (Text130) and "Total" (Text132). Each text box is bound to the Control Sources "Sub-Total", "VAT" and "Total" respectively in a table called "Orders".

I would like the "VAT" text box to automatically calculate the VAT on an order and enter it into the VAT field in the table "Orders". To do this I have tried using the formula:

=[Text128]*0.2

I would also like the "Total" text box to automatically calculate the total cost of the order and enter it into the Total field in the table "Orders". To do this I have tried using the formula:

=[Text128]+[Text130]

I know that these calculations are correct as they work in an unbound text box however I cannot get this to work with bound text boxes.

Is it actually possible to do this or do I need to use another method to perform these calculations?

Many thanks

 

[billmeye]

For bound controls that you would like to automatically populate based on information recorded in another control you can take advantage of the After Update event to perform the calculation and assign the value.

In the Text128 After Update event add the code to calculate the VAT and Total:

If IsNumeric(Me.Text128) Then 
Me.VAT = Me.Text128 * 0.2
If IsNumeric(Me.Text130) Then Me.Total = Me.Text128 + Me.Text130
End If

In the Text130 After Update event add the code to calculate Total:

If IsNumeric(Me.Text130) And IsNumeric(Me.Text128) Then Me.Total = Me.Text128 + Me.Text130

Ensure that your VAT and Total controls are bound to the fields in your table.

 

[Damo1412]

Thank you so much. That works perfectly.

 

[billmeye]

Your welcome. Glad I could help.

 

[Damo1412]

Hi,

I thought that I this was solved and the solution given by billmeye works perfectly however when I have tried to adapt the formula to perform a calculation using different text boxes I am getting stuck again.

In the same form I enter the quantity of a component in text box "Text16". This is bound to the control source "Quantity Component 1". I then enter the sale price of this component in text box "Text18" which is bound to the control source "Component 1 Sale Price".

I then have another text box "Text145" which is the total sale price of component 1 ie quantity x price. This is bound to the control source "C1TSP".

Ideally I would like the database to automatically calculate the total sale price. Based on the previous answer I have tried the entering the following code:

If IsNumeric(Me.Text16) And IsNumeric(Me.Text18) Then Me.C1TSP = Me.Text16 * Me.Text18
End If

I'm sure it's something small that I'm missing out on but I'm afraid that programming is not exactly my speciality.

Thanks in advance

 

[billmeye]

If IsNumeric(Me.Text16) And IsNumeric(Me.Text18) Then Me.C1TSP = Me.Text16 * Me.Text18
End If

If (no pun intended) you are only resolving one If ... Then statement you don't need the End If. End If is only needed if you have multiple steps to carry out, i.e,

If IsNumeric(Me.Text18) The
Me.Text145 = Me.Text18
Me.Another = Me.Text145 * 2
End If

Otherwise your code looks good. If that is not your problem, what error are you getting?

 

[Damo1412]

Hi billmeye,

Thanks for having another look at this. I'm not actually getting an error message, for some reason nothing happens when I enter the figures into the other fields. The full code is:

Private Sub Text145_AfterUpdate()
IsNumeric(Me.Text16) And IsNumeric(Me.Text18) Then Me.C1TST = Me.Text16 * Me.Text18
End Sub

If I try to enter a value in the C1TST field i receive the error message:

Compile error:

Syntax error

I hope this helps make sense of it.

 

[Cronk]

Um, you did say the name of the text box control was Text145.

Use that instead of the field name to which Text145 is bound.

On to a more general aspect, why do you bother storing the resultant sum paid when it is a derived value. It breaks one of Codd's database design principles.

 

[Damo1412]

Hi Cronk,

Please excuse my ignorance but I have changed the code to:

IsNumeric(Me.Text16) And IsNumeric(Me.Text18) Then Me.Text145 = Me.Text16 * Me.Text18

Which I think is what you meant but I'm still having the same result.

With regards to your other point I had never heard of Codd's database design principles but I will certainly read more into it. The reason for having these figures is to allow the resulting invoice to show a complete break down of the total value ie the cost of each item as well as the tax component of the final price. I thought it would be easier to include each item in the table rather than perform calculations in the report which forms the invoice.

 

[Cronk]

And when you typed it in, did not the line go red, indicating a syntax error?

Like the If is missing from the beginning of the code line.

 

[Damo1412]

Thank you, I didn't realise that I still needed to leave the "If" statement in. It all works perfectly now.

Just out of curiosity, is there anywhere online that I can learn about these statements for future use?

 

[Cronk]

Yes. GIYF. You could google search for, in this case, vba if statement

I guess there are any number of vba web tutorial sites. And of course, Access has its own Help.

 

[Damo1412]

Thanks for the advice. I didn't even know this code was VBA. I'm going to start reading up on it over the weekend.

Thanks again for your time and help.

 

[Cronk]

AccessBlaster

Mate, it's just not so much caring but keeping it simple and living an easier life.

Codd's rules just state the bleeding obvious, maybe in academic terms and I ain't no academic, but one rule says don't store in a table, the value derivation based on other fields.