Microsoft Access vba update date of record on button click

Question

I have a form with combobox cmbProjectName where a project name can be selected from tblProjects. The form also has a button btnComplete.

When the button is clicked, I would like to update the record for the selected project so that field Date Modified is filled with today's date. I'm having trouble coding up the VBA to find the correct record to update.

The field I am looking to update is tblProjects.[Last Modified], and I would like to find the record by referencing the combobox cmbProjectName by using the column tblProjects.projName.

Answer 1

This could be handled several different ways, however, it looks like you might be trying to find a VBA SQL solution. As such, try this:

Currentdb.Execute "UPDATE tblProjects " & _
"SET tblProjects.[Last Modified] = DATE()
WHERE tblProjects.projName = " & cmbProjectName.Value & "", dbfailonerror

This would of course require that the tblProjects.projName be a unique value for the entire table. Otherwise, you will need some other sort of solution.