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:="LoggedIn User", Value:=strTheUs ersNameFromTheF orm
Here I've explicitly set the agruments by name and the "strTheUsersNam eFromTheForm" 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!Logged InUser 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!UserN ame" 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!userna me
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.Val ue

so that it now reads: tempvars.add "strUserNam e", txtUsername.Val ue

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!strUs erName press return
it should return the last user name entered in the login form

?tempvars.item( "strUserNam e") press return
it should return the last user name in the login form

tempvars.remove allpress return
Now both:
?tempvars!strUs erName press return
?tempvars.item( "strUserNam e") 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.