Microsoft Access 2003 form - won't update 'total field' for existing records

[gbell1sc]

Hi All
I have quite a complex database which I have set up for my job with the Council. Over the last 12 months, I've added and removed various functions to the main form on the database. Yesterday, I added extra fields onto the form called 'amount 1', 'amount 2', 'amount 3', 'amount 4' and 'amount 5' with a total box. For the total box, I set the control source to add up the fields 'amount1 - amount 5'. Now, this works perfectly when I click on new record, it displays the feault value in each amount box and updates the total field perfectly. However, my database already had the details of 200+ people on there already. The part I am struggling with is with the records that were on there before I added the extra fields onto the form - the total box won't update and in each of the amount boxes, there is no default value. How can I get it so that I can input the amounts and it sums up the total for the records that were on the database before I added the extra fields?

 

[ypma]

I am assuuming you have the sum formula in the control of the totals field.
I suspect the reason the old records are now not beeing added to the new total is because of blanks. if this is the case the nz function sould get round that.
Me.Text262 = Nz([Text158], 0) + Nz([Text207], 0) + Nz([Terxt111], 0) + Nz([Text28], 0) + Nz([Text], 0)

The zero is not mandatory but i always put in. Replace my textnumbers with your correct field names. hope this sorts the problem

 

[Galaxiom]

Fields named Amount1, Amount2, etc almost invariably indicate an improper data model. Typically these fields would be a single field in an a related table.

Show us some details about your tables before you go any further. It might save you going off in the wrong direction.

 

[gbell1sc]

WOW - this site is ace, wasn't expecting such a quick reply! Ok, although the database is v complex, it has been built by myself via hours of trawling through google etc and I also had some background knowledge too. Where do I stick the 'Me.Text262 = Nz([Text158], 0) + Nz([Text207], 0) + Nz([Terxt111], 0) + Nz([Text28], 0) + Nz([Text], 0)'? Do I put it in the control source for the total field? What do I replace the Me.Text262 with - do I replace that with the name of the total field?

 

[gbell1sc]

Basically, I have a main form which has details of eeach person me and my colleauges work with. On the form, it has a lot of input fields like name, age, dob, status, staff name etc. The fields I added in was their state benefit income. Each of the amount fields is for a seperate benefit and when the user enters the amount, it is stored in the main table along with all of the other inputted data from the form. The field 'total benefit income' is unbound as I don't need the total amount stored in the table.

 

[gbell1sc]

This is the code which I have in the control source for the total field - =IIf(IsNull([Benefit 1 Amount]+[Benefit 2 Amount]+[Benefit 3 Amount]+[Benefit 4 Amount]+[Benefit 5 Amount]),0,([Benefit 1 Amount]+[Benefit 2 Amount]+[Benefit 3 Amount]+[Benefit 4 Amount]+[Benefit 5 Amount])). I got this code off google, so it may be completely the wrong one! At the moment, if I create a new record in the form, it works perfectly where I can input the applicable amounts and it will total it up. If I close the form, go back into the form and go to the record, it will allow me to update the amounts and it gives me the new total. It it purely with the existing records that it won't work for.

 

[ypma]

mislead you sligllty. my example was for a call procedure ,in this case the me.text262 is not required as you said the whole formula is put in the control source of the totals Field .
= Nz([Text158], 0) + Nz([Text207], 0) + Nz([Text111], 0) + Nz([Text28], 0) + Nz([Text], 0)

 

[gbell1sc]

THANK YOU SOOOOOO MUCH!!! It's now working as it should! The only thing left is to figure out why in each of the Amount 1 to Amount 5 fields, the default £0.00 doesn't show. However, it does show when I create a new record. I've checked the properties for each box and in the deafult value, I've set it to "£0.00". But, like I said, it won't show that default value for records which were already on the database.

 

[ypma]

Glad to have helped. your second problem might be solve by usng an update query which shows all the blank fieds and then update them to zero..
Regards