Capturing Who Amended the data

[khwaja]

One thing that noticed that I could not even get the sample db work even after the changes you suggested. You managed to see that work. So does this mean that there is something fundamentally wrong regardless of the db I am using? May be the right references?

 

[GinaWhipp]

No problem, here to help...

I did not touch the References, didn't even peek at them. Just changed it to handle TEXT. Hmm, maybe I did more, I sleep walk thru changes. So, I'll send it back so you can have a look.

 

[khwaja]

Thanks. I noticed that I was supposed to be showing Project ID or any other field (I tried a text field too) in the following line in addition to fields I am testing against. That was the only difference between what you have and I have. But it does not work. I don't know if triggering this from a sub form stops it from writing to the table.

Call LogChange(Me.DevCode, " Construction form ", strField & " changed from " & strold & " to " & strnew)

Maybe the following public function is not working in some ways?

Function LogChange(lngField As String, strForm As String, strNotes As String)
'From http://www.access-diva.com/

Dim strSQL As String

strSQL = "INSERT INTO tblLog ( lID, lLogDate, lUserID, lNotes, lSystemForm )" & _
" SELECT '" & lngField & "' AS ID, Now(), GetUserID(), '" & strNotes & "', '" & strForm & "'"
CurrentDb.Execute strSQL, dbFailOnError


End Function

 

[GinaWhipp]

Wait, I just thought of something... Are you using the Date Picker? Try typing it in and see what happens.

 

[khwaja]

Thanks. I am not using date picker. But having no other recourse, I have created a test version of the db. Took me ages to strip it down for data. Hopefully, you could see the issue I have. In the main form, frmProjectsConstSM, I am trying to capture changes to Completion Date filed (buildercomp).

Cheers

 

[GinaWhipp]

Fixed...

First I moved the Dim Statements to the top (no need to keep typing when once at the top will do) and then since DevCode is TEXT I had to adjust the line to what I posted earlier.

 

[khwaja]

Many thanks for your help. This is all very good. Works.

Kind regards

 

[GinaWhipp]

You're welcome, glad we finally got it working!