CODEWITHSUNDEEP
ms-accessbuttoncommandvba
DtheHut
DtheHut

Reputation: 51

Updating a date record in a table using VBA & cmd button

I'm trying to get a Command Button to update a date on a table. The button is in a separate form. Right now I'm trying:

Private Sub Command13_Click()

 Update Master
     Set [Last Complete] = Date()
End Sub

"Master" being the table I'm trying to update and "Last Complete" being the specific record.

Access throws an error message with my code, complaining "sub or function isn't properly defined".

I've worked quite a bit with VBA in Excel but not so much with Access.

Upvotes: 0

Views: 2476

Answers (1)

HansUp
HansUp

Reputation: 97131

The problem is that a SQL statement is not valid VBA code.

You can place the statement in a string and use the DAO.Database.Execute method to execute it:

Private Sub Command13_Click()
    CurrentDb.Execute "Update Master Set [Last Complete] = Date()"
End Sub

Assuming that UPDATE statement is valid and does what you want, that could be all you need. However, here is another version which demonstrates features you may find useful:

Private Sub Command13_Click()
    Dim db As DAO.Database
    Dim strUpdate As String
    strUpdate = "Update Master Set [Last Complete] = Date()"
    Debug.Print strUpdate
    Set db = CurrentDb
    db.Execute strUpdate, dbFailOnError
    Debug.Print db.RecordsAffected & " rows updated"
    Set db = Nothing
End Sub

Upvotes: 1

Related Questions