Filtering Question (1 Viewer)

[2wistd]

So I am using the following code to the the userid:

Function GetUserName() As String
   Dim LUserName As String
   Const lpnLength As Integer = 255
   Dim status As Integer
   Dim lpName
   
   ' Assign the buffer size constant to lpUserName.
   LUserName = Space$(lpnLength + 1)
   
   ' Get the log-on name of the person using product.
   status = WNetGetUser(lpName, LUserName, lpnLength)
   
   ' See whether error occurred.
   If status = NoError Then
      ' This line removes the null character. Strings in C are null-
      ' terminated. Strings in Visual Basic are not null-terminated.
      ' The null character must be removed from the C strings to be used
      ' cleanly in Visual Basic.
      LUserName = Left$(LUserName, InStr(LUserName, Chr(0)) - 1)
      
   Else
      ' An error occurred.
      MsgBox "Unable to get the name."
      End
   End If
   
   GetUserName = LUserName
   
End Function

Then I am using this to allow access for users to certain forms.

Public Function autoprotectmain()
If DCount("*", "Accesslist", "WindowsID = '" & GetUserName() & "'") = 0 Then
MsgBox "You are not authorized in this section"
Else
DoCmd.OpenForm "maintable", acNormal, , , acFormEdit, acWindowNormal
End If
End Function

I have a table named "Accesslist" that has 3 columns, users name, users windows id, and office.

How can I add to this code to allow the user to only access members of their own office?

(maintable has all the user information in it for all employees, terrible naming I know!)

 

[CJ_London]

In your accesslist table you will need a field which can tell access what office they belong to.

Then all your forms/reports etc need to filter on this field

if you want to get the office value then just use the dlookup function - something like

UserOffice=Dlookup("Office","AccessList","WindowsID = '" & GetUserName() & "'")

 

[2wistd]

Dim tempbFlight As String
tempFlight = DLookup("Flight", "FlightCCAccesslist", "WindowsID = '" & GetUserName() & "'")
DoCmd.OpenForm "maintable", acNormal, "[Flight] = tempFlight", , acFormEdit, acWindowNormal

Okay, I tried this. Coming up with no filtering. Stepping through the code, tempFlight does have the right attribute, but I must still be missing something.

 

[CJ_London]

your openform isn't quite right - try

DoCmd.OpenForm "maintable", acNormal, "[Flight] [COLOR=red]= " &[/COLOR] tempFlight, , acFormEdit, acWindowNormal

This assumes that tempflight is a number, if it is text then you need

CODE]
DoCmd.OpenForm "maintable", acNormal, "[Flight] = '" & tempFlight & "'", , acFormEdit, acWindowNormal
[/CODE]

 

[2wistd]

Thanks, makes perfect sense.