View Full Version : How to automate calculations using vba
I'm trying to add two random numbers together on a form in ms access and with the click of a calculate button, the result will appear in a blank field. But when I click on the calculate button there is no result in the empty label field. This is the code I wrote.(Below) Any suggestions as to why the result isn't showing on the blank label box(field)? Is my code correct? Any solutions to resolve this would be more than welcome.:friends:
Private Sub Command1_Click()
'To add the values in text box 1 and text box 2
Sum = Val(Text1.Text) + Val(Text2.Text)
'To display the answer on label 1
Label101.Caption = Sum
End Sub
I would use an Unbound text box rather than a Label with something like this
Private Sub Command1_Click()
'To add the values in text box 1 and text box 2 & To display the answer in the new unboundtextbox
unboundtextboxname = Val(Text1) + Val(Text2)
End Sub
Where "unboundtextboxname" is the actual name of your text box.
geekgirlau
09-09-2010, 03:58 AM
Actually I wouldn't have a command button - use the AfterUpdate event for the text boxes.
Private Sub Text1_AfterUpdate()
'To add the values in text box 1 and text box 2 & To display the answer in the new unboundtextbox
unboundtextboxname = Val(nz(Text1, 0)) + Val(nz(Text2, 0))
End Sub
Private Sub Text2_AfterUpdate()
'To add the values in text box 1 and text box 2 & To display the answer in the new unboundtextbox
unboundtextboxname = Val(nz(Text1, 0)) + Val(nz(Text2, 0))
End Sub
I added the code to the cost tectbox field, discount textbox field and Total textbox field and the following error messgae popped up - 'This error occurs when an event has failed to run because Microsoft Office Access cannot evaluate the location of the logic for the event. For example, if the OnOpen property of a form is set to =[Field], this error occurs because Access expects a macro or event name to run when the event is fired'.
Have I applied the code to the correct textboxes? Or should I apply the code to the total text box? I'm sure it's logical
Thanks
geekgirlau
09-10-2010, 01:43 AM
Can you post your code?
Here is the code I wrote:
Private Sub Cost_AfterUpdate()
unboundtextboxname = Val(Nz(CostField, 0)) + Val(Nz(Discount, 0))
End Sub
geekgirlau
09-10-2010, 02:00 AM
So is the field called "Cost" or "CostField"?
geekgirlau
09-10-2010, 02:33 AM
You have to check that your code is using the correct field names, and I'm not sure from your response which is correct.
Private Sub Cost_AfterUpdate()
unboundtextboxname = Val(Nz(CostField, 0)) + Val(Nz(Discount, 0))
End Sub
Which is right? And is there a control named "unboundtextboxname"?
Thanks, I cracked it! I used an expression with a formula...many thanks!
Movian
09-16-2010, 08:10 AM
If i could make 1 minor adjustment.
I have in the past run in to problems using code along the lines of
Private Sub Cost_AfterUpdate()
unboundtextboxname = Val(Nz(CostField, 0)) + Val(Nz(Discount, 0))
End Sub
I now ensure that i use the me. identifier for each reference of a control on a form. This eliminated a corruption problem and cross reference issue that had been causing me severe issues for a couple weeks so i would suggest using
Private Sub Cost_AfterUpdate()
me.unboundtextboxname = Val(Nz(me.CostField, 0)) + Val(Nz(me.Discount, 0))
End Sub
Just my 2 cents :)
I applied your theory to my total field and this messge: (#Name) appears on the total field what does this mean?
Thanks - I'm learning a lot about performing calculations in ms access...
Imdabaum
09-20-2010, 08:16 AM
#Name error means that it has been bound to a control or formula that cannot be referenced. Either you have misspelled the name of the control or the control source doesn't exist. If the control is bound to a calculation then the calculation might have an error in it.
Movian
09-21-2010, 06:44 AM
Also in some situations I have had #name appear if the text box is not wide enough to show the whole text (For a date or similar).
To calculate the field total are you using a VBA Procedure or a "Calculation" directly in the source field ?
My tip was designed only for VBA code not for a script.
I'm using an expression written in a source field.
Movian
09-21-2010, 07:00 AM
Then you do not need the me. clarification i only mentioned it because the examples given were for vba code in the afterupdate sub for the field. Not for an expression in the source field.
So the me. is probably causing the problem and giving you the #name error
You have to check that your code is using the correct field names, and I'm not sure from your response which is correct.
Private Sub Cost_AfterUpdate()
unboundtextboxname = Val(Nz(CostField, 0)) + Val(Nz(Discount, 0))
End Sub
Which is right? And is there a control named "unboundtextboxname"?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.