Access: Update a date field with VBA code in Access 2003/XP/2000/97

Question:  In Access 2003/XP/2000/97, I have a database which contains a date field called TransactionDate. I want to be able to query the user for a new date and then update the TransactionDate field based on what the user entered.

Answer:  You can update your date 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 UpdateTransactionDate() As Boolean

    Dim db As Database
    Dim LUpdate As String
    Dim LMsg As String

    Dim LTransactionDt As Date

    On Error GoTo Err_Execute

    'Query user for Date of R
    LMsg = "Enter the Date of R Form __/__/____"
    LMsg = LMsg & Chr(10) & Chr(10) & "Format date as:   mm/dd/yyyy"

    LTransactionDt = InputBox(LMsg)

    Set db = CurrentDb()

    'Re-Assign Date to TransactionDate
    LUpdate = "update [ChapterFormBulkInputTable]"
    LUpdate = LUpdate & " set [TransactionDate] = #" & Format(LTransactionDt, "mm/dd/yyyy") & "#"

    db.Execute LUpdate, dbFailOnError

    Set db = Nothing

    MsgBox "Changing the Dates was Successful."
    UpdateTransactionDate = True

    On Error GoTo 0

    Exit Function

Err_Execute:

    MsgBox "Updating the dates failed, you will need to enter each date individually."
    UpdateTransactionDate = 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 TransactionDate field.

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.