Filter subform based on user

[gori1084]

Hi everyone,

I have a database where I created a log in box. Each user accesses the file by entering his/her credentials in the log in box. Those credentials are stored in a separate table called "users". The same table also stores the field "region" corresponding to where each user is located.

In the database there are two other tables: "companies" and "subsidiaries". The "subsidiary" table contains a field called "region", with the same set of values as those stored in the "users" table (see above). These tables are populated using a form/subform combination. "Companies" is the parent (main form) and "subsidiaries" is the children (sub-form).

I need to have the sub-form display ONLY the records where the region field in the sub-form matches the region field corresponding to the user who just logged in through the log in box.



I am fairly new to Access and if someone could provide some guidance, that would be truly appreciated.

Best,
Bernardo

[rpeare]

so you want your main form to show only companies that have subsidiaries in the region in question and the subform only show the subsidiaries within the region?

I am assuming this is a bound form?

For the main form I would create a query that connects the COMPANY table to the SUBSIDIARY table through and put a criteria in the REGION field of the subsidiary that matches the user's region I'm assuming you are storing that in a field on your forms somewhere or storing the userID where you can retrieve it) This should give you a query that's updatable and only shows companies that have subsidiaries within the region. Then you could apply a criteria to the query driving your subform that also limits the REGION to match the user's region.

[gori1084]

Thank you for your quick reply.

I'll answer your questions first:

1) I don't really care about the main form since there is no region information stored there. I can leave that unfiltered, guess it would make the whole thing simpler and I need to go baby steps cause I don't know Access that well.

2) Yes, the forms are bound forms.

In relation to your answer I have two questions:

1) How would a query filter the form? I'm kinda puzzled how that would work.
2) How does the database know which user is currently logged in? I mean, yes the data for the user and the corresponding region is stored in the Users table but how do I tell Access to filter based on the User that's logged in?

Thanks!

[ranman256]

If you want.. create a new module: modUSER , paste in this code...
It can use their network login as their ID , thus eliminating the 'access' login.
function : getUserID()

Option Compare Database
Public Const C_STARTCOUNT As Integer = 1001
Public Const C_MIN_LIST_HEIGHT As Integer = 3675
Public Const C_MAX_LIST_HEIGHT As Integer = 4210
Public Const MAX_PATH = 260
Public Const CSIDL_FLAG_MASK = &HFF00                    'mask for all possible flag values
Public Const SHGFP_TYPE_CURRENT = &H0                    'current value for user, verify it exists
Public Const SHGFP_TYPE_DEFAULT = &H1
Public Const S_OK = 0
Public Const S_FALSE = 1
Public Const E_INVALIDARG = &H80070057                   ' Invalid CSIDL Value
'Use the GetUserName API function.
Public Declare Function SHGetFolderPath Lib "shfolder" Alias "SHGetFolderPathA" (ByVal hwndOwner As Long, ByVal nFolder As Long, ByVal hToken As Long, ByVal dwFlags As Long, ByVal pszPath As String) As Long
Public Declare Function WNetGetUserA Lib "mpr.dll" (ByVal lpName As String, ByVal lpUserName As String, lpnLength As Long) As Long
  
' Return the user's name.
Public Function getUserID() As String
Dim sBuffer As String * 255
Dim sName As String
sBuffer = Space(255)
Call WNetGetUserA(vbNullString, sBuffer, 255&)
sName = Left$(sBuffer, InStr(sBuffer, vbNullChar) - 1)
If Len(sName) Then
    getUserID = LCase$(sName)
Else
    getUserID = "<Unknown>"
End If
End Function

Public Function GetUserFullName()
Dim vName, vUserName, vUserDomain
Dim i As Integer
    Set WSHnet = CreateObject("WScript.Network")
    vUserName = WSHnet.UserName
    vUserDomain = WSHnet.UserDomain
    Set objUser = GetObject("WinNT://" & vUserDomain & "/" & vUserName & ",user")
    
    i = InStr(objUser.FullName, "(")
    If i = 0 Then
       vName = objUser.FullName
    Else
       vName = Left(objUser.FullName, i - 1)
    End If
    
    GetUserFullName = vName
