Having trouble with the "Lost_Focus" command

[southphillyman]

I have a form that will multiple the unit price by the quanity when the quantity textbox loses focus.
eg:

Private Sub txtPart3Quantity_LostFocus()
CalculateOrder

End Sub

But when i tab off the quantity textbox, an error comes up saying there was a problem with MS communicating with the OLE server or ActiveX Control.
What does this mean?
Do I have to set the textbox to some certain setting or something?
I thought lost_focus meant when I tabbed off the textbox my function would calculate.

[kfcSmitty]

What is CalculateOrder? is it a procedure/function? If so it should look like

CalculateOrder()

What line does it highlight when the error occurs? it could be happening in the CalculateOrder procedure.

[southphillyman]

CalculateOrder is a function.

[kfcSmitty]

Did you try what I suggested in my first post?

[southphillyman]

Why would I put a () after the function name if I'm calling it?
I tried it tho, and an error came up saying "=" was expected. I thought you could just call a function by name with out the pathethsis

[kfcSmitty]

Does your CalculateOrder return a value? if so, you will need to assign it to a variable

IE)

CalculatedOrder = CalculateOrder()


There is nothing wrong with the call as far as I can see. Can you post the function code as well please?

[southphillyman]

here it is:

Sub CalculateOrder()


On Error GoTo CalculateOrder_Error

Dim Part1UnitPrice As Double, Part1Quantity As Byte
Dim Part2UnitPrice As Double, Part2Quantity As Byte
Dim Part3UnitPrice As Double, Part3Quantity As Byte
Dim Part4UnitPrice As Double, Part4Quantity As Byte
Dim Part5UnitPrice As Double, Part5Quantity As Byte

Dim Part1SubTotal As Double
Dim Part2SubTotal As Double
Dim Part3SubTotal As Double
Dim Part4SubTotal As Double
Dim Part5SubTotal As Double
Dim JobPrice1 As Double
Dim JobPrice2 As Double
Dim JobPrice3 As Double
Dim JobPrice4 As Double
Dim JobPrice5 As Double

Dim TotalParts As Double
Dim TotalLabor As Currency
Dim TaxRate As Double
Dim TaxAmount As Currency
Dim RepairTotal As Currency

'get the unit price from the text boxes
Part1UnitPrice = txtPart1UnitPrice
Part2UnitPrice = txtPart2UnitPrice
Part3UnitPrice = txtPart3UnitPrice
Part4UnitPrice = txtPart4UnitPrice
Part5UnitPrice = txtPart5UnitPrice

'get the Qty from the text boxes
Part1Quantity = txtPart1Quantity
Part2Quantity = txtPart2Quantity
Part3Quantity = txtPart3Quantity
Part4Quantity = txtPart4Quantity
Part5Quantity = txtPart5Quantity

'calculate the subtotals
Part1SubTotal = Part1UnitPrice * Part1Quantity
Part2SubTotal = Part2UnitPrice * Part1Quantity
Part3SubTotal = Part3UnitPrice * Part3Quantity
Part4SubTotal = Part4UnitPrice * Part4Quantity
Part5SubTotal = Part5UnitPrice * Part5Quantity

'put the subtotals in proper textboxes
txtPart1SubTotal = Part1SubTotal
txtPart2SubTotal = Part2SubTotal
txtPart3SubTotal = Part3SubTotal
txtPart4SubTotal = Part4SubTotal
txtPart5SubTotal = Part5SubTotal

'total up parts
TotalParts = CCur(Part1SubTotal + Part2SubTotal + Part3SubTotal + Part4SubTotal + Part5SubTotal)


'obtain the labor cost
JobPrice1 = txtJobPrice1
JobPrice2 = txtJobPrice2
JobPrice3 = txtJobPrice3
JobPrice4 = txtJobPrice4
JobPrice5 = txtJobPrice5

'calculate total labor
TotalLabor = CCur(JobPrice1 + JobPrice2 + JobPrice3 + JobPrice4 + JobPrice5)

'get the taxrate
TaxRate = txtTaxRate
TaxAmount = (TotalLabor + TotalParts) * (TaxRate / 100)

RepairTotal = TotalLabor + TotalParts + TaxAmount

'show the results
txtRepairTotal = RepairTotal
txtTaxAmount = TaxAmount
txtTotalLabor = TotalLabor
txtTotalParts = TotalParts




'End Sub

'If there is a problem address it here
CalculateOrder_Error:

If Error = 94 Then
MsgBox "Make sure you enter the proper format" & vbCrLf & "Try Again!"

End If

End Sub

and i'm calling it in something like:

Private Sub txtJobPrice1_LostFocus()
CalculateOrder

End Sub

but when i tab off jobprice1 textbox an error pops up saying the OLE server or activex is not communicating

[kfcSmitty]

First of all, thats a procedure, not a function

A function returns a value.


Anyways, I see a couple bugs that will cause errors in your code.

'get the unit price from the text boxes
Part1UnitPrice = txtPart1UnitPrice
Part2UnitPrice = txtPart2UnitPrice
Part3UnitPrice = txtPart3UnitPrice
Part4UnitPrice = txtPart4UnitPrice
Part5UnitPrice = txtPart5UnitPrice
 
'get the Qty from the text boxes
Part1Quantity = txtPart1Quantity
Part2Quantity = txtPart2Quantity
Part3Quantity = txtPart3Quantity
Part4Quantity = txtPart4Quantity
Part5Quantity = txtPart5Quantity
 
