Access: Reset a text field to NULL with VBA code in Access 2003/XP/2000/97

Question:  In Access 2003/XP/2000/97, I have a database which contains a text field called TransactionDescription. I want to reset this field back to a NULL (ie: blank value) on all records in my table.

Answer:  You can reset your text field using a custom function.

Open your Access database, click on the Modules tab and create a new Module. Paste in the following code:

Function UpdateTransactionDescription() As Boolean

    Dim db As Database
    Dim LUpdate As String

    On Error GoTo Err_Execute

    Set db = CurrentDb()

    'Change each record
    LUpdate = "update [ChapterFormBulkInputTable]"
    LUpdate = LUpdate & " set [TransactionDescription] = null"
    db.Execute LUpdate, dbFailOnError

    Set db = Nothing

    MsgBox "Resetting the Description was Successful."
    UpdateTransactionDescription = True

    On Error GoTo 0

    Exit Function

Err_Execute:
    MsgBox "Resetting the Description was not successful, change individually "

    UpdateTransactionDescription = False

End Function

You can then call the function from a Form (ie: button click event, etc) or a Macro and it will reset the TransactionDescription field back to a NULL value.

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.