MS Access 2003: Perform an update with VBA code
This MSAccess tutorial explains how to perform an update to a table using VBA code in Access 2003 (with step-by-step instructions).
Question: In Microsoft Access 2003/XP/2000/97, I have a table which has two fields - DepositAmount and WithdrawalAmount. I want to use VBA code to update the Withdrawal Column to equal the Deposit Column then to zero the Deposit column. How can I do this?
Answer: You can update your DepositAmount and WithdrawalAmount fields using a custom function.
Open your Access database, click on the Modules tab and create a new Module. Paste in the following code:
Function UpdateAmounts() As Boolean Dim db As Database Dim LUpdate As String On Error GoTo Err_Execute Set db = CurrentDb() 'Change Withdrawal Column to equal the Deposit Column ' and then zero the Deposit Column LUpdate = "update [TempTGTable]" LUpdate = LUpdate & " set [WithdrawalAmount] = [DepositAmount]," LUpdate = LUpdate & " [DepositAmount] = 0" db.Execute LUpdate, dbFailOnError Set Lrs = Nothing Set db = Nothing UpdateAmounts = True On Error GoTo 0 Exit Function Err_Execute: UpdateAmounts = 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 DepositAmount and WithdrawalAmount fields.
If after trying this example, you receive a "not defined" error on the "Dim db as Database" declaration, you will need to follow some additional instructions.
Advertisements