End Function

[gori1084]

yeah, that's great thank you. I'll test it out. But I guess that wouldn't fix the filtering issue right?

[rpeare]

You are capturing your user information when they log in, what are you doing with the user information once the password is verified? You can perform a lookup of the person's 'region' with their username/pw then pass that value to your form when they open it (as an unbound field that is invisible would be the simplest way). Then use that unbound field in your criteria

So let's say your formname is called frmMain, when the user logs in, you look up their region from your user table, open the form frmMain and copy their region information to a field called CRITERIAFIELD.

if you create the queries as I posted previously you could then use a criteria of:

[forms]![frmMain]![criteriafield]

in both of the queries.

[gori1084]

Ok thank you, i am beginning to see where you are going. However, I'm still unclear on the role of queries here in the forms. I guess I don't understand how does a query relate to a form? Meaning that if I do capture the user region through the query, I'm not sure how it would make it into the form?

[rpeare]

that's my whole point.... if you designed a login screen you have a table that lists user names, passwords, and region when you go through the procedure of looking up to make sure the password matches you can perform a lookup of the region at the same time, then when you open your data entry form pass the looked up region value to the form (forms(formname).controls("RegionControl").value = <looked up region value> for instance).

[gori1084]

Oh I see. So what you are suggesting is to build the feature in the code I built for the login screen?

If so, that'd be amazing so I don't have to make it so much more complicated. Thing is, where I work none in the radius of 20 miles has the slightest idea of how to code (and actually neither do I for the most part) so if I make the DB too code heavy they'll freak out when I hand it over. If I just have one form code heavy and the others kinda make intuitive sense, that'll make all the difference.

Here is the code I use, modified substantially from accessforums.uk. Would you be ableto take a look and suggest where you think the feature you suggest would fit best?

Private Sub Combo5_AfterUpdate()
'After selecting user name set focus to password field
Password.SetFocus
End Sub

Private Sub Command9_Click()
'Check to see if data is entered into the UserName combo box

If IsNull(Employee) Or Employee = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Employee.SetFocus
Exit Sub
End If

'Check to see if data is entered into the password box

If IsNull(Password) Or Password = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Password.SetFocus
Exit Sub
End If

'Check value of password in tblEmployees to see if this
'matches value chosen in combo box

If Password.Value = DLookup("[Password]", "Users", _
"[LoginID] =" & Employee.Value) Then

LoginID = Employee.Value

'Hide logon form and open splash screen
Visible = False
DoCmd.OpenForm "Sponsors"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"Invalid Entry!"
Password.SetFocus
End If

'If User Enters incorrect password 3 times database will shutdown

intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database.Please contact admin.", _
vbCritical, "Restricted Access!"
Application.Quit
End If
End Sub



Thanks, truly, a million.

B

[rpeare]

'Hide logon form and open splash screen
        Visible = False
        DoCmd.OpenForm "Sponsors"

right after this perform your lookup of the region from your users table
dlookup would work fine and you're already performing the dlookup of the password so the methodology would work the same.

ON your SPONSORS form put in an invisible field named USERREGION

add something like

form("sponsors").controls("USERREGION").value = DLookup("[Region]", "Users", "[LoginID] =" & Employee.Value)

then when you want to use this field as a criteria to limit the data you see on your form/subform you would use

forms!sponsors!userregion

[gori1084]

Thank you SO MUCH! I'll test it out now then I'll revert back here. All my gratitude!

[gori1084]

Just one more question before I start actually.

This part here: forms!sponsors!userregion

Would it need to go in the filter of the form right?

[rpeare]

not the filter, the critera for both the main form and subform based on my original post.

EDIT:

Actually, without going to a whole lot of trouble you can apply the criteria to just the subform. Any company that does not have a subsidiary in the region would just have an empty subform

[gori1084]

Wait, are you still talking about creating a query?

[rpeare]

you don't need to create a separate query, you just need to base your subform on a SQL statement where the criteria of the region is pulling from the userregion field of your sponsors form