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