Capturing Who Amended the data (1 Viewer)

[khwaja]

I am sure I am not asking a question that has not been asked before. I have a form and i can capture who edited the form using log in ID. But now the issue that is coming my way is that certain date field are more critical and we need to know who amended those dates and whether it is possible to find out what was the pre existing date?

Is there an option to achieve this, let us say, for one date field?

 

[plog]

Yes, with a new table for that field. You now have a one to many relationship with it.

Let's say you want to do that with Sales_Date in this table:

Sales
Sales_ID, Sales_Date, Sales_Client, Sales_Qty
12, 1/13/2015, ABC Co., 15
14, 2/1/2015, Acme Inc., 22

You would take Sales_Date out and put it in this table:

SalesDates
Sales_ID, Sales_Date, Edit_Date, Edit_User
12, 1/13/2015, 10/13/2015, Steve
14, 1/31/2015, 1/31/2015, Steve
14, 2/1/2015, 2/5/2015, David

 

[khwaja]

Thanks great. How do automatically I create a record on the many side when user is done amending the date. Would that be after update event or something?

 

[burrina]

You will need a log table to store the preexisting data.
Example:

Me.ControlName = Me.ControlName.OldValue

Too Slow.

 

[GinaWhipp]

Hmm, this might help...
http://www.access-diva.com/vba10.html

 

[khwaja]

Thank you once again for the tip. It is good code but I somehow can't get this to work. I have on enter and on exit as well as Record Change code used it in a sub form making sure that I change field names. I added two public functions to a module. When I make a change to the date, nothing really happens. No error either.

 

[GinaWhipp]

Have you changed the field names to match what you have in your database? Have you created the Table needed so the data has somewhere to go?

 

[khwaja]

Yes I have created a table and changed field name in the code everywhere.

 

[GinaWhipp]

And you added the lines to the On_Enter and On_Exit event of the appropriate fields?

 

[khwaja]

Yes, I have. There is just one date field I am trying to track changes for.

The only change I made was to add declarations as these were missing and I was getting error.

 

[khwaja]

Private Sub BuilderComp_Enter()
Dim strold As String

strold = Nz(Me.BuilderComp, "")
End Sub

Private Sub BuilderComp_Exit(Cancel As Integer)
Dim strnew As String
strnew = Nz(Me.BuilderComp, "")
Call RecordChange(" BuilderComp ")
End Sub

 

[GinaWhipp]

Well, those look fine so let's see the rest of it. And please use Code Tags, makes it easier to read.

 

[khwaja]

Just to help you, I have created a sample db and added the code and table. But it does not quite work in this. I think I might be making a silly mistake. I am using the Parts form to test it.

 

[GinaWhipp]

Looking now...

 

[GinaWhipp]

Okay, I see the issue, your PartNo is TEXT and the lID field is NUMERIC. So, use the Function below in place of the one you have in mod1

Function LogChange(lngField As String, strForm As String, strNotes As String)
'From [URL]http://www.access-diva.com/[/URL]
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

 

[khwaja]

Thanks. In the actual DB which is fairly large to share, I am using ProjectID which is auto number and even then I had the issue. I will try to change this anyway to see if it works.

 

[GinaWhipp]

Hmm, then perhaps you should have sent a stripped down one of that one because all I changed was that and it was fine. Might be something else with the actual one.

 

[khwaja]

Thanks. I have a normal one to many relationship where I have a parent form and the sub form. The field in question is in sub form. I do have other validation events at form level. I am sure they are not likely to affect what is being done on the exit and enter events. If there was an error message that would have helped. When I make a change to the field, I hardy notice any lag or pause when a record might be writing. Not too sure what to do except to create a sample for you.

Cheers

 

[GinaWhipp]

Not noticing any lag time is not an issue since there isn't any. Did you check the Table? Are there any entries? If not then without a sample not sure what else I can do.

 

[khwaja]

Thank you once again. There are no records created. I will see if I could create a stand alone copy and send you. Meanwhile, please accept my gratitude for persevering with me.