Log User IDs

[cunnie76]

I want to do 3 things - Can anyone help asap?

1) I am developing a risk database and I want to track when changes are made to any data field. My initial thoughts were to create an audit form which users would have to fill in before they can exist. - but how do you (A) ensure the audit form only appears when data is changed?, and (B) is filled in? - Is this a good way?

2) Can I somehow log user's ID's when they make changes to certain field? and then store this on a form & report?

3) When a user opens a form through a switchboard - I want to give them the option to either open as read-only or open for edit. i.e. If I open a form, I get a pop-up box which says "Do you want to read or edit?" with two option command buttons - How do I do this?

 

[ColinEssex]

Hi

1) You can pick up the userName by using this code.

Function fosusername2()
Dim lngLen As Long, lngX As Long
Dim strUserName As String, db As Database, r As Recordset
Set db = CurrentDb


strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
fosusername2 = Left$(strUserName, lngLen - 1)
Else
fosusername2 = ""
End If
msgbox fosusername2 (delete this MsgBox when its working ok)
End Function

Put this bit under the "Option Compare Database" heading

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

2) You can link this in with 1) by creating an audit table with whatever fields you like and post the data to it

3) If you lock the fields then run some code if the users try to change data which will post the old data to the audit table created at 2) with the username and date time etc etc. Then you can unlock the field with code to allow the changes then lock it again when they exit the field. You shouldn't need to have the form you described pop up because the users can't change anything without it being logged to the audit table.

Col

 

[cunnie76]

Thanks Colin for that.

I tried the User ID and it works a treat - the only thing is that it brings up my (as an example) NT Login ID rather than my user name - Is there any code that will pick up my name rather than my ID?

Also - Can you explain/ send me info on how to do Step 3.

Bob