Date stamp when specific field changes.

[Ventura]

Could someone please tell me if it is possible to record (Date stamp?) when a specific 'yes/no' field in a table is changed from false to true? i.e. record the date in another field in the same table.

Many thanks

 

[CJ_London]

it is possible, yes. but you would do it using a form, not directly in the table. But would have thought you don't need the yes/no field - if the date field is blank, it can be considered false, if it's not, then its true

 

[Ventura]

it is possible, yes. but you would do it using a form, not directly in the table. But would have thought you don't need the yes/no field - if the date field is blank, it can be considered false, if it's not, then its true

Thanks for your reply.
Let me expand on my original post. There is a yes/no field in a form as a tick box, and what I need is to record the date when a user ticks that particular field.

 

[isladogs]

Hi Ventura

Whilst agreeing with the first reply, just to add the following:

I assume you aren't talking about the timestamp datatype used in SQL server tables as that is a binary field used to detect table field value changes.
Despite its name, it doesn't actually show a date/time!

 

[isladogs]

Sorry just clicked send before reading your last message.

Yes it is easy enough to do using code on the click event of your checkbox.
Something like (replacing generic names with your own):

If Me.CheckBoxName = True Then
DoCmd.RunSQL "UPDATE TableName SET TableName.DateUpdated = Date WHERE TableName.ID = " & Me.ID & ";"
End If

You might need to tweak that a bit for your purposes
I'd also suggest adding DoCmd.SetWarnings False before the update & set as true afterwards

 

[Galaxiom]

If Me.CheckBoxName = True Then
DoCmd.RunSQL "UPDATE TableName SET TableName.DateUpdated = Date WHERE TableName.ID = " & Me.ID & ";"
End If

I'd also suggest adding DoCmd.SetWarnings False before the update & set as true afterwards

If the Update fails for any reason, (for example the checkbox is ticked while entering a new record) Set Warnings False will cause the procedure to quietly ignore the error. Action queries are better performed using CurrentDb.Execute with the inclusion of the second argument as dbFailOnError, so that an error is raised if there is a failure.

Better still, avoid query problems entirely by simply including the DateUpdated field in the form's recordsource and write the date to it where it will be included when the record is saved. This is more logical, since abandoning edits on the form will also avoid the DateUpdated field being changed in the table.

If Me.CheckBoxName Then Me.DateUpdated = Date

Some developers like to include the = True condition but it is not necessary to compare what is already a Boolean value.

Also note that it is not essential to include a DateUpdated control on the form. Just having the field in the RecordSource is sufficient if you don't want to display it.

 

[isladogs]

If Me.CheckBoxName Then Me.DateUpdated = Date

I agree with Galaxiom's comments - keep it simple!

In my post I had also meant to say, setting warnings false should only be done once you know something works in normal use!

 

[almahmood]

You can use Data Macro to do this type of jobs but you have to have Access 2010 or higher version. Otherwise, you can write VBA like other adivised.

 

[jdraw]

Ventura,

Are you trying to keep a history of the Yes/No changes to this record?
If so, you may want a separate table and record other field(s)--username, other...
Perhaps a little more info on what the field is really measuring and what the change means in business terms would help readers.