Calculate on a form (1 Viewer)

[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

 

[Isskint]

hi dab1477

If i understand your problem correctly, you need to lookup the earned minutes/hours in the tbl_Tooling_Product_Type table, dependant on the Category the user is recording data for. The way to do this is to use the DLOOKUP function. DLookup(expr, domain, [criteria]) where expr is the field you are looking to return data from, domian is the table or query the data is stored in and [criteria] is an optional string expression used to restrict the range of data on which the DLookup function is performed

if you have a field Category on your form then the code in the FormField1_AfterUpdate event would look like this:
FormField2=DLOOKUP("[EarnedMinutes]","[tbl_Tooling_Product_Type]","[Category]='" & [Category] & "'")

Alternatively, if it will always be "Scrolls" that you would want to lookup, you could use
FormField2=DLOOKUP("[EarnedMinutes]","[tbl_Tooling_Product_Type]","[Category]='Scrolls'")


Another way to do this without resorting to coding, is to set the control source for the form to a query (a query based on the table the form is already based on) and include the dlookup in the query. That value can then be used as the control source for field2 on your form.

 

[dab1477]

Worked Great. Thank you.