form code not working - please help

[dab1477]

Thanks in advance.
I created the following code to do the following: After exiting an input form field (HandsetProduct1), I wanted to perform a calculation in another form field (Handset1_EH_CALC) by looking up a specific value (i,e,EARNED HRS standard) using DLOOKUP function, based upon the input of another form box titled cboLocation and multiplying that standard by the qty entered into the HandsetProduct1.

Bottom line...the Dlookup value will be based upon the selection from cboLocation form field.

ISSUE: The following code does NOT display the calculation result within the desired Handset1_EH_CALC field. I get NO error messages and no result. Just blank.

Code:

Private Sub HandsetProduct1_Exit(Cancel As Integer)
If Me.cboLocation.Value = "Teardown" Then
Me.Handset1_EH_CALC.Value = DLookup("[std hrs wght]", "[tbl_handsetstd]", "[location]" = "Teardown_Individual") * Me.HandsetProduct1.Value
ElseIf Me.cboLocation.Value = "Letter Sorting" Then
Me.Handset1_EH_CALC.Value = DLookup("[std hrs wght]", "[tbl_handsetstd]", "[location]" = "Sorting_Individual") * Me.HandsetProduct1.Value
End If
End Sub

Please point me in the right direction. I want to use the cboLocation field input to determine the criteria for the DLOOKUP function, then multiply that lookup value by the qty input into form box HandsetProduct1 field. The result should display in Handset1_EH_CALC field. I will have multiple ELSEIF for 8 possible inputs for cboLocation. Thanks.

 

[dhookom]

Try something like:

Private Sub HandsetProduct1_Exit(Cancel As Integer)
If Me.cboLocation = "Teardown" Then
  Me.Handset1_EH_CALC = DLookup("[std hrs wght]", "[tbl_handsetstd]", "[location] = 'Teardown_Individual'") * _
    Me.HandsetProduct1
 ElseIf Me.cboLocation = "Letter Sorting" Then
  Me.Handset1_EH_CALC = DLookup("[std hrs wght]", "[tbl_handsetstd]", "[location] = 'Sorting_Individual'") * _
    Me.HandsetProduct1
End If
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[dab1477]

Nope. Still no value in Me.Handset1_EH_CALC. No error message. Field remains blank. I copied code and directly overwrote. Saved code. Shutdown and restart form. Should I have exited Access? I understood alteration of single vs double quotes at end of DLOOKUP, but why omit .value? Trying to understand, thereby gain enlightenment!
Thanks.

 

[dhookom]

Value" is the default property of bound controls so it is optional. Are you actually clicking in the HandsetProduct1 control? I don't really care for storing a value that can be calculated. You might want to use the after update event of the HandsetProduct1 control.

If you are going to write code, you need to understand how to troubleshoot your code faq705-7148.

Duane
Hook'D on Access
MS Access MVP