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.

Welcome to Stack Overflow! ...so, you want to update which record? Based on what criteria? The easiest way is with DoCmd.RunSQL but you need a it more info about the table. — ashleedawg
– ashleedawg, Commented Jul 13, 2018 at 13:09
I want to update the record corresponding to the project selected in cmbProjectName (e.g., WHERE tblProjects.projName = cmbProjectName). — Pat
– Pat, Commented Jul 13, 2018 at 14:51

ccarpenter32 · Accepted Answer · 2018-07-13 17:46:16Z

2

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.

edited Jul 13, 2018 at 17:46

answered Jul 13, 2018 at 16:06

ccarpenter32

1,0772 gold badges9 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pat Over a year ago

I'm not seeing where this puts the date into the Date Modified field for the project. I guess I'm needing something like "UPDATE tblProjects SET tblProjects.[Last Modified] = Date WHERE tblProjects.projName = " & cmbProjectName.

ccarpenter32 Over a year ago

Sorry, I completed misinterpreted what you were trying to do there. The answer is updated, if I get what you mean correctly this time, this should accomplish the goal you're shooting for.

Collectives™ on Stack Overflow

Microsoft Access vba update date of record on button click

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related