Access: Perform an update with VBA code in Access 2003/XP/2000/97

Question:  In 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.