How to automate calculations using vba

[wedd]

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.


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

[OBP]

I would use an Unbound text box rather than a Label with something like this
[VBA]
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[/VBA]

Where "unboundtextboxname" is the actual name of your text box.

[wedd]

Thank you!

[geekgirlau]

Actually I wouldn't have a command button - use the AfterUpdate event for the text boxes.


[vba]

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


[/vba]

[wedd]

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]

Can you post your code?

[wedd]

Here is the code I wrote:

Private Sub Cost_AfterUpdate()

unboundtextboxname = Val(Nz(CostField, 0)) + Val(Nz(Discount, 0))
End Sub

[geekgirlau]

So is the field called "Cost" or "CostField"?

[wedd]

Cost Field

[geekgirlau]

You have to check that your code is using the correct field names, and I'm not sure from your response which is correct.

[vba]
Private Sub Cost_AfterUpdate()
unboundtextboxname = Val(Nz(CostField, 0)) + Val(Nz(Discount, 0))
End Sub
[/vba]

Which is right? And is there a control named "unboundtextboxname"?

[wedd]

Thanks, I cracked it! I used an expression with a formula...many thanks!

[Movian]

If i could make 1 minor adjustment.

I have in the past run in to problems using code along the lines of

[vba]Private Sub Cost_AfterUpdate()
unboundtextboxname = Val(Nz(CostField, 0)) + Val(Nz(Discount, 0))
End Sub [/vba]
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

[vba]Private Sub Cost_AfterUpdate()
me.unboundtextboxname = Val(Nz(me.CostField, 0)) + Val(Nz(me.Discount, 0))
End Sub
[/vba]
Just my 2 cents

[wedd]

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]

#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]

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.

[wedd]

I'm using an expression written in a source field.

[Movian]

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.

[vba]
Private Sub Cost_AfterUpdate()
unboundtextboxname = Val(Nz(CostField, 0)) + Val(Nz(Discount, 0))
End Sub
[/vba]
Which is right? And is there a control named "unboundtextboxname"?