There may be circumstances where you, as the database developer, may only wish to allow the current user of the Microsoft Access database to view only their own records. This can be achieved using various approaches and you will find one way of dealing with this shown below.
What we need to do to achieve this:
We have created the module called basCurrentUser shown below that collects the CurrentUserName:
Private Declare Function GetUserName Lib "ADVAPI32.dll" Alias "GetUserNameA" _ (ByVal lpBuffer As String, nSize As Long) As Long Public Function GetCurrentUserName() As String On Error GoTo Err_GetCurrentUserName Dim lpBuff As String * 25 Dim ret As Long, Username As String ret = GetUserName(lpBuff, 25) Username = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1) GetCurrentUserName = Username & "" Exit_GetCurrentUserName: Exit Function Err_GetCurrentUserName: MsgBox Err.Description Resume Exit_GetCurrentUserName End Function
Now that we have the Current User Name we can apply this in various ways to display only certain information pertaining to that user.
Now we need a method to store the Current User Name. This example uses a hidden form to store that value in a text field, the form is hidden using the AutoExec macro that fires at start-up.
The AutoExec macro contains the conditions of:
The form - frmLogin uses the On Open event to populate the text box with the user name taken from the above module. The on open event for the form is detailed below:
Private Sub Form_Open(Cancel As Integer) 'Populate the LoginName textbox with the CurrentUserName Me.LoginName = GetCurrentUserName() End Sub
The hidden form, contains only the LoginName text field:
Login Form hidden at start up containing the CurrentUserName
Now that we have the information stored, we can pass this to forms or queries to display only information relating to the Current User. The form below shows only records where "Graham" is entered as the Employee:
Form, filtered to show only the Current User's records
This form is opened from the Switchboard form using the OnClick event of a command button. Below shows the code required to open the form filtered by user. The filter is created via a query with the criteria set to only show the current users records based to the hidden login form:
Private Sub cmdOpenSalesFiltered_Click() 'Open the Sales form filtered to only show CurrentUser 'Close the Switchboard form DoCmd.OpenForm "frmSales", , "qry_filter_current_user" DoCmd.Close acForm, "frmSwitchboard" End Sub
Where the OpenForm passes the Filter set in the query "qry_filter_current_user". This query design is shown below:
Criteria passed to the query from the hidden login form
You can access any of the features shown from the switchboard contained in the downloadable database:
NOTE: To enable this example to work for yourself you will need to enter a record into the Sales table. This can be done by opening the Sales form and adding a new record.