Limiting the contents of a Combo Box // depending on USERID

[Boost]

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:

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:

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:

[June7]

You want to set RowSource property with selected SQL statement? I think combobox columns have 255 character limit so referencing column index to get SQL might not be feasible. Use the ID with DLookup to pull SQL from table. Code in each restricted combobox AfterUpdate event would set master RowSource.

[CJ_London]

have you freetyped your rowsource? - you say

In doing so, the combo box is populated by all queries in my dB that start with the prefix of "qry_".

but the sql has the criteria 'Like *qry_' - which is a suffix

That said, your issue is trying to limit what a manager sees and is available for selection in a combo. So far as I can see the easy way (although goes against 'normalisation') would be to include in your query name a key such as 'Bay' or 'SF' and have that key associated with the manager so you can include it in your rowsource criteria. But would only work for a relatively limited number of keys - at some point you are likely to get a clash.

A more flexible way would be to use the query description property to hold the key. Then rather than using msysObjects, parse the querydefs collection to build a value list of the queries - something like

function getQueryNames(key as string) as string
dim qdef as querydef
dim qryStr as string

qryStr=""
for each qdef in currentdb.querydefs
    if qdef.description=key then qryStr=qryStr & "," & qdef.name

next qdef

if qryStr<>"" then getQueryNames=mid(qryStr,2)

end function

and to call (probably in the form open event if you already know the manager at that time)....

me.combo.rowsource=getQueryNames(managerkey)

But I would question this

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.

I would expect a single query which would have appropriate filters applied to limit the rows which would mean you would not need the above