Record date and time when continous form updated

[systemx]

Hi all,

I have a continuous bound form based on a query (designed to look a little like a spreadsheet).

When the user clicks on a check box, I would like the date to be written to the table for the corresponding record.

I have this code -

Dim ADOrs As ADODB.Recordset
Dim strSQL As String
   
strSQL = "SELECT * FROM tblCertifieds WHERE Reference_No = '" & Me.Reference_No.Value & "'"
   
'Update date and time captured
Set ADOrs = New ADODB.Recordset
ADOrs.ActiveConnection = CurrentProject.Connection
ADOrs.Open strSQL, , adOpenKeyset, adLockOptimistic

With ADOrs
    .fields("Date_Captured") = Date
    .Update
    .Close
End With

Set ADOrs = Nothing

The code seems to work problematically - sometimes recording the date, sometimes doing nothing. The major issue is that when I go click the checkbox on another record, the 'Write Conflict' error occurs.

Is there a better approach to achieving this outcome? I get the feeling I am doing this the hard way!

Thanks,

Rob

 

[MStef]

Don't complicate.
Look at "DemoDateUpdateA2000.mdb"
Open Form2.

 

[MStef]

If you want date and time look at a new one.

 

[systemx]

Thanks MStef.

Just one of those things...I knew the approach was arse about but could'nt think of the logical (simple) way of addressing the problem!

I will simply use a non-visible control and include it in the query.

Cheers,

Rob