Hi there,
I have successfully implemented a UserAccessID method that locks down various aspects of my database.
I have a search form, that prompts the end-user to choose a query from which to conduct a 'smart search'. This combo-box gets the list of queries available using the following function in the Row Source:
Code:
SELECT DISTINCT MSysObjects.Name FROM MSYsObjects WHERE (((MSysObjects.Name) Like *qry_));
In doing so, the combo box is populated by all queries in my dB that start with the prefix of "qry_".
However, I have multiple queries that contain various collations of data records that are relatable only to certain end-users. For example, only the Manager of the Bay Area would need and want to see the query that pulls together all Bay Area data, while the Manager of SF would only need to see the query that pulls together the city data.
Is there any way to limit the contents of a single combo box by identifying using the IF function, based on access ID - what is available/visible?
Another way I have tried to achieve this has been to construct a combo-box for each end-user group that is available/hidden based off of the UserAccessID when they log in.
However, my code allows for only one master combo-box to determine what query is being searched and refined - so is there a way to link the value of multiple combo boxes (of which only one can contain a value at any given time) (IE. if there are four combo boxes, only one will have a record in it) and have that value written/ported to the master combo-box which could be hidden in the background?
The way I have set up my userAccessID's is roughly as follows:
Code:
Private Sub Form_Open(Cancel As Integer) If User.AccessID = 1 Or User.AccessID = 10 Then
Me.cboAvailableQueries.Enabled = True
Me.cbo24.Enabled = True
Me.cbo36.Enabled = True
Me.cbo57.Enabled = True
End If
If User.AccessID = 7 Or User.AccessID = 11 Then
Me.cbo57.Enabled = True
Me.cbo24.Enabled = False
Me.cbo36.Enabled = False
Me.cboAvailableQueries.Enabled = False
Exit Sub
End If
If User.AccessID = 8 Or User.AccessID = 12 Then
Me.cbo36.Enabled = True
Me.cbo24.Enabled = False
Me.cbo57.Enabled = False
Me.cboAvailableQueries.Enabled = False
Exit Sub
End If
If User.AccessID = 9 Or User.AccessID = 13 Then
Me.cbo24.Enabled = True
Me.cbo36.Enabled = False
Me.cbo57.Enabled = False
Me.cboAvailableQueries.Enabled = False
End If
Exit Sub
End Sub
Here is an image of the form I am currently attempting to manipulate with my current attempts at figuring this out: