Solved Calculate textbox value (1 Viewer)

estelleh

Member
Local time
Today, 06:12
Joined
Jul 16, 2021
Messages
30
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!
Local time
Today, 12:12
Joined
May 7, 2009
Messages
13,784
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
Local time
Today, 06:12
Joined
Jul 16, 2021
Messages
30
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

  • After_Update code.JPG
    After_Update code.JPG
    36.2 KB · Views: 78
  • With Calculation.JPG
    With Calculation.JPG
    23.2 KB · Views: 83
  • With Datasource.JPG
    With Datasource.JPG
    23.8 KB · Views: 82

Gasman

Enthusiastic Amateur
Local time
Today, 05:12
Joined
Sep 21, 2011
Messages
8,826
Have you walked though the code with F8?
 

estelleh

Member
Local time
Today, 06:12
Joined
Jul 16, 2021
Messages
30
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
Local time
Today, 06:12
Joined
Jul 16, 2021
Messages
30
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 :)

 

estelleh

Member
Local time
Today, 06:12
Joined
Jul 16, 2021
Messages
30
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 :confused:
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:12
Joined
Sep 21, 2011
Messages
8,826
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!
Local time
Today, 12:12
Joined
May 7, 2009
Messages
13,784
change your function to:
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)
 

Users who are viewing this thread

Top Bottom