Updating a date field in a record with the current date

[Newby]

I am setting up a "to-do" table and I want a button that will update the "date last completed" with the current date. Also, I was watching a u-tube video show how a macro can be converted to code, but I do not see that function in Access 2007. Is that a feature of Access 2010?

[RuralGuy]

Here's ac2007 in pictures: http://www.btabdevelopment.com/ts/de...aspx?PageId=91

[orange]

typical format for an SQL UPDATE query

UPDATE table SET column = expression WHERE predicates;

You must be careful with UPDATE query, if you do not constrain
the request with a proper WHERE clause, you could UPDATE all records in your table.

Here is a typical Update query in vba in a button click event. Hope it helps.

Private Sub Command4_Click()

Dim DB As DAO.Database
Dim sql As String
   On Error GoTo Command4_Click_Error

sql = " UPDATE tblCabinet  " _
    & " SET  MyDate = Date " _
    & " WHERE RepairOrderNo = 12345"  
Set DB = CurrentDb
DB.Execute sql, dbFailOnError

   On Error GoTo 0
   Exit Sub

Command4_Click_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description  _ 
         & ") in procedure Command4_Click of VBA Document Form_Form5"
End Sub

Do some research on SQL queries. There is a tutorial at
http://www.w3schools.com/sql/default.asp

[Newby]

typical format for an SQL UPDATE query

UPDATE table SET column = expression WHERE predicates;

You must be careful with UPDATE query, if you do not constrain
the request with a proper WHERE clause, you could UPDATE all records in your table.

Here is a typical Update query in vba in a button click event. Hope it helps.

Private Sub Command4_Click()

Dim DB As DAO.Database
Dim sql As String
   On Error GoTo Command4_Click_Error

sql = " UPDATE tblCabinet  " _
    & " SET  MyDate = Date " _
    & " WHERE RepairOrderNo = 12345"  
Set DB = CurrentDb
DB.Execute sql, dbFailOnError

   On Error GoTo 0
   Exit Sub

Command4_Click_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description  _ 
         & ") in procedure Command4_Click of VBA Document Form_Form5"
End Sub

Do some research on SQL queries. There is a tutorial at
http://www.w3schools.com/sql/default.asp

Private Sub Cmd_Update_Click()

Dim DB As DAO.Database
Dim sql As String
On Error GoTo Command4_Click_Error

sql = " UPDATE to_dos " _
& " SET DateCompleted = Date " _ DateCompleted is my field Name
& " WHERE ID = 1"
Set DB = CurrentDb
DB.Execute sql, dbFailOnError

On Error GoTo 0
Exit Sub

Command4_Click_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description _
& ") in procedure Command4_Click of VBA Document Form_Form5"
End Sub


Note: I need this to update the DateCompleted field with the current date for whatever record that I am view in a form. I hard coded the Where command above to “ID = 1” to see if I could get the program to update record 1, but got this message instead.

Error 3061 (Too Few paramenters. Expected 1.) in procedure Command4_Click of VBA Document form _Form5

[orange]

Private Sub Cmd_Update_Click()

Dim DB As DAO.Database
Dim sql As String
   On Error GoTo Command4_Click_Error

sql = " UPDATE to_dos  " _
    & " SET  DateCompleted = Date " _  '''''''DateCompleted is my field Name
    & " WHERE ID = 1"
Set DB = CurrentDb
DB.Execute sql, dbFailOnError

   On Error GoTo 0
   Exit Sub

Command4_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description _
         & ") in procedure Command4_Click of VBA Document Form_Form5"
End Sub

Ok so you do have a button on a form called Cmd_Update and
you do have a numeric Id field in your record?

You need to separate a comment from code with at least one ' (I used several to highlight where this is in your code)

[Newby]

Private Sub Cmd_Update_Click()

Dim DB As DAO.Database
Dim sql As String
   On Error GoTo Command4_Click_Error

sql = " UPDATE to_dos  " _
    & " SET  DateCompleted = Date " _  '''''''DateCompleted is my field Name
    & " WHERE ID = 1"
Set DB = CurrentDb
DB.Execute sql, dbFailOnError

   On Error GoTo 0
   Exit Sub

Command4_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description _
         & ") in procedure Command4_Click of VBA Document Form_Form5"
End Sub

Ok so you do have a button on a form called Cmd_Update and
you do have a numeric Id field in your record?

You need to separate a comment from code with at least one ' (I used several to highlight where this is in your code)

That comment was not in my code . I added that comment to this thread only. Some where in this code I need to identify that the current record is the record whose date field I want to update. This is where I am stuggling.

[John_G]

Hi -

Regarding your error 3061 - you are getting that because either DateCompleted or ID (or both) is not the name of a field in your table to_dos.

John