changing comboboxs on form open

[dhannant]

So I have a form that is used through 4 different sections of my work center. It's used to create schedules and each combox is populated using information that is in the data via Select [Combined Roster].[Last Name] etc... that parts not important because it works great. My problem is that since a single form is used for all 4 sections, and each section has about 30 people, I don't want to populate the list with EVERYONEs name. Is it's broken down at the end with the WHERE [section]='sales' or 'marketing' etc...

Every section can access it through a menu and is populated differently depending on which section they are in. So if a sales manager pulls up the scheduler, it's populated only with his sales reps and like wise for the warehouse manager being populated with only warehouse workers. There are 15 comboboxes for day shift labeled Days1 - Days15.

Initially I just set it up like

Forms![Shift Schedules]!Days2.RowSource = "SELECT [Roster].[Last Name] & ', ' & Left([First Name],1) FROM [Roster] WHERE [Work Center]='Sales'; "
Forms![Shift Schedules]!Days3.RowSource = "SELECT [Roster].[Last Name] & ', ' & Left([First Name],1) FROM [Roster] WHERE [Work Center]='Sales'; "
Forms![Shift Schedules]!Days4.RowSource = "SELECT [Roster].[Last Name] & ', ' & Left([First Name],1) FROM [Roster] WHERE [Work Center]='Sales'; "

This works fine however, I have 3 shifts so that's 45 lines like the above and it's just a mess. Would rather do it with a while loop. So I set this up.

Dim formsource As Variant
Dim cbooutput As String
Dim cbovalue As Integer
Dim cboexp As Variant

cbovalue = 2

Do
    cbooutput = "Days" & cbovalue
    formsource = "Forms![Shift Schedules]!"
    cboexp = formsource & cbooutput
    cboexp.rowsource = "SELECT [Roster].[Last Name] & ', ' & Left([First Name],1) FROM [Roster] WHERE [Work Center]='Sales'; "
    cbovalue = cbovalue + 1
    
    Debug.Print cbooutput
    Debug.Print cboexp
    Debug.Print formsource
    Debug.Print cbovalue
    
Loop Until cbovalue > 16

All of the variables work... I can see in the watch window that it's all set to what it should be however, it's like it's not combining everything correctly because unlike the 1st part, none of the RowSource information changes on the shift schedule. Maybe I'm using the variables in the statement incorrectly? I'm not sure.

The cboexp is supposed Forms![Shift Schedules]!Days1.RowSource = "SELECT [Roster].[Last Name] & ', ' & Left([First Name],1) FROM [Roster] WHERE [Work Center]='Sales';"
Abvously the only part that actually changes is the Days1... Is there an easier way or am I on the right path?

New to variables and Loops.

Thanks,

 

[dhannant]

ok... so I was way wrongloop setup. Even if I was able to make that work, which I doubt, I found a much easier way to do this through a lot of trial and error. Just posting incase someone else has a similar question.

Dim ctrl As Access.Control
For Each ctrl In Forms![Shift Schedules].Controls
    If ctrl.ControlType = acComboBox Then
       ctrl.RowSource = "SELECT [Roster].[Last Name] & ', ' & Left([First Name],1) FROM [Roster] WHERE [Work Center]='Sales'; "   
    End If
Next ctrl