'obtain the labor cost
JobPrice1 = txtJobPrice1
JobPrice2 = txtJobPrice2
JobPrice3 = txtJobPrice3
JobPrice4 = txtJobPrice4
JobPrice5 = txtJobPrice5

In the above code you are referencing the entire object of a textbox. That could be what is causing your error.

you need to reference it as such

JobPrice1 = Val(txtJobPrice1.Value)

The above code references the value within the textbox, rather than the entire textbox itself. Also, the "Val" function takes in the string value from txtJobPrice1.Value and returns a numerical equivalent.


For this one, it should be the opposite:

'put the subtotals in proper textboxes
txtPart1SubTotal = Part1SubTotal
txtPart2SubTotal = Part2SubTotal
txtPart3SubTotal = Part3SubTotal
txtPart4SubTotal = Part4SubTotal
txtPart5SubTotal = Part5SubTotal

txtPart1SubTotal.Value = Part1SubTotal

I use the .Value property because in VBA if you use .Text it needs to receive focus.

HTH

[southphillyman]

.


.



In the above code you are referencing the entire object of a textbox. That could be what is causing your error.

you need to reference it as such

JobPrice1 = Val(txtJobPrice1.Value)

The above code references the value within the textbox, rather than the entire textbox itself. Also, the "Val" function takes in the string value from txtJobPrice1.Value and returns a numerical equivalent.


For this one, it should be the opposite:

'put the subtotals in proper textboxes
txtPart1SubTotal = Part1SubTotal
txtPart2SubTotal = Part2SubTotal
txtPart3SubTotal = Part3SubTotal
txtPart4SubTotal = Part4SubTotal
txtPart5SubTotal = Part5SubTotal

txtPart1SubTotal.Value = Part1SubTotal

I use the .Value property because in VBA if you use .Text it needs to receive focus.

HTH

what is the advantage of using the Val function to convert the string to numerical?
Also why is it bad to take the entire content of the textbox?

And for the subtotal why is it opposite? If I am placing a value in the subtotal textbox....don't I want to say "This textspace is equal to the calculation in variable part1subtotal"?
which is : txtpart1subtotal is equal to part1subtotal
switching it to "part1subtotal = txtpart1subtotal" would make it 0

[kfcSmitty]

I didn't mean it should be the opposite of what you have, i meant it would be the opposite of the example I gave previously.

IE) First example was: Textbox1.Value = whatever
Opposite of that example is: whatever = Textbox1.Value

If you look below where I said "opposite" I gave an example right after the clippet of code


The advantage of using Val is that it converts it to a numerical value. If you want to use a numerical value and you have text, it shouldnt work. I am not sure exactly what happens, but your numbers should have been off. Perhaps it convered the string into its ascii values, not exactly sure.

Referencing the textbox object isn't really "taking the entire content of the textbox", it is referencing every property and everything associated with it. Referencing the "Text" or "Value" parameter only grabs what the user has entered into the textbox itself.

IE) just referencing the textbox would grab its Size, fontcolour, bgcolour, etc.

[southphillyman]

Yea man i'm getting the same error. I don't know why it doesn't work

[kfcSmitty]

What line of code does it actually highlight during this error?

[southphillyman]

What line of code does it actually highlight during this error?

no line of code. This errors occurs when i'm using the actual form. when i enter values into the unit price and quantity it's fine. then when i tab over to subtotal price (which is supposed to trigger the lost_focus procedurces) the error occurs. a pop up box pops up and it's not highlighting any area of code in my code part.

[sparbag]

Hello,
I have always been advised/taught to catch the OnExit method of textboxes as attempting to catch a LostFocus event is a can of worms in that once focus is moved to another control on the form you have to programmatically work from that control and not the one that's lost focus, most people use the mouse to move between controls.
If the loss of focus is because the user has moved to another form then the focus event is irrelevant as you have to programmatically test another forms control. Setting modal will assist this.

Steve

[southphillyman]

Sparbag i'm not understanding what you are trying to say. The error occurs whether I tab or use the mouse to go to the subtotal textbox.

[kfcSmitty]

I really have no idea why its doing this...One of the more experienced VBA guys, such as RobDog, would probably know the answer.


As to what sparbag was referring to; He was just trying to give a coding tip, as in his experience, the LostFocus function has given him trouble.

[sparbag]

Hello,
I think what I was trying to communicate, quite poorly, is catch the OnExit event to fire the CalculateOrder() subroutine. The OnExit event is fired immediately when a different control is accessed.
Lost_Focus events are as I have attempted to express awkward, difficult and should be avoided as a last resort.

Steve

[southphillyman]

Ok let me try to use onexit's instead.

[salvelinus]

Just to add, maybe unnecessarily, it's sometimes problematic to use events that fire (even if nothing happens in other events) at the begining or end of a control's "activity". As sparbag wrote, the lost_focus event can cause problems because it happens after the first control lost focus, so the code in that event may throw an error if it needs to reference the first control. I ran into this type of problem trying to run code sequentially from one control to the next in a project.
What I've never found, except in limited cases like opening a form, is a list showing what events fire in what order for various controls. Some are obvious, but many events fire even if nothing happens in them. For example, say you .SetFocus to another control. What happens first, the .GetFocus event for the second control, or the .LostFocus event for the first control? I suppose you could log or msgbox all the control events to figure it out, but there could be a ton of different combinations of this type of thing.