Access - VBA to match a table

[dab1477]

I have a form field that I would like to perform a calculation upon the exit from a second form field. For instance...
FormField1 is a quantity showing how many "units" were created for the day for the Category "Scrolls". I also have a table in my database containing 6 Categories - including Scrolls and 3 columns showing associated earned minutes or earned hours for each Category plus some other data details.
I want to calculate FormField1 x the associated Earned Minutes or Earned Hours from the table "tbl_Tooling_Product_Type" and display the result in FormField2 text box. For example: If I input 5 into FormField1, upon exit, I want FormField2 to calculate sum(FormField1 x the value for Earned Hours from tbl_Tooling_Product_Type for SCROLLS (this is the specific category type that relates to FormField1). This value resides in column 4 of the 6th row of the table "tbl_Tooling_Product_Type"
My first inclination is to create the following code:
Private Sub Expense_Type_Input_AfterUpdate()
Dim strSql1 As String
Dim strSql2 As String
Dim strSql3 As String
Dim strSql4 As String
Dim strSql5 As String
Dim strSql6 As String

strSql1 = "1-10 size"
strSql2 = "11-23 size"
strSql3 = "24-39 size"
strSql4 = "40-59 size"
strSql5 = "60+ size"
strSql6 = "Scrolls"
If FormField1.Value >0 Then
FormField2=sum(FormField1 * tbl_Tooling_Product_Type.RowSource = strSql16
End Sub

I can't get the calculation to use the value in the 6th row, 4th column of the "tbl_Tooling_Product_Type" table. I believe it has something to do with RowSource. If successful, FormField2 would calculate as sum(5*.41) where 5 was the FormField1 input on the form and .41 is the value residing in 4th column of 6th row of "tbl_Tooling_Product_Type". The 6th row will ALWAYS be the row I want associated with FormField1. The value in column 4 MAY be updated occasionally. I don't want to use a fixed value in my calculation so that if I update the value, I don't have to find the code, I simply update the table.

Am I on the right track? I'm stuck on how to use the right rowsource from the table. Help is appreciated.

I hope I am clear. I'm somewhat a neophite at coding.

Thanks

dab1477

 

[PHV]

Have a look at the DLookUp function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dab1477]

Dlookup worked. Thank you.

 

[SkipVought]

MS Access questions are best addressed in one of the many MS Access formus like forum705, forum703, forum702 and many others.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]