How to use login username to filter all records in the current database.

[rod4]

hello guys,

how can i use the login username to filter all records in the specific database the user is working on, including looking into his profile information table and editing it (only finding his profile data), create new records in performance tables (with his username stored as well) etc.

how can i store the username till the user is logout or exit the database?

[jforbes]

Hey rod4,

If your users have Unique Windows Logins, I would use this to identify them. Then use that Identity to Filter Forms as needed to just the currently logged in user.

This is a function I use to get the Currently logged in User (I stripped out some things to make it more generic):

Option Compare Database
Option Explicit

Global gImpersonateUser As String

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function GetWindowsUser() As String
On Error GoTo ErrorOut

    ' Source:  http://www.techrepublic.com/blog/10-things/10-plus-of-my-favorite-windows-api-functions-to-use-in-office-applications/

    Dim lngResponse As Long
    Dim strUserName As String * 32

    If Len(gImpersonateUser) > 0 Then
        GetWindowsUser = gImpersonateUser
    Else
        lngResponse = GetUserName(strUserName, 32)
        GetWindowsUser = Left(strUserName, InStr(strUserName, Chr$(0)) - 1)
    End If

ExitOut:
    Exit Function
ErrorOut:
    MsgBox "Could not determine Windows User."
    Resume ExitOut
End Function

Then you can reference the GetWindowsUser( ) function when you want to Filter a Form as well as use it for a default when entering data on a Form.

There is a few ways to use it to Filter the Recordset of a Form. One way is to create a Query with the GetWindowsUser( ) function as part of the criteria:

SELECT Employees.*, Employees.UserID
FROM Employees
WHERE (((Employees.UserID)=GetWindowsUser()));

Another way would be to create the Form as normal, then apply a Filter On Load either from the DoCmd.OpenForm method or in the OnLoad Event of the Form:

DoCmd.OpenForm "Preferences", WhereCondition:="UserID='" & GetWindowsUser() & "'"
OR
Private Sub Form_Load()
    Me.Filter = "UserID='" & GetWindowsUser() & "'"
    Me.FilterOn = True
End Sub

You can also use the GetWindowsUser( ) function as a default for fields that you want to default to the currently logged in user. Easiest way to do this is add a TextBox to the Form, set it's ControlSource to the field name that you want to default, set the Default Property to =GetWindowsUser () and then either Lock or Hide the field.

One last thing to note, in the code above, it makes use of a Global Variable named gImpersonateUse r. If you want to do some debugging and want to act like you are someone else, set gImpersonateUse r equal to their Windows User ID and everything that uses GetWindowsUser( ) will get their Windows User Name instead of yours.

[rod4]

hi jforbes,

thanks for your help! i am trying this right away!