How can I tell if a user edits a field/column in a table

[eHigh]

How can I tell if a user opens a table in datasheet view and edits a field/column in the table?

I would like to write to a log table anytime a user edits a table field. The logFunction below writes to the log table using CurrentUser.

For example:

If table opened
logFunction "testTable has been opened"
End If

Or

If table field edited
logFunction "testTable field has been edited"
End If

Thanks in advance for any assistance

 

[MoLaker]

I don't think you can if the table is opened directly. It's not a good idea to allow users to open a table directly, anyway. As you are finding out, you lose control. You could force users to go through a form (which could be in datasheet view if you wish) and use the form's BeforeUpdate event to write to a log or transaction table.

 

[Zion7]

On the appropriate events, on the particular form you want to monitor, write a procedure, which log to LogTable, when & what edits took place.

On AfterUpdate event of each field, I would put, for example...

Private Sub txtAddress_AfterUpdate()
Call LogChange "txtAddress"

Private Sub txtFirstName_AfterUpdate()
Call LogChange "txtFirstName"

etc....


Sub LogChange(strFieldName As String)
Dim wks As WorkSpace
Set wks = DBEngine(0)
Dim rec as ADODB.Recordset
Set rec = new ADODB.Recordset
rec.Open "tblLogEdits",CurrentProject.Connection,acDynaset,acLockOptimistic

rec.AddNew
rec!DateEdited = Date
rec!txtTimeEdited = Time
rec!txtTextEdited = strFieldName
rec!txtUser = wks.Users(CurrentUser).Name
rec.Update
End Sub

...something to this effect. You could, enhance it to prevent duplicate or redundant records.

eg; if a textbox gets frivously edited, 3 times within a certain amt of time, you could just edit the LogTable, not Add a new record.

rec.Open "SELECT * FROM logTable WHERE rec!txtUser = wks.Users(CurrentUser).Name And rec!txtDate = Date And (rec!txtTime Between (Time - 10) And Time) ..excuse the syntax

If rec.EOF Then rec.AddNew

rec!DateEdited = Date
rec!txtTimeEdited = Time
rec!txtTextEdited = strFieldName
rec!txtUser = wks.Users(CurrentUser).Name
rec.Update
End Sub

..some of my declarations & syntax may be wrong. If you need more precise coding, please let me know. Otherwise, I just wanted to get some ideas rolling.

Hope this offers some help, Good Luck!

 

[eHigh]

Thanks.

I thought I would have to force users to go through a form, but wasn't sure.

 

[Zion7]

eHigh, I'm really sorry, I may not have read your thread carefully enough. My code is predicated on the fact, that your using the table behind a form, my mistake.
This may not be feasible without a form. I really don't know? Sorry again.