Error msg if total field does not add up

[louisa]

Hi all, me again.

I have a field called OverallPercentage and also Percentage, in overall percentage a value should be entered for example 10. The percentage field is set to continuous form and data is inputed into it.

I am looking for an error message to appear if the values entered into the percentage field do not add up to the overall percentage value of 10.

Percentage - 4
Percentage - 2
Percentage - 3
The total above would be 9 but should be 10, this is when i am hoping an error message can be displayed.

Thanks for looking,

 

[MStef]

Send a short example of your MDB, (access 2000 or 2002-2003).

 

[louisa]

View attachment Example.zip

I have attached a screen shot of my form hopefully you can see now what i am hoping to get.

 

[vbaInet]

Have you considered conditional formatting? Perform a =Sum([Field]) in the Footer of the form and use conditional formatting to make it red if it doesn't add up.

 

[louisa]

I could do that, i know how to get it to total the fields but wouldnt have a clue how to make it turn red if it didnt add up to the overall percentage value.

 

[vbaInet]

Like I mentioned, Conditional Formatting. Do some research.

 

[vbaInet]

And here's what came up after performing a quick search on Google for the term "contional formatting Access":

http://office.microsoft.com/en-us/a...using-conditional-formatting-HA010208133.aspx

http://www.databasedev.co.uk/conditional_report_formatting.html

http://www.techonthenet.com/access/reports/cond_format2.php

 

[MStef]

Send the MDB on the forum.

 

[vbaInet]

Send the MDB on the forum.

Have a look at Post #3

 

[louisa]

Hi Everyone,

I have been looking into the conditional formatting...
I have placed in the footer of my form a total field with a control source of =SUM([Percentage]) this all works fine and adds up the value of all data inputed in to the percentage fields.
I have a field in the header of my for which is bound and called OverallPercentage.
Now the way i want this to work is if the calculated field does not add up to the value in OverallPercentage then the background will turn red.
I have tried to format the =SUM([Percentage]) field to if field not equal to "OverallPercentage" then it turns red, the field itself stays red even if it does add up. I have tried other ways also like field value is less than "OverallPercentage" and it also always stays red. I cannot get this to work. Is it because the conditional formatting is based on a calculated field? Any help greatly appreciated.

 

[vbaInet]

Just a slight correction louisa, Sum([Percentage]) isn't a field, it's a calculation. Percentage is the field.

So in your conditional formatting expression you should be using the name of the textbox that has Sum([Percentage]) as the control source. Don't write:

If Sum([Percentage]) Is Not Equal OverallPercentage

Use:

If [NameOfTextbox] Is Not Equal OverallPercentage.

And another thing, ensure that your default value is set too unless it will remain red.