Form is not Updating Table 1

[jrtaylor]

I have a form created using a query where nine payments are tracked by date. There is also a total payment field. My form is totaling the payments because of the following in the Control Source of the properties for the “Payment Total”: =[txtPayment1Amount]+[txtPayment2Amount]+[txtPayment3Amount]+[txtPayment4Amount]+[txtPayment5Amount]+[txtPayment6Amount]+[txtPayment7Amount]+[txtPayment8Amount]+[txtPayment9Amount]

My problem is the table that should be updated when the form is closed is not picking up the total. How to I get the table updated when the Payment Total field is change? I know this is probably very simple.

Appreciate the help!

jrtaylor

 

[jerseyboy]

jrtaylor,

I think by your explanation that you have an UNBOUND textbox on your form. If the textbox Control Source starts with "=" then it is unbound. You should see the field name otherwise.

If you want update the value create another text box using the Field List box from the tool bar. If you select Payment Total then that will be your Control Source. Then tell the form to populate the box from the unbound text box in an Event Procedure.
Sorry I don't have time for more, but if no one else chimes in I will finish later.

JerseyBoy
Remember: self-praise is no recommendation

 

[LonnieJohnson]

The payment total field is not actually linked to your table.

Tradition would have it that you don't even try and store this information. If it is showing on the screen, you have what you need in front of you. If you want it on a report or query, then you use the same expression in a new field. The reason why is because, if someone were to change the raw data in the underlying table your total field would not get updated and the informaiton on any reports or queries would be wrong. Again, as a rule, you do not store calculated fields.

Now, if you just have to have it then you may want to...

1. Create an invisible field on your form.
2. Make the control source the (total) field name in your table.
3. Now you need to update this invisible field equal to the field with the expression. You should probably do this every time you update one of the fields that are a part of the sum. You should also do this before the form updates. You should also do it in the form's OnCurrent event. This way you always have the most accurate amount stored. Again this will not help you if you or someone else changes the data at the table level.
4. So that you don't have to code this in each of these procedures, you may want to create a separate procedure in the module and call it from each of these events.

Hope this helps.

prodevmg@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

May God bless you in the year 2002.

 

[jerseyboy]

Thanks for finishing LonnieJohnson, Right on Target!
Just want to add one thing - If you make the control invisible, you will have to show it to update it, then hide it again.
JerseyBoy
Remember: self-praise is no recommendation

 

[LonnieJohnson]

Actually if you update it in the module via VBA then you can do it while it is invisible.

prodevmg@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

May God bless you in the year 2002.

 

[jerseyboy]

Huh?
When I try to update I get an error.

Paraphrased: "cannot set focus on hidden control"
Can you elaborate? Very excited to see what I'm missing.
Thanks JerseyBoy
Remember: self-praise is no recommendation

 

[LonnieJohnson]

No need to set the focus to a control when you set it's value. Say for instance if you were updating a hidden control in the form's Before Update event you just say...

InvisibleControlName = CalculatedControlName

prodevmg@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

May God bless you in the year 2002.

 

[jerseyboy]

I ran some tests on what you said, and initally received the setfocus error.

What I discovered is that I ALWAYS put:
Field1.Text = Field2.Text

The property being included is the problem.

If I leave the property out then works like a champ!

Thanks, I would have never know if I hadn't seen this.

Chalk another up to TEK-TIPS! JerseyBoy
Remember: self-praise is no recommendation