How do I make an Access Form automatically open to

Question

I am trying to write VB code that would allow Access to recognize the computer ID of the person opening the database, open a form, and filter to the records assigned to the user. Does anyone know how to accomplish that?

Answer 1

An API to get the user name is probably better than using Environ. If the name is available in a table, you can use DLookUp to get an ID, which can be used with the Where argument of the OpenForm method of the DoCmd object to open a filtered list.

Answer 2

You can use Environ$("ComputerName") if that is what you mean by ComputerID. Here's a quick and dirty way I did it. Note that the user can usually turn off the form's filter. As others have stated, the environment variables on the PC have the potential to be modified by a non-admin user.

If you're concerned that the user will modify the environment variable for computer (i.e., SET COMPUTERNAME=MYBOSS via cmd) you can use a Win32 API call to get the ComputerName value if you know that the user is a non-admin user who cannot modify the computername via registry or through windows (see below).

CAVEAT with API

If the user has Admin privilege on the PC and they are able to edit the HKLM registry values then they could make the following registry entry:

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName]
"ComputerName"="MyBossComputerName"

If they restart the PC the Windows API call will use the incorrect name. They could then set the registry entry back to normal and restart PC.

In my scenario I have multiple users working on different days from same PC and Access Database. They open the database from a shared folder location on the PC. When they click the switchboard item and the form opens up the form's Load event runs. This form is bound to a table with a column named USERNAME which is simply the environment variable value of UserName (ex. John.Smith).

Here's the VBA I put in the load event:

Private Sub Form_Load()
    Dim currUser As String
    DoCmd.Maximize
    currUser = Environ$("USERNAME")
    Me.Filter = "USERNAME = '" & currUser & "'"
    Me.FilterOn = True
End Sub

API Call Solution

If you'd prefer to make an API call to get the ComputerName instead then you can do the following:

The API call is GetComputerName is a derivation from code either from Ken Getz (VBA Developer's Handbook) or Dan Appleman (Visual Basic Programmer's Guide to the Win32 API). You'll also find it all over the internet. First add a Module to your project rename it API or something meaningful. Then insert the following code:

Private Declare Function GetComputerName Lib "kernel32" _ 
   Alias "GetComputerNameA"(ByVal strBuffer As String, lngSize As Long) As Long

Public Function ComputerNameValue() As String
   Dim strBuffer as String
   Dim retValApi as Long
   Dim bufferSize as Long

   bufferSize = 256

   strBuffer = Space(bufferSize)

   'bufferSize will be returned with ComputerName length less null character.'

   retValApi = GetComputerName(strBuffer, bufferSize) 

   If CBool(retValApi) Then
      ComputerNameValue = Left$(strBuffer, bufferSize)
   Else
     'Your Error Handler'
   End If

End Function

In the form load event (modified to use API wrapper function instead of Envrion$):

Private Sub Form_Load()
    Dim PCName As String
    DoCmd.Maximize
    PCName = ComputerNameValue()
    Me.Filter = "COMPUTERNAME = '" & PCName & "'"
    Me.FilterOn = True
End Sub