record date a record is modified (1 Viewer)

[hefly]

I have a field in table tblInstruments called ModDate. I want to record the date that a record is modified.

I have tried two things on my input form but neither are working.

First solution I tried is:

Private Sub Form_AfterUpdate()
Docmd.RunSQL UPDATE tblInstruments SET tblInstruments.ModDate = Date();

End Sub


That give me a syntex error.



I have tried:

Private Sub Form_Dirty(Cancel As Integer)
Me!ModDate.Value = Date
End Sub

That does nothing. I tried that code under AfterUPdate and the input form froze.

I am obviously a novice at VBA and have no clue to how to create code to perform my update. I have searched the web for examples and have not found anything. I'm stuck. :banghead:

Can someone please help?

Thank you.

Hefly

 

[sneuberg]

The argument to Docmd.RunSQL needs to be a string and the date from the date function need to be concatenated in with date delimiters so this:

Docmd.RunSQL[COLOR="green"] UPDATE tblInstruments SET tblInstruments.ModDate = Date();[/COLOR]

would need to be something like:

DoCmd.RunSQL [COLOR="blue"]"[/COLOR]UPDATE tblInstruments SET tblInstruments.ModDate = [COLOR="red"]#[/COLOR][COLOR="blue"]"[/COLOR] & Date & "[COLOR="red"]#[/COLOR]"

 

[TJPoorman]

The argument to Docmd.RunSQL needs to be a string and the date from the date function need to be concatenated in with date delimiters so this:

Docmd.RunSQL[COLOR="green"] UPDATE tblInstruments SET tblInstruments.ModDate = Date();[/COLOR]

would need to be something like:

DoCmd.RunSQL [COLOR="blue"]"[/COLOR]UPDATE tblInstruments SET tblInstruments.ModDate = [COLOR="red"]#[/COLOR][COLOR="blue"]"[/COLOR] & Date & "[COLOR="red"]#[/COLOR]"

You don't have to break out the date in delimiters. Date() is a function that does not require being broken out.
So from the original string all that is necessary is to put quotes around the SQL string.

Docmd.RunSQL [COLOR="red"]"[/COLOR]UPDATE tblInstruments SET tblInstruments.ModDate = Date();[COLOR="Red"]"[/COLOR]

 

[sneuberg]

@TJPoorman Thanks for catching that.

@Hefly. I think you need more in this update statement. Both my and TJPoorman's version will update all of the records. To update the one being modified you need to identify it in a WHERE clause. If for example ID is the primary key in the recordset then.

DoCmd.RunSQL "UPDATE tblInstruments SET tblInstruments.ModDate = Date() WHERE [ID] = " & Me.ID

if instead the primary key field is a text field let's say InstrumentsPK then the textbox would have to be in single quotes like:

DoCmd.RunSQL "UPDATE tblInstruments SET tblInstruments.ModDate = Date() WHERE [InstrumentsPK] = '" & Me.InstrumentsPK & "'"

Note that the field name are assumed to be the same as the textbox names in these examples.