UPDATE is especially useful when you want to change many records or when the records that you want to change are in multiple tables. The following example increases the Order Amount values by 10 percent and the Freight values by 3 percent for shippers in the United Kingdom: Sub Update X() Dim dbs As Database Dim qdf As Query Def ' Modify this line to include the path to Northwind ' on your computer.
Set dbs = Open Database("Northwind.mdb") ' Change values in the Reports To field to 5 for all ' employee records that currently have Reports To ' values of 2. Execute "UPDATE Employees " _ & "SET Reports To = 5 " _ & "WHERE Reports To = 2;" dbs.
I went about it by creating two recordsets and trying a loop through SQL Update. Close End Sub I hate to be the one to tell you this, but you should reconsider using SQL to do this update.
However I end up with all 100 records containing just the last Sales Rep ID 100 times repeating. I see that you have already written a lot of code and might feel like if you switch back to SQL that you will then have wasted all this vb code. But you can solve this problem with SQL with an order of magnitude less code(or nearly so).
Data Macros are new in Access 2010 and not created in the Macros section, but from the Table. Qty_mt) AS Sum Of Qty_mt FROM Orders INNER JOIN Order Detail ON Orders.
2) Can you use a Macro in conjunction with an update query or is there some reason you can't use update queries?
Execute LUpdate, db Fail On Error Set Lrs = Nothing Set db = Nothing Update Amounts = True On Error Go To 0 Exit Function Err_Execute: Update Amounts = False End Function You can then call the function from a Form (ie: button click event, etc) or a Macro and it will update the Deposit Amount and Withdrawal Amount fields.
To be honest, your query doesn't make much sense, as it says: "update the Quantity to 5 where the Quantity is 5". Refine your query in Access, and once it works, paste it back to the VBA code.Value") Call Do SQL Next i End Sub Public Sub Do SQL() Dim SQL As String SQL = "UPDATE Lead Source " & _ "SET [Quantity] = " & LCt Lead Source & _ "WHERE [ID] = " & i Do Cmd****n SQL SQL End Sub(CBox Lead Source LS is a combo box I have on a form that has a list of all the values in the 'Lead Source' column.This is so the user can select an individual item to get the stats for just that item) When I try running this I get this error: That second option worked brilliantly thanks. Hi, I'm fairly new to Access and SQL but have been using VBA in Excel for years so have a fairly good understanding of forms etc.My problem is that I have a table (Lead Source) with 3 columns (ID (unique integer from 1 to 37, Primary Key), Lead Source (text) & Quantity (integer)).