---

MS Access 2003: Reset a text field to NULL with VBA code

This MSAccess tutorial explains how to reset a text field to null in a table using VBA code in Access 2003 (with screenshots and step-by-step instructions).

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