# SolvedCalculate textbox value (1 Viewer)

#### estelleh

##### Member
I have a "Job" form, which contains a JobType field. It also contains an Option Group for costing, with 3 radio buttons. Which type of job is selected (1, 2, or 3) will dictate which of the radio buttons are enabled. On opening the form, as well as when the user changes any of the values that affect the costing, I want to calculate a total field. The calculation will depend on which of the 3 radio buttons is selected.

In English, what I am trying to achieve is by evaluating the Option Group's Value:

If optionGroup.Value = 1 Then
TotalPrintCost = PrintCost
Else if optionGroup.Value= 2 Then
TotalPrintCost = PrintCost * Quantity
Else
TotalPrintCost = (Quantity/1000)*PrintCost

PrintCost and Quantity are fields in the table, as well as textboxes in the form. Part of the problem is I'm not sure if I should be using the table fields or the form objects to do the calculations.

I have been going round and round with this - I feel like there is such a simple answer that I am missing!

Any help would be appreciated #### arnelgp

##### ..forever waiting... waiting for jellybean!
add code to the optonGroup's AfterUpdate event:

private sub optionGroup_AfterUpdate()
If optionGroup.Value = 1 Then
[TotalPrintCost] = [PrintCost]
Else if optionGroup.Value= 2 Then
[TotalPrintCost] = [PrintCost] * [Quantity]
Else
[TotalPrintCost] = ([Quantity]/1000)*[PrintCost]
end if
end sub

'''
note that those fields should be present in your form.

#### estelleh

##### Member
private sub optionGroup_AfterUpdate()
If optionGroup.Value = 1 Then
[TotalPrintCost] = [PrintCost]
Else if optionGroup.Value= 2 Then
[TotalPrintCost] = [PrintCost] * [Quantity]
Else
[TotalPrintCost] = ([Quantity]/1000)*[PrintCost]
end if
end sub
Thanks That is similar to what I have been trying - the textbox comes up blank. I have tried a direct ControlSource to a table field to make sure the problem is not with the textbox, and that works fine.

#### Attachments

• arnelgp

#### Gasman

##### Enthusiastic Amateur
Have you walked though the code with F8?

#### estelleh

##### Member
Have you walked though the code with F8?
Good point....... thanks!

I hadn't this time - the blank textblock is on the initial load, so the After_Update event hasn't been triggered. Now I just have to figure out where to put it for the initial form load.

#### estelleh

##### Member
Thanks @arnelgp and @Gasman - seems to work now. I'm still not sure what I was duing wrong, but at this point I'll just accept it works!

Hppy weekend #### ​

• arnelgp

#### estelleh

##### Member
add code to the optonGroup's AfterUpdate event:

private sub optionGroup_AfterUpdate()
If optionGroup.Value = 1 Then
[TotalPrintCost] = [PrintCost]
Else if optionGroup.Value= 2 Then
[TotalPrintCost] = [PrintCost] * [Quantity]
Else
[TotalPrintCost] = ([Quantity]/1000)*[PrintCost]
end if
end sub

'''
note that those fields should be present in your form.
Good morning!

After getting the code you helped me with working, I realised there were more events that would require that code to be run, so I decided to put it in a Function - so I can run it as a macro from wherever needed.

I created a Module (modJobCostings), and inside that module created a function TotalPrintCost(), which contains the code you gave me.

Code:
``````Option Explicit
Function TotalPrintCost()

If fmeCostPer.Value = 1 Then
[txtTotalPrintCost] = [txtPrintCost]
ElseIf fmeCostPer.Value = 2 Then
[txtTotalPrintCost] = [txtPrintCost] * [txtQty]
Else
[txtTotalPrintCost] = ([txtQty] / 1000) * [txtPrintCost]
End If

End Function``````

The OptionGroup_After Update event has an embedded macro RunCode, with Function Name TotalPrintCost()

When calling the function from the Jobs Form, I get an error from the function "Compile error: External Name not defined" on the field txtPrintCost.

Here's what I've tried:

1) Making it a Public Function
2) Making it a Private Function
3) Changing the field name to [Forms!frmJobs.txtPrintCost]
4) Changing the field name to [Forms!frmJobs!txtPrintCost]

All give me the compile error.

Why does it see the textbox field name as an external name even when I specify the form name?

TIA for any help for this really frustrated newbie #### Gasman

##### Enthusiastic Amateur
If you put the code into a module, then you need to pass the control values to the function?

Code:
``Function TotalPrintCost(intCostPer as Integer, curPrintCost as Currency, intCurQty as Integer)``
Then replace your variables with the corresponding ones in the parameters.

Then call as
Code:
``````Me.txtTotalPrintCost = TotalPrintCost(fmeCostPer,txtPrintCost,txtQty)
[code]``````

Last edited:

#### arnelgp

##### ..forever waiting... waiting for jellybean!
Code:
``````Function TotalPrintCost(ByVal CostPer As Integer, ByVal Quantity, ByVal PrintCost) As Double
If IsEmpty(Quantity) Or IsNull(Quantity) Then
Quantity = 0
End If
If IsEmpty(PrintCost) Or IsNull(PrintCost) Then
PrintCost = 0
End If
If CostPer = 1 Then
TotalPrintCost = PrintCost
ElseIf CostPer = 2 Then
TotalPrintCost = PrintCost * Quantity
Else
TotalPrintCost = (Quantity / 1000) * PrintCost
End If
End Function``````

Me.txtTotalPrintCost = TotalPrintCost(fmeCostPer, txtQty, ,txtPrintCost)

#### estelleh

##### Member
Thank you both  Replies
3
Views
51
Replies
13
Views
336
Replies
2
Views
173
Replies
16
Views
343
Replies
10
Views
246