By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,121 Members | 1,723 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,121 IT Pros & Developers. It's quick & easy.

Log(AUDIT TRAIL) User from source Table

P: 9
im doing a database for Hospital Admission, I have a log in form which prompt user for a password. The source of log in is to look for the values in my Table tblEmployees and match user name and password entered.My case now is I have Audit Trail Module which keeps records of modifications and current user, I wanted my audit trail to log the current user based on who has log from based on my Log in Form.please help ty

HERES THE CODE FOR ON CLICK EVENT I MADE ON A BUTTON at a login form

Option Compare Database
Private intLogonAttempts As Integer

Private Sub Form_Open(Cancel As Integer)
'On open set focus to combo box
Me.cboEmployee.SetFocus
End Sub

Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
txtPassword.SetFocus
End Sub

Private Sub cmdLogin_Click()

'Check to see if data is entered into the UserName combo box

If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.cboEmployee.SetFocus
Exit Sub
End If

'Check to see if data is entered into the password box

If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.txtPassword.SetFocus
Exit Sub
End If

'Check value of password in tblEmployees to see if this matches value chosen in combo box

If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "Switchboard"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.SetFocus
End If

'If User Enters incorrect password 3 times database will shutdown

intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database. Please contact your system administrator.", vbCritical, "Restricted Access!"
Application.Quit
End If

End Sub

Private Sub close_Click()
On Error GoTo Err_close_Click


DoCmd.close

Exit_close_Click:
Exit Sub

Err_close_Click:
MsgBox Err.Description
Resume Exit_close_Click

End Sub

Private Sub txtPassword_AfterUpdate()
'After selecting user name set focus to password field
cmdLogin.SetFocus
End Sub


Private Sub Exit_Click()
On Error GoTo Err_Exit_Click


DoCmd.Quit

Exit_Exit_Click:
Exit Sub

Err_Exit_Click:
MsgBox Err.Description
Resume Exit_Exit_Click

End Sub


AND HERES THE CODE FOR AUDIT TRAIL MODULE

Option Compare Database
Option Explicit

Public Function AuditTrail()
On Error GoTo Err_Audit_Trail

'ACC2000: How to Create an Audit Trail of Record Changes in a Form
'http://support.microsoft.com/default.aspx?scid=kb;en-us;197592

Dim MyForm As Form
Dim ctl As Control
Dim sUser As String
Set MyForm = Screen.ActiveForm
' sUser = "User: " & UsersID 'You need to identify your users if you are not using Access security with workgroups.
sUser = CurrentUser

'If new record, record it in audit trail and exit function.
If MyForm.NewRecord = True Then
MyForm!AuditTrail = MyForm!tbAuditTrail & "New Record added on " & Now & " by " & sUser & ";"
Exit Function
End If

'Set date and current user if the form (current record) has been modified.
MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & vbLf & "Changes made on " & Now & " by " & sUser & ";"

'Check each data entry control for change and record old value of the control.
For Each ctl In MyForm.Controls

'Only check data entry type controls.
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
If ctl.Name = "tbAuditTrail" Then GoTo TryNextControl 'Skip AuditTrail field.
'If new and old value do not equal
If ctl.Value <> ctl.OldValue Then
MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Changed From: " & ctl.OldValue & ", To: " & ctl.Value
'If old value is Null and new value is not Null
ElseIf IsNull(ctl.OldValue) And Len(ctl.Value) > 0 Or ctl.OldValue = "" And Len(ctl.Value) > 0 Then
MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Was Previoulsy Null, New Value: " & ctl.Value
'If new value is Null and old value is not Null
ElseIf IsNull(ctl.Value) And Len(ctl.OldValue) > 0 Or ctl.Value = "" And Len(ctl.OldValue) > 0 Then
MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Changed From: " & ctl.OldValue & ", To: Null"
End If
End Select

TryNextControl:
Next ctl

Exit_Audit_Trail:
Exit Function

Err_Audit_Trail:
If Err.Number = 64535 Then 'Operation is not supported for this type of object.
Exit Function
ElseIf Err.Number = 2475 Then 'You entered an expression that requires a form to be the active window
Beep
MsgBox "A form is required to be the active window!", vbCritical, "Invalid Active Window"
Else
Beep
MsgBox Err.Number & " - " & Err.Description
End If
Resume Exit_Audit_Trail

End Function


PLEASE HELP
Nov 3 '06 #1
Share this Question
Share on Google+
3 Replies


PEB
Expert 100+
P: 1,418
PEB
In which table do you want to save the information of the log?

What is it structure?

:)
Nov 4 '06 #2

P: 9
i have a field Updates(memo type) on every table,when i craete the form i call on the Audit trail on before update event to record changes on the form... for example i have a table for employee, and create form for employee, everytime i edit and create the form logs are created in the update field. i already have but the log records the user of the worksation not the source table.please help
Nov 6 '06 #3

P: 51
i guess we hav the same problem man
Apr 23 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.