Allen Browne's audit trail - change NetworkUsername() function

[Bellina]

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!

[NeoPa]

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 :-)

[Bellina]

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]

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]

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 ;-)

[Bellina]

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]

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]

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.

[Bellina]

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]

Do not Quote the Tempvars.
They should be "outside" of the quotes.
"some text here and then " & tempvars!username
etc...

[NeoPa]

Which version of Access are you using?

[Bellina]

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]

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.removeallpress 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]

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.

[Bellina]

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]

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]

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]

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!
'

[Bellina]

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!

[PPelle]

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]

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

?currentuser

(^-^)