By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,827 Members | 2,220 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,827 IT Pros & Developers. It's quick & easy.

Access 2003 adding unbound text boxes on form.

P: 38
I have a form and there are some text boxes that show a score depending on what someone picks in the drop down next to that box. I did this part with some coding and therefore, for example if they choose the first option it could be a score of 0.1, if they choose the second option it could be a score of 0.25 and so on. I have the textboxes working correctly and at the bottom there is another text box called BV_Score. I am trying to get BV_Score to add all the other text boxes and give me a total but I can't. Here is what I have in the control source property for BV_Score.
=([BVQ1SCORE]+[BVQ2SCORE]+[BVQ3SCORE]+[BVQ4SCORE]+[BVQ5SCORE]
+[BVQ6SCORE]+[BVQ7SCORE]+[BVQ8SCORE]+[BVQ9SCORE]+[BVQ10SCORE]
+[BVQ11SCORE]+[BVQ12SCORE]+[BVQ13SCORE]+[BVQ14SCORE]+[BVQ15SCORE])
Can anyone help me with this?
Thanks
Amanda
Apr 18 '07 #1
Share this Question
Share on Google+
11 Replies


Rabbit
Expert Mod 10K+
P: 12,366
What is it doing that it shouldn't be doing? Or vice versa?
Apr 18 '07 #2

P: 38
It doesn't put anything in the textbox that should be adding them all together. And if I change it to have it be BVQ1SCORE.Value + BVQ2SCORE.Value, etc. Then in puts Name#? in the text box.

What is it doing that it shouldn't be doing? Or vice versa?
Apr 18 '07 #3

pks00
Expert 100+
P: 280
Howdo Amanda, is there any possibility that any of these fields have no value in them?

What u could try is wrapping it with NZ and give it a default value of 0, see if that helps

=NZ([BVQ1SCORE],0)+NZ([BVQ2SCORE],0)+NZ([BVQ3SCORE],0)+ etc
Apr 18 '07 #4

P: 38
That worked! You are so AWESOME! Thank you so much!

Howdo Amanda, is there any possibility that any of these fields have no value in them?

What u could try is wrapping it with NZ and give it a default value of 0, see if that helps

=NZ([BVQ1SCORE],0)+NZ([BVQ2SCORE],0)+NZ([BVQ3SCORE],0)+ etc
Apr 18 '07 #5

pks00
Expert 100+
P: 280
:)

Hope you understand the use of NZ
Apr 18 '07 #6

P: 38
I looked it up on google and I think I understand. However I have another problem. It shows the total while in the form but it wont save within the table. It shows BV_Score as 0 in the table even when the form said it was 2.5 or something. Any suggestions?

:)

Hope you understand the use of NZ
Apr 18 '07 #7

pks00
Expert 100+
P: 280
91215275208518139147161519


Is this a bounded form?
your BV_Score field is most likely unbounded because u have set the control source to be a calculation instead of the field it should be linked to

What u would need to do is this
Put the control source back to the field in the table

Now for some vba coding
For each BV Score field, use the afterupdate event and call a procedure you are just about to create :)
Afterupdate events are called each time u update the field


eg

private sub BVQ1SCORE_AfterUpdate
UpdateTotal
end sub

private sub BVQ2SCORE_AfterUpdate
UpdateTotal
end sub

etc - create one for each field

Now for the procedure called UpdateTotal
u can create this within the form's code, take the controlsource u had originally and set BV_Score within VBA


private sub UpdateTotal
Me.BV_Score = NZ([BVQ1SCORE],0)+NZ([BVQ2SCORE],0)+NZ([BVQ3SCORE],0) + etc
end sub
Apr 18 '07 #8

P: 38
I did everything you said. However the total box stayed 0 the whole time. Even when the other text boxes were showing the score the total box stayed 0. In the table it also reflects 0, and suggestions?

91215275208518139147161519


Is this a bounded form?
your BV_Score field is most likely unbounded because u have set the control source to be a calculation instead of the field it should be linked to

What u would need to do is this
Put the control source back to the field in the table

Now for some vba coding
For each BV Score field, use the afterupdate event and call a procedure you are just about to create :)
Afterupdate events are called each time u update the field


eg

private sub BVQ1SCORE_AfterUpdate
UpdateTotal
end sub

private sub BVQ2SCORE_AfterUpdate
UpdateTotal
end sub

etc - create one for each field

Now for the procedure called UpdateTotal
u can create this within the form's code, take the controlsource u had originally and set BV_Score within VBA


private sub UpdateTotal
Me.BV_Score = NZ([BVQ1SCORE],0)+NZ([BVQ2SCORE],0)+NZ([BVQ3SCORE],0) + etc
end sub
Apr 19 '07 #9

pks00
Expert 100+
P: 280
Can u put breakpoints and debug or put msgbox in the afterupdate events and procedure UpdateTotal to ensure 1) its going in there and 2) check the values
Apr 19 '07 #10

P: 38
It's not going to the after update events.

Can u put breakpoints and debug or put msgbox in the afterupdate events and procedure UpdateTotal to ensure 1) its going in there and 2) check the values
Apr 19 '07 #11

pks00
Expert 100+
P: 280
ok, in forms design, go to one of your bv fields, then look at the field properties for it. Look in the Events tab. Find "After Update" and click on that
It should then find your code otherwise it would prompt u, select Code Builder
then add the code to call UpdateTotal
Apr 19 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.