I want to update a table by entering a new date on a form.

[QMDirk]

I want to update a table by entering a new date on a form. The form itself is based on a query that looks at a field named "Last Reviewed" and returns any records older than "today - 1 year". The form shows the Spec Number, Product Name, and Last Reviewed fields. And there is a command button to trigger the update event. It's the next step that I'm tripping over. How to make the Last Reviewed field in the table change to the 'New Reviewed Date' on the form?

 

[theDBguy]

What does your UPDATE query look like?

 

[Isaac]

You may consider writing an UPDATE query in SQL, building a dynamic SQL string by referencing the New Reviewed Date on the form.

Totally untested, but something like:

dim strSQL as string
strSQL = "Update tablename set fieldname = #" & Me.Controlname & "# where [IDField]=" & me.IDControlname
CurrentDb.Execute strSQL, dbFailOnError

 

[QMDirk]

You may consider writing an UPDATE query in SQL, building a dynamic SQL string by referencing the New Reviewed Date on the form.

Totally untested, but something like:

dim strSQL as string
strSQL = "Update tablename set fieldname = #" & Me.Controlname & "# where [IDField]=" & me.IDControlname
CurrentDb.Execute strSQL, dbFailOnError

Thanks. All good stuff. Turns out that the form I made, based on a query of tblBOM, will update the table by changing the date and saving so it was really easy. Another simple query and I opened a different form, on the Double-Click event, to view the entire record. Thanks again.

 

[Isaac]

Ok cool