Populate combo box based on another query

[RossWindows]

I have searched through this forum hoping to find an applicable example but maybe I am not looking for the right thing:

I have a form called frmUserIncidents based on qryUserIncidents.
On the form I have a combo box which will open another form based on what the user clicks.

I have another query called qryUserPermissions which has boolean fields that will control various accessibility.

When frmUserIncidents opens, I need the combobox populated with the options available based on the user's permissions in qryUserPermissions.

I wish I could post an example of my database, but it contains confidential information and it would be a whole other project to remove that data.

Currently I am using select case statements for each permission variable. I think I should be using a combination of a select query and a loop statement but I just don't know how to go about that.

Also, the code in the funky green color is intended to remove the semicolon from the end of string CreateList if one is there. I don't know if the code actually works, so if you know whether it does or not, please let know.

Here is what I have so far (errors highlighted in red)

' Populate cboCreateNew with options based on the user's permissions.
Dim CreateList As String
Dim SemiColon As String
Select Case [COLOR=darkred]qryUserPermissions.bolRP_ADD Or qryUserPermissions.bolRP_EDIT[/COLOR]
    Case True
        CreateList = CreateList & "Replacement Part" & Chr(59)
    Case Else
End Select
Select Case [COLOR=darkred]qryUserPermissions.bolPI_ADD Or qryUserPermissions.bolPI_EDIT[/COLOR]
    Case True
        CreateList = CreateList & "Product Info" & Chr(59)
    Case Else
End Select
Select Case [COLOR=darkred]qryUserPermissions.bol88_ADD_EDIT[/COLOR]
    Case True
        CreateList = CreateList & "Escalation/Assist" & Chr(59)
    Case Else
End Select
Select Case [COLOR=darkred]qryUserPermissions.bolQA_ADD_[/COLOR][COLOR=darkred]EDIT Or qryUserPermissions.bolQA_OPS_EDIT[/COLOR]
    Case True
        CreateList = CreateList & "Call Monitoring" & Chr(59)
    Case Else
End Select
 
[COLOR=darkolivegreen]If Right(CreateList, 1) = ";" Then[/COLOR]
[COLOR=darkolivegreen]   Right(CreateList, 1) = ""[/COLOR]
[COLOR=darkolivegreen]End If[/COLOR]
    cboCreateNew.RowSource = CreateList

Any help or a point in the right direction is greatly appreciated!

 

[WayPay]

Also, the code in the funky green color is intended to remove the semicolon from the end of string CreateList if one is there. I don't know if the code actually works, so if you know whether it does or not, please let know.

No, it doesn't. You can't assign to the Right() function. Try this:

CreateList = Left(CreateList, Len(CreateList)-1)

As for the combobox, you could try this:
1. Put the list in a table.
2. JOIN on the permissions table to populate the combobox with only those items the user has permission to use.

Hope this helps.

 

[RossWindows]

No, it doesn't. You can't assign to the Right() function. Try this:

CreateList = Left(CreateList, Len(CreateList)-1)

I just saw that somewhere but I thought it was for something else.
Thanks!

As for the combobox, you could try this:
1. Put the list in a table.
2. JOIN on the permissions table to populate the combobox with only those items the user has permission to use.

Sorry, I don't quite follow. Could you elaborate?