In this video you will find out how to load a table into a recordset object, loop through the records, and vba calculate 2 fields. Also you’ll find out how to make a user defined function and call it from a query.

Need to know how to insert into a table? See this video – MS Access SQL

'========================================================
'BY LOEBLCOM SERVICES 2012
'ERIK LOEBL(713)409-7041
'EMAIL: erik@loeblcomservices.com
'WEB:  http://loeblcomservices.com
'========================================================


Option Compare Database

Sub loop_products()
    
    'in this example, we simply load the Products table into a  & _
        recordset object, and loop through the table.
        

    Dim rst As Object
    Dim strSQL As String
    
    strSQL = "SELECT UnitPrice,UnitsInStock FROM Products"
    
    Set rst = CurrentDb.OpenRecordset(strSQL)
    
    rst.MoveFirst
    Do Until rst.EOF
        Debug.Print rst.Fields("UnitsInStock")
        rst.MoveNext
    Loop
End Sub

Sub loop_products_and_calculate()
    
    'in this example, we simply load the Products table into a  & _
        recordset object, and loop through the table calculating 2 fields, & _
        UnitPrice * UnitsInStock

    Dim rst As Object
    Dim strSQL As String
    Dim dblResult As Double
    
    strSQL = "SELECT UnitPrice,UnitsInStock FROM Products"
    
    Set rst = CurrentDb.OpenRecordset(strSQL)
    
    rst.MoveFirst
    Do Until rst.EOF
        dblResult = rst.Fields("UnitPrice") * rst.Fields("UnitsInStock")
        Debug.Print "The result = " & dblResult
        rst.MoveNext
    Loop
End Sub

Function calculate_this(num1, num2) As Double

    'in this example, we simply calculate the 2 passed numbers
    
    Dim dblResult As Double
    
    dblResult = num1 * num2
    
    calculate_this = dblResult
End Function
    

You can download the database file, by clicking here.