Hi,
My knowledge in Access is very limited but I have managed to build a pretty good-wroking database for my department based on help and assistance of experts from forums like this one.
A bit of background: This database will be used on a common computer, shared amongst the team members. Hence, I've created a login screen for users to login before using the database. Below is the code from my login form: -
-
Option Compare Database
-
Public strUserName As String
-
-
Private Sub Form_Load()
-
Me.txtUsername.SetFocus
-
End Sub
-
-
Private Sub txtUsername_AfterUpdate()
-
'After selecting user name set focus to password field
-
Me.txtPassword.SetFocus
-
End Sub
-
-
Private Sub cmdLogin_Click()
-
-
Dim rs As Recordset
-
Dim strSQL As String
-
Dim strPassword As String
-
Dim strUserName As String
-
-
On Error Resume Next
-
-
strUserName = txtUsername.Value
-
-
strPassword = txtPassword.Value
-
-
Set db = CurrentDb
-
strSQL = "SELECT password FROM tblUser WHERE username='" & strUserName & "'"
-
Set rs = db.OpenRecordset(strSQL)
-
If rs.RecordCount > 0 Then
-
If rs.Fields(0) <> strPassword Then
-
MsgBox "Invalid username/password. Please try again", vbOKOnly, "Invalid Login!"
-
Else
-
Me.Visible = False
-
DoCmd.OpenForm "HOMEPAGE", acNormal, , , , acDialog
-
Me.txtUsername.Value = ""
-
Me.txtPassword.Value = ""
-
End If
-
Else
-
MsgBox "Invalid username/password. Please try again", vbOKCancel, "Invalid Login!"
-
txtUsername.SetFocus
-
End If
-
-
End Sub
-
At the same time, I would like to create an audit trail for any changes/updates done on the database. I have used the Allen Browne's audit trail sample code and changed it to fit mine - which worked well; except, it is returning the Windows login code of the Computer itself, not the user login. Here's the code for my audit trail module: -
Option Compare Database
-
Option Explicit
-
-
Private Const conMod As String = "ajbAudit"
-
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
-
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
-
-
-
Function NetworkUserName() As String
-
On Error GoTo Err_Handler
-
'Purpose: Returns the network login name
-
Dim lngLen As Long
-
Dim lngX As Long
-
Dim strUserName As String
-
-
NetworkUserName = "Unknown"
-
-
strUserName = String$(254, 0)
-
lngLen = 255&
-
lngX = apiGetUserName(strUserName, lngLen)
-
If (lngX > 0&) Then
-
NetworkUserName = Left$(strUserName, lngLen - 1&)
-
End If
-
-
Exit_Handler:
-
Exit Function
-
-
Err_Handler:
-
-
Resume Exit_Handler
-
End Function
-
-
-
Function AuditDelBegin(sTable As String, sAudTmpTable As String, sKeyField As String, lngKeyValue As Long) As Boolean
-
On Error GoTo Err_AuditDelBegin
-
'Purpose: Write a copy of the record to a tmp audit table.
-
' Copy to be written to real audit table in AfterDelConfirm.
-
'Arguments: sTable = name of table to be audited.
-
' sAudTmpTable = the name of the temp audit table.
-
' sKeyField = name of AutoNumber field in table.
-
' lngKeyValue = number in the AutoNumber field.
-
'Return: True if successful.
-
'Usage: Call from a form's Delete event. Example:
-
' Call AuditDelBegin("tblInvoice", "audTmpInvoice", "InvoiceID", Me.InvoiceID)
-
'Note: Must also call AuditDelEnd in the form's AfterDelConfirm event.
-
Dim db As DAO.Database ' Current database
-
Dim sSQL As String ' Append query.
-
-
' Append record to the temp audit table.
-
Set db = DBEngine(0)(0)
-
sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
-
"SELECT 'Delete' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
-
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
-
db.Execute sSQL, dbFailOnError
-
-
Exit_AuditDelBegin:
-
Set db = Nothing
-
Exit Function
-
-
Err_AuditDelBegin:
-
-
Resume Exit_AuditDelBegin
-
End Function
-
-
-
Function AuditDelEnd(sAudTmpTable As String, sAudTable As String, Status As Integer) As Boolean
-
On Error GoTo Err_AuditDelEnd
-
'Purpose: If the deletion was completed, copy the data from the
-
' temp table to the autit table. Empty temp table.
-
'Arguments: sAudTmpTable = name of temp audit table
-
' sAudTable = name of audit table
-
' Status = Status from the form's AfterDelConfirm event.
-
'Return: True if successful.
-
'Usage: Call from form's AfterDelConfirm event. Example:
-
' Call AuditDelEnd("audTmpInvoice", "audInvoice", Status)
-
Dim db As DAO.Database ' Currrent database
-
Dim sSQL As String ' Append query.
-
-
' If the Delete proceeded, copy the record(s) from temp table to delete table.
-
' Note: Only "Delete" types are copied: cancelled Edits may be there as well.
-
Set db = DBEngine(0)(0)
-
If Status = acDeleteOK Then
-
sSQL = "INSERT INTO " & sAudTable & " SELECT " & sAudTmpTable & ".* FROM " & sAudTmpTable & _
-
" WHERE (" & sAudTmpTable & ".audType = 'Delete');"
-
db.Execute sSQL, dbFailOnError
-
End If
-
-
'Remove the temp record(s).
-
sSQL = "DELETE FROM " & sAudTmpTable & ";"
-
db.Execute sSQL, dbFailOnError
-
AuditDelEnd = True
-
-
Exit_AuditDelEnd:
-
Set db = Nothing
-
Exit Function
-
-
Err_AuditDelEnd:
-
-
Resume Exit_AuditDelEnd
-
End Function
-
-
-
Function AuditEditBegin(sTable As String, sAudTmpTable As String, sKeyField As String, _
-
lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean
-
On Error GoTo Err_AuditEditBegin
-
'Purpose: Write a copy of the old values to temp table.
-
' It is then copied to the true audit table in AuditEditEnd.
-
'Arugments: sTable = name of table being audited.
-
' sAudTmpTable = name of the temp audit table.
-
' sKeyField = name of the AutoNumber field.
-
' lngKeyValue = Value of the AutoNumber field.
-
' bWasNewRecord = True if this was a new insert.
-
'Return: True if successful
-
'Usage: Called in form's BeforeUpdate event. Example:
-
' bWasNewRecord = Me.NewRecord
-
' Call AuditEditBegin("tblInvoice", "audTmpInvoice", "InvoiceID", Me.InvoiceID, bWasNewRecord)
-
Dim db As DAO.Database ' Current database
-
Dim sSQL As String
-
-
'Remove any cancelled update still in the tmp table.
-
Set db = DBEngine(0)(0)
-
sSQL = "DELETE FROM " & sAudTmpTable & ";"
-
db.Execute sSQL
-
-
' If this was not a new record, save the old values.
-
If Not bWasNewRecord Then
-
sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
-
"SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
-
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
-
db.Execute sSQL, dbFailOnError
-
End If
-
AuditEditBegin = True
-
-
Exit_AuditEditBegin:
-
Set db = Nothing
-
Exit Function
-
-
Err_AuditEditBegin:
-
-
Resume Exit_AuditEditBegin
-
End Function
-
-
-
Function AuditEditEnd(sTable As String, sAudTmpTable As String, sAudTable As String, _
-
sKeyField As String, lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean
-
On Error GoTo Err_AuditEditEnd
-
'Purpose: Write the audit trail to the audit table.
-
'Arguments: sTable = name of table being audited.
-
' sAudTmpTable = name of the temp audit table.
-
' sAudTable = name of the audit table.
-
' sKeyField = name of the AutoNumber field.
-
' lngKeyValue = Value of the AutoNumber field.
-
' bWasNewRecord = True if this was a new insert.
-
'Return: True if successful
-
'Usage: Called in form's AfterUpdate event. Example:
-
' Call AuditEditEnd("tblInvoice", "audTmpInvoice", "audInvoice", "InvoiceID", Me.InvoiceID, bWasNewRecord)
-
Dim db As DAO.Database
-
Dim sSQL As String
-
Set db = DBEngine(0)(0)
-
-
If bWasNewRecord Then
-
' Copy the new values as "Insert".
-
sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _
-
"SELECT 'Insert' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
-
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
-
db.Execute sSQL, dbFailOnError
-
Else
-
' Copy the latest edit from temp table as "EditFrom".
-
sSQL = "INSERT INTO " & sAudTable & " SELECT TOP 1 " & sAudTmpTable & ".* FROM " & sAudTmpTable & _
-
" WHERE (" & sAudTmpTable & ".audType = 'EditFrom') ORDER BY " & sAudTmpTable & ".audDate DESC;"
-
db.Execute sSQL
-
' Copy the new values as "EditTo"
-
sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _
-
"SELECT 'EditTo' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
-
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
-
db.Execute sSQL
-
' Empty the temp table.
-
sSQL = "DELETE FROM " & sAudTmpTable & ";"
-
db.Execute sSQL, dbFailOnError
-
End If
-
AuditEditEnd = True
-
-
Exit_AuditEditEnd:
-
Set db = Nothing
-
Exit Function
-
-
Err_AuditEditEnd:
-
-
Resume Exit_AuditEditEnd
-
End Function
-
So my question is:
How do I tweak the codes above so that my audit trail will display the user login name (entered upon entering the database) instead of the Computer's windows login?
Your assistance is highly appreciated. Thanks!
Lets try this:
In the AuditDelBegin() function:
There is a line (#23?) in Neopa/AB's code that currently reads: sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
"SELECT 'Delete' AS Expr1, Now() AS Expr2, TempVars!UserName AS Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
Change this to read: sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
"SELECT 'Delete' AS Expr1, Now() AS Expr2, '" & TempVars!UserName & "', " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");" NOTE THE HIGHLIGHTED SECTION!
The spaceing here is VERY importaint - missing a space will result in an error.
Also note the SINGLE QUOTE marks around the TempVars!UserName just inside the double quotes on each side.
If You used "TestUserName" as the value in TempVars!UserName then debug.print sSQL the resolved string will be: INSERT INTO audtmptblpuke1 ( audType, audDate, audUser ) SELECT 'Delete' AS Expr1, Now() AS Expr2, 'TestUserName', tblpuke1.* FROM tblpuke1 WHERE (tblpuke1.id = 1);
(ok, tblpuke1... don't ask... just say I have 4 kids, three under 5 and it was a very long and very sick day when I made this database to play with (@_@) )
This should now work... it worked in my test database.
You will need to make the same changes to the remaining functions.
BOL
-Z
[edit] This is the same code as in the section above, I've just made it easier to cut and paste and added the debug.print - sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
-
"SELECT 'Delete' AS Expr1, Now() AS Expr2, '" & TempVars!UserName & "', " & sTable & ".* " & _
-
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
-
'
-
Debug.Print sSQL ''<<< remove this line after troubleshooting is done!
-
'
20 2668 NeoPa 32,556
Expert Mod 16PB
A properly formatted question from a new user. How refreshing.
I'd start by storing the name of the validated user in a session-level variable. Recent versions of Access have these, and they persist beyond the point at which the project crashes or stops.
From there, it's a simple case of changing any references in your logging procedures to reference that variable instead of using NetworkUserName().
Let us know if you need more help implementing this. I'm rushing at the moment, but will have more time when next I look.
Welcome to Bytes :-)
Hi NeoPa,
Thanks for the warm welcome. I've read through dozens of threads in Bytes and somehow learned how's best to approach the forum :)
Anyways, you guessed right. I do need further help on this. I dont quite understand by what you meant with "session-level variable".
My guess is I would have to change Line 4-31 accordingly from the audit trail module; but I have no idea how to do this.
I'll be patient and wait till you have some time to reply.
Thanks!
zmbd 5,501
Expert Mod 4TB
I think that Neopa is refering to the newer "temporary variable" collection that is available in ACC2007/2010.
This link has a VERY basic explanation and example of useage: Power Tip: Maximize the use of TempVars in Access 2007 and 2010
The cool thing here is that you can reference these from the stored queries without a function call!
Neopa will be along shortly to correct me if I'm off...
If you need still more help let us know.
NeoPa 32,556
Expert Mod 16PB
Nothing to correct (of course). That's what I was talking about.
I use another approach for 2003 and earlier, but that's more complicated and unnecessary if you have TempVars available ;-)
Hi all,
I've read through the article suggested by zmbd - as much as I am excited at how useful it can be, I'm beyond clueless at this point. To be frank, I have no training in Access at all- just learned it briefly during my uni days and the rest are through the web!
Appreciate if you can provide a detailed guide/sample on how I can write the code to fulfil my requirements - to capture the current user name instead of the Windows login name.
Thanks guys
zmbd 5,501
Expert Mod 4TB
Bellina:
Using the method shown in the article to set a memeber of the tempvars collection. When the user enters the information in your login/on form, after validating the user's entries, store the user name in the tempvars. TempVars.Add Name:="LoggedInUser", Value:=strTheUsersNameFromTheForm
Here I've explicitly set the agruments by name and the "strTheUsersNameFromTheForm" would be from the form's control
Now whenever you need the user name you refer to it using one of the three methods shown in the article keeping in mind that all three though valid within VBA (Macro uses a slightly different method) in stored queries only the TempVars!LoggedInUser would be used. Also if you set three of these and want to use the TempVars.Item(0) format, the "0" refers to the first one you set and "2" would be the third (think arrays or listbox referencing)
As we're getting ready for the big family feed I've not a lot if time this weekend.
NeoPa 32,556
Expert Mod 16PB
Your original checking code could be altered to : - Option Compare Database
-
Public strUserName As String
-
-
Private Sub Form_Load()
-
Me.txtUsername.SetFocus
-
End Sub
-
-
Private Sub txtUsername_AfterUpdate()
-
'After selecting user name set focus to password field
-
Me.txtPassword.SetFocus
-
End Sub
-
-
Private Sub cmdLogin_Click()
-
Dim rs As Recordset
-
Dim strSQL As String
-
-
On Error Resume Next
-
With Me
-
Set db = CurrentDb()
-
strSQL = "SELECT [password] FROM [tblUser] WHERE [username]='" & strUserName & "'"
-
Set rs = db.OpenRecordset(strSQL)
-
If rs.RecordCount > 0 Then
-
If rs.Fields(0) <> .txtUserName Then
-
MsgBox "Invalid username/password. Please try again", vbOKOnly, "Invalid Login!"
-
Else
-
.Visible = False
-
Call TempVars.Add(Name:="UserName", Value:=CStr(.txtUserName))
-
.txtUsername = Null
-
.txtPassword = Null
-
DoCmd.OpenForm "HOMEPAGE", acNormal, , , , acDialog
-
End If
-
Else
-
MsgBox "Invalid username/password. Please try again", vbOKCancel, "Invalid Login!"
-
txtUsername.SetFocus
-
End If
-
End With
-
End Sub
Then, when you want to use it (in a way similar to your earlier code) use : -
Option Compare Database
-
Option Explicit
-
-
Private Const conMod As String = "ajbAudit"
-
-
Function AuditDelBegin(sTable As String, sAudTmpTable As String, sKeyField As String, lngKeyValue As Long) As Boolean
-
On Error GoTo Err_AuditDelBegin
-
'Purpose: Write a copy of the record to a tmp audit table.
-
' Copy to be written to real audit table in AfterDelConfirm.
-
'Arguments: sTable = name of table to be audited.
-
' sAudTmpTable = the name of the temp audit table.
-
' sKeyField = name of AutoNumber field in table.
-
' lngKeyValue = number in the AutoNumber field.
-
'Return: True if successful.
-
'Usage: Call from a form's Delete event. Example:
-
' Call AuditDelBegin("tblInvoice", "audTmpInvoice", "InvoiceID", Me.InvoiceID)
-
'Note: Must also call AuditDelEnd in the form's AfterDelConfirm event.
-
Dim db As DAO.Database ' Current database
-
Dim sSQL As String ' Append query.
-
-
' Append record to the temp audit table.
-
Set db = DBEngine(0)(0)
-
sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
-
"SELECT 'Delete' AS Expr1, Now() AS Expr2, TempVars!UserName AS Expr3, " & sTable & ".* " & _
-
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
-
db.Execute sSQL, dbFailOnError
-
-
Exit_AuditDelBegin:
-
Set db = Nothing
-
Exit Function
-
-
Err_AuditDelBegin:
-
Resume Exit_AuditDelBegin
-
End Function
-
-
Function AuditDelEnd(sAudTmpTable As String, sAudTable As String, Status As Integer) As Boolean
-
On Error GoTo Err_AuditDelEnd
-
'Purpose: If the deletion was completed, copy the data from the
-
' temp table to the autit table. Empty temp table.
-
'Arguments: sAudTmpTable = name of temp audit table
-
' sAudTable = name of audit table
-
' Status = Status from the form's AfterDelConfirm event.
-
'Return: True if successful.
-
'Usage: Call from form's AfterDelConfirm event. Example:
-
' Call AuditDelEnd("audTmpInvoice", "audInvoice", Status)
-
Dim db As DAO.Database ' Currrent database
-
Dim sSQL As String ' Append query.
-
-
' If the Delete proceeded, copy the record(s) from temp table to delete table.
-
' Note: Only "Delete" types are copied: cancelled Edits may be there as well.
-
Set db = DBEngine(0)(0)
-
If Status = acDeleteOK Then
-
sSQL = "INSERT INTO " & sAudTable & " SELECT " & sAudTmpTable & ".* FROM " & sAudTmpTable & _
-
" WHERE (" & sAudTmpTable & ".audType = 'Delete');"
-
db.Execute sSQL, dbFailOnError
-
End If
-
-
'Remove the temp record(s).
-
sSQL = "DELETE FROM " & sAudTmpTable & ";"
-
db.Execute sSQL, dbFailOnError
-
AuditDelEnd = True
-
-
Exit_AuditDelEnd:
-
Set db = Nothing
-
Exit Function
-
-
Err_AuditDelEnd:
-
Resume Exit_AuditDelEnd
-
End Function
-
-
Function AuditEditBegin(sTable As String, sAudTmpTable As String, sKeyField As String, _
-
lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean
-
On Error GoTo Err_AuditEditBegin
-
'Purpose: Write a copy of the old values to temp table.
-
' It is then copied to the true audit table in AuditEditEnd.
-
'Arugments: sTable = name of table being audited.
-
' sAudTmpTable = name of the temp audit table.
-
' sKeyField = name of the AutoNumber field.
-
' lngKeyValue = Value of the AutoNumber field.
-
' bWasNewRecord = True if this was a new insert.
-
'Return: True if successful
-
'Usage: Called in form's BeforeUpdate event. Example:
-
' bWasNewRecord = Me.NewRecord
-
' Call AuditEditBegin("tblInvoice", "audTmpInvoice", "InvoiceID", Me.InvoiceID, bWasNewRecord)
-
Dim db As DAO.Database ' Current database
-
Dim sSQL As String
-
-
'Remove any cancelled update still in the tmp table.
-
Set db = DBEngine(0)(0)
-
sSQL = "DELETE FROM " & sAudTmpTable & ";"
-
db.Execute sSQL
-
-
' If this was not a new record, save the old values.
-
If Not bWasNewRecord Then
-
sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
-
"SELECT 'EditFrom' AS Expr1, Now() AS Expr2, TempVars!UserName AS Expr3, " & sTable & ".* " & _
-
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
-
db.Execute sSQL, dbFailOnError
-
End If
-
AuditEditBegin = True
-
-
Exit_AuditEditBegin:
-
Set db = Nothing
-
Exit Function
-
-
Err_AuditEditBegin:
-
Resume Exit_AuditEditBegin
-
End Function
-
-
Function AuditEditEnd(sTable As String, sAudTmpTable As String, sAudTable As String, _
-
sKeyField As String, lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean
-
On Error GoTo Err_AuditEditEnd
-
'Purpose: Write the audit trail to the audit table.
-
'Arguments: sTable = name of table being audited.
-
' sAudTmpTable = name of the temp audit table.
-
' sAudTable = name of the audit table.
-
' sKeyField = name of the AutoNumber field.
-
' lngKeyValue = Value of the AutoNumber field.
-
' bWasNewRecord = True if this was a new insert.
-
'Return: True if successful
-
'Usage: Called in form's AfterUpdate event. Example:
-
' Call AuditEditEnd("tblInvoice", "audTmpInvoice", "audInvoice", "InvoiceID", Me.InvoiceID, bWasNewRecord)
-
Dim db As DAO.Database
-
Dim sSQL As String
-
Set db = DBEngine(0)(0)
-
-
If bWasNewRecord Then
-
' Copy the new values as "Insert".
-
sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _
-
"SELECT 'Insert' AS Expr1, Now() AS Expr2, TempVars!UserName AS Expr3, " & sTable & ".* " & _
-
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
-
db.Execute sSQL, dbFailOnError
-
Else
-
' Copy the latest edit from temp table as "EditFrom".
-
sSQL = "INSERT INTO " & sAudTable & " SELECT TOP 1 " & sAudTmpTable & ".* FROM " & sAudTmpTable & _
-
" WHERE (" & sAudTmpTable & ".audType = 'EditFrom') ORDER BY " & sAudTmpTable & ".audDate DESC;"
-
db.Execute sSQL
-
' Copy the new values as "EditTo"
-
sSQL = "INSERT INTO " & sAudTable & " ( audType, audDate, audUser ) " & _
-
"SELECT 'EditTo' AS Expr1, Now() AS Expr2, TempVars!UserName AS Expr3, " & sTable & ".* " & _
-
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
-
db.Execute sSQL
-
' Empty the temp table.
-
sSQL = "DELETE FROM " & sAudTmpTable & ";"
-
db.Execute sSQL, dbFailOnError
-
End If
-
AuditEditEnd = True
-
-
Exit_AuditEditEnd:
-
Set db = Nothing
-
Exit Function
-
-
Err_AuditEditEnd:
-
Resume Exit_AuditEditEnd
-
End Function
We are no longer using the procedure NetworkUserName(), so that can be excluded from your code.
Thank you zmbd & NeoPa!
I've done the changes as you have suggested. However, the audit trail did not work. I then tried including ' to quote the TempVars in the audit module for all the different functions as such: - ..... 'TempVars!UserName' AS Expr3....
The audit trail worked, BUT, it recorded "TempVars!UserName" in the audit table as the current user.
Somehow, the tempvars is not capturing the user login from the login form. Any reason why?
Btw, happy thanksgiving guys. For me, I'm thankful for the hope in humanity shown through the helpful people around the world like you guys :)
zmbd 5,501
Expert Mod 4TB
Do not Quote the Tempvars.
They should be "outside" of the quotes. "some text here and then " & tempvars!username
etc...
NeoPa 32,556
Expert Mod 16PB
Which version of Access are you using?
zmbd, did that but then the audit trail did not work. :(
NeoPa, I'm using Access2013. Sorry, I should probably have mentioned this much earlier.
zmbd 5,501
Expert Mod 4TB
Let's go back to your original code in post#1 and try something very simple and see if we can get the simpler code to work:
Change Line#23 from the original: strUserName = txtUsername.Value
so that it now reads: tempvars.add "strUserName", txtUsername.Value
Now to use the stored user name in the remaining code let's take a look at line#28, which is currently: strSQL = "SELECT password FROM tblUser WHERE username='" & strUserName & "'"
So change it to this: strSQL = "SELECT password FROM tblUser WHERE username='" & tempvars.item(0) & "'"
note that here I'm using the numerical index... this is to avoid the issues with the multiple quotes that run into things like """"" and ""'"'"" which are a real treat to troubleshoot.
So if the user name was "zmbd" then if you run the code the strSQL would debug.print out to be: SELECT password FROM tblUser WHERE username='zmbd'
New here's the neat thing:
Run the code... if everything runs, no errors, then do the following:
<ctrl><g>
this will open the immediates window in the VBE
in this window type the following: ?tempvars!strUserName press return
it should return the last user name entered in the login form ?tempvars.item("strUserName") press return
it should return the last user name in the login form tempvars.removeall press return
Now both: ?tempvars!strUserName press return ?tempvars.item("strUserName") press return
should return a null
Once we can handle the more straight forward use, then we'll dive into the audit code.
NeoPa 32,556
Expert Mod 16PB
It should be fine as long as it's after version 2003. They were introduced in V2007.
I doubt the problem is with the TempVars. It's hard to say exactly what the problem is without direct access to it, or at least a clear explanation of exactly what is going wrong where.
Did you try the code as I posted it? Exactly what went wrong when you did? I'm afraid we need a more detailed answer than simply "the auditing".
PS. I missed Z's post earlier (Page loaded ready much earlier). You should follow his straightforward instructions to provide info that will be very helpful and illuminative.
Hi zmbd, NeoPa,
I followed zmbd easy step-by-step instructions and now, my code for the login form looks like this: -
Option Compare Database
-
Public strUserName As String
-
-
Private Sub Form_Load()
-
Me.txtUsername.SetFocus
-
End Sub
-
-
Private Sub txtUsername_AfterUpdate()
-
'After selecting user name set focus to password field
-
Me.txtPassword.SetFocus
-
End Sub
-
-
Private Sub cmdLogin_Click()
-
-
Dim rs As Recordset
-
Dim strSQL As String
-
Dim strPassword As String
-
Dim strUserName As String
-
-
On Error Resume Next
-
-
TempVars.Add "strUserName", txtUsername.Value
-
-
strPassword = txtPassword.Value
-
-
Set db = CurrentDb
-
strSQL = "SELECT password FROM tblUser WHERE username='" & TempVars.Item(0) & "'"
-
Set rs = db.OpenRecordset(strSQL)
-
If rs.RecordCount > 0 Then
-
If rs.Fields(0) <> strPassword Then
-
MsgBox "Invalid username/password. Please try again", vbOKOnly, "Invalid Login!"
-
Else
-
Me.Visible = False
-
Call TempVars.Add(Name:="UserName", Value:=strUserName)
-
DoCmd.OpenForm "HOMEPAGE", acNormal, , , , acNormal
-
-
End If
-
Else
-
MsgBox "Invalid username/password. Please try again", vbOKCancel, "Invalid Login!"
-
txtUsername.SetFocus
-
End If
-
-
Me.txtUsername = Null
-
Me.txtPassword = Null
-
-
End Sub
-
-
After doing the checking on the immediate window, it did show the correct username accordingly.
Now, on the audit module- I've copied & pasted the code from NeoPa's earlier thread. When I tried to amend the information in my database, the audit trail is not recorded in the audit table, which worked fine earlier using the NetworkUserName.
I wish I can attach my database here for you to have a look at it. But I noticed that .accdb is not an accepted attachment file format here.
zmbd 5,501
Expert Mod 4TB
If we get to the point where we need your database you will need to compress it. There are several free/shareware file compressors on the net and WindowsXP, Windows7 have native support so never fear will walk you thru it when the time comes.
I'm in the morning routine so once I have a few free moments we'll take a closer look at the other code.
zmbd 5,501
Expert Mod 4TB
When you were using the AllenBrowne code, in the event calling the functions, you are using something along these lines: - Call AuditDelBegin("tblInvoice", "audTmpInvoice", "InvoiceID", Nz(Me.InvoiceID,0))
?
zmbd 5,501
Expert Mod 4TB
Lets try this:
In the AuditDelBegin() function:
There is a line (#23?) in Neopa/AB's code that currently reads: sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
"SELECT 'Delete' AS Expr1, Now() AS Expr2, TempVars!UserName AS Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
Change this to read: sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
"SELECT 'Delete' AS Expr1, Now() AS Expr2, '" & TempVars!UserName & "', " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");" NOTE THE HIGHLIGHTED SECTION!
The spaceing here is VERY importaint - missing a space will result in an error.
Also note the SINGLE QUOTE marks around the TempVars!UserName just inside the double quotes on each side.
If You used "TestUserName" as the value in TempVars!UserName then debug.print sSQL the resolved string will be: INSERT INTO audtmptblpuke1 ( audType, audDate, audUser ) SELECT 'Delete' AS Expr1, Now() AS Expr2, 'TestUserName', tblpuke1.* FROM tblpuke1 WHERE (tblpuke1.id = 1);
(ok, tblpuke1... don't ask... just say I have 4 kids, three under 5 and it was a very long and very sick day when I made this database to play with (@_@) )
This should now work... it worked in my test database.
You will need to make the same changes to the remaining functions.
BOL
-Z
[edit] This is the same code as in the section above, I've just made it easier to cut and paste and added the debug.print - sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
-
"SELECT 'Delete' AS Expr1, Now() AS Expr2, '" & TempVars!UserName & "', " & sTable & ".* " & _
-
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
-
'
-
Debug.Print sSQL ''<<< remove this line after troubleshooting is done!
-
'
Hi zmbd,
"tablepuke"? I understand. I have 2 kids of my own too.
Anyways, AMAZING! It worked! Although, I did face some issues at first ("Editfrom" did not show in audTable etc). But managed to check everything in detail and repaired the code accordingly.
Thank you guys for an amazing help!
I haven't read through all posts, and there probably is a good reason you don't use the built-in CurrentUser variable in vba, or [CurrentUser] in queries. Maybe you are not using the Access security to login, but those who are, can probably use this variable instead? CurrentUser Function zmbd 5,501
Expert Mod 4TB
PPelle
Very simple reasons... it can be hacked and it only returns the currently logged in person to the database which in ACCDB file will always be "admin" as user level security is disabled - If you have ACC2007/2010, try it...open a new database, or any database using the new ACCDB extension, <ctrl><g> then
(^-^)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Zlatko Matić |
last post by:
Hello.
I tried to implement audit trail, by making an audit trail table with the
following fileds:
TableName,FieldName,OldValue,NewValue,UpdateDate,type,UserName.
Triggers on each table were...
|
by: alanknipmeyer |
last post by:
Dear Sirs,
Having previously used other SQL implementations I have grown used to
having an audit trail to find when problems occured, the problem I am
increasingly finding with Access is that...
|
by: bruboj |
last post by:
I created an audit trail for my access 97 database using code I found
at: http://members.iinet.net.au/~allenbrowne/AppAudit.html
One of the limitations stated for the code is "each table to be...
|
by: allyn44 |
last post by:
Helllo--I have implemented the audit trail from the Microsoft KB
article that records changes on a data entry form to a memo filed in
the fieeld's table record. What I would like to do is pull...
|
by: Jim M |
last post by:
I've been playing with Allen Browne's audit code and found it very
useful. I need to track record insertions, deletions, and edits for
several tables. I am planning to replace Access with Microsoft...
|
by: philmgron |
last post by:
Hello
I have been hitting my head against the wall on this problem for a day
now. I have a simple table that stores cities, on of the fields on the
table is modified_by. I am trying to write the...
|
by: hary08 |
last post by:
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...
|
by: babamc4 |
last post by:
I have a main form (mainformlung) with 5 subforms (followupacute, followuplate, biochemresults, haemresults and pftresults). I have copied Allen Browne's Audit Trail code (thanks to Allen Browne)...
|
by: flower88 |
last post by:
I have this code to keep track of the changes in a form. It is suppose to tell me what was changed, when, and by whom. I have this code as a module
Public Function Audit_Trail()
On Error GoTo...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |