VBA Calculate – Multiply
by admin on Monday, December 31st, 2012 | No Comments
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.