databasedev.co.uk - database solutions and downloads for microsoft access

Filter to only view Current User's Records in a Microsoft Access form or query

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:

• Create a module to capture the Current User accessing the Microsoft Access database
• Store the User Name to pass this to the Microsoft Access forms or queries
• Display information that relates to the Current User only.

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:

• OpenForm - frmLogin
• RunCommand - WindowHide

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:

To download the accompanying Microsoft Access 2000 Database example please go to either the Microsoft Access Forms page or the Microsoft Access Downloads page.

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.