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

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

P: 17
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?
Jun 8 '15 #1

✓ answered by 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):
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Global gImpersonateUser As String
  5.  
  6. Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
  7.  
  8. Public Function GetWindowsUser() As String
  9. On Error GoTo ErrorOut
  10.  
  11.     ' Source:  http://www.techrepublic.com/blog/10-things/10-plus-of-my-favorite-windows-api-functions-to-use-in-office-applications/
  12.  
  13.     Dim lngResponse As Long
  14.     Dim strUserName As String * 32
  15.  
  16.     If Len(gImpersonateUser) > 0 Then
  17.         GetWindowsUser = gImpersonateUser
  18.     Else
  19.         lngResponse = GetUserName(strUserName, 32)
  20.         GetWindowsUser = Left(strUserName, InStr(strUserName, Chr$(0)) - 1)
  21.     End If
  22.  
  23. ExitOut:
  24.     Exit Function
  25. ErrorOut:
  26.     MsgBox "Could not determine Windows User."
  27.     Resume ExitOut
  28. 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:
Expand|Select|Wrap|Line Numbers
  1. SELECT Employees.*, Employees.UserID
  2. FROM Employees
  3. WHERE (((Employees.UserID)=GetWindowsUser()));
  4.  
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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "Preferences", WhereCondition:="UserID='" & GetWindowsUser() & "'"
  2. OR
  3. Private Sub Form_Load()
  4.     Me.Filter = "UserID='" & GetWindowsUser() & "'"
  5.     Me.FilterOn = True
  6. End Sub
  7.  
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 gImpersonateUser. If you want to do some debugging and want to act like you are someone else, set gImpersonateUser equal to their Windows User ID and everything that uses GetWindowsUser() will get their Windows User Name instead of yours.

Share this Question
Share on Google+
2 Replies


jforbes
Expert 100+
P: 1,107
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):
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Global gImpersonateUser As String
  5.  
  6. Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
  7.  
  8. Public Function GetWindowsUser() As String
  9. On Error GoTo ErrorOut
  10.  
  11.     ' Source:  http://www.techrepublic.com/blog/10-things/10-plus-of-my-favorite-windows-api-functions-to-use-in-office-applications/
  12.  
  13.     Dim lngResponse As Long
  14.     Dim strUserName As String * 32
  15.  
  16.     If Len(gImpersonateUser) > 0 Then
  17.         GetWindowsUser = gImpersonateUser
  18.     Else
  19.         lngResponse = GetUserName(strUserName, 32)
  20.         GetWindowsUser = Left(strUserName, InStr(strUserName, Chr$(0)) - 1)
  21.     End If
  22.  
  23. ExitOut:
  24.     Exit Function
  25. ErrorOut:
  26.     MsgBox "Could not determine Windows User."
  27.     Resume ExitOut
  28. 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:
Expand|Select|Wrap|Line Numbers
  1. SELECT Employees.*, Employees.UserID
  2. FROM Employees
  3. WHERE (((Employees.UserID)=GetWindowsUser()));
  4.  
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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "Preferences", WhereCondition:="UserID='" & GetWindowsUser() & "'"
  2. OR
  3. Private Sub Form_Load()
  4.     Me.Filter = "UserID='" & GetWindowsUser() & "'"
  5.     Me.FilterOn = True
  6. End Sub
  7.  
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 gImpersonateUser. If you want to do some debugging and want to act like you are someone else, set gImpersonateUser equal to their Windows User ID and everything that uses GetWindowsUser() will get their Windows User Name instead of yours.
Jun 8 '15 #2

P: 17
hi jforbes,

thanks for your help! i am trying this right away!
Jun 10 '15 #3

Post your reply

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