---

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.