I am new to working with VBA, so it is possible that I am going about this a harder way than is necessary.
Scenario: I am working in a product specification database and I've setup an audit trail to record any changes made through a form to my table. I've also setup a user table with a form that requires users to login to the database using a user name and password.
Objective: The code has a few bugs I'm working out one at a time. The big one I'm working on right now is changing the source of the audit trail code that records "username" to refer to the user name used to login to access. Currently it is using the user name used to login to windows, which isn't ideal because not all users have unique windows logins (not something I can change unfortunately).
This is the code I believe I need to change:
Code:
& cDQ & Environ("username") & cDQ & ", " _
I havent been able to find a good way to change it to refer to the username used to login to access though. If anyone has an answer or any resources that I could refer to to learn more about this process it would be greatly appreciated. Thank you!
Also here is the code in its entirety incase that is helpful, Thanks again!
Code:
Const cDQ As String = """"
Sub AuditTrail(frm As Form, recordid As Long) 'Track changes to data. 'recordid identifies the pk field's corresponding 'control in frm, in order to id record.
Dim ctl As Control
Dim varBefore As Variant
Dim varAfter As Variant
Dim strControlName As String
Dim strSQL As String
On Error GoTo ErrHandler 'Get changed values.
For Each ctl In frm.Controls
With ctl 'Avoid labels and other controls with Value property.
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox, acOptionButton, acToggleButton
If .Value <> .OldValue Then
varBefore = .OldValue
varAfter = .Value
strControlName = .Name 'Build INSERT INTO statement.
strSQL = "INSERT INTO " _
& "tblUpdateAudit (EditDate, User, RecordID, SourceTable, " _
& " SourceField, BeforeValue, AfterValue) " _
& "VALUES (Now()," _
& cDQ & Environ("username") & cDQ & ", " _
& recordid & ", " _
& cDQ & frm.RecordSource & cDQ & ", " _
& cDQ & .Name & cDQ & ", " _
& cDQ & varBefore & cDQ & ", " _
& cDQ & varAfter & cDQ & ")" 'View evaluated statement in Immediate window.
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
End If
End Select
End With
Next
Set ctl = Nothing
Exit Sub
ErrHandler: MsgBox Err.Description & vbNewLine _
& Err.Number, vbOKOnly, "Error"
End Sub