Comboboxes

[BMan111]

Ok, I'm not entirly sure I can rig the row source of a combo box like this or not, so I'm just going to ask.

I have a table that defines a training course: courseid, name, etc... There can be up to 3 trainers, with field names trainerid, secondtrainerid, thirdtrainerid. each one holds an integer that is an id number linked to the employeeid field in a seprate employee table.

After you define a training course, you have to schedule a training session. On the form for the training session, I need a combo box to select which of the three possible trainers is teachign that particular session.

So, how would I go about taking the values from those three fields and make them the row source for the combo box.

Any help would be most appreciated, and if you need anymore information just let me know.

Brandon

[BMan111]

I figured out a rather shady/backdoor way to accomplish this. I'm going to post the code, some of it wont make sense, as it includes table names and field names and such, but if anyone else would need to do this, this will prolly work for you to.

Private Sub cboTrainer_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("tblCourse")
    Dim rst1 As DAO.Recordset
    Set rst1 = CurrentDb.OpenRecordset("tblEmployee_Reference")
    Dim con As String
    Dim sql As String
    Dim train1, train2, train3 As String
    
    Do Until rst.EOF
        con = rst!courseID
        If con = Me.cboCourse Then
            train1 = rst!trainerID
            train2 = rst!secondtrainerid
            train3 = rst!thirdtrainerid
            rst.MoveLast
        End If
        rst.MoveNext
    Loop
    
    If Not IsNull(train1) Then
        Do Until rst1.EOF
        con = rst1!Employee_Reference_Number
             If con = train1 Then
                train1 = rst1![first name] & " " & rst1![last name]
                sql = sql & train1 & ";" & rst1!Employee_Reference_Number
                rst1.MoveLast
            End If
            rst1.MoveNext
        Loop
    End If
    
    If Not IsNull(train2) Then
        Do Until rst1.EOF
        con = rst1!Employee_Reference_Number
             If con = train2 Then
                train2 = rst1![first name] & " " & rst1![last name]
                sql = sql & ";" & train2 & ";" & rst1!Employee_Reference_Number
                rst1.MoveLast
            End If
            rst1.MoveNext
        Loop
    End If
    
    If Not IsNull(train3) Then
        Do Until rst1.EOF
        con = rst1!Employee_Reference_Number
             If con = train3 Then
                train3 = rst1![first name] & " " & rst1![last name]
                sql = sql & ";" & train3 & ";" & rst1!Employee_Reference_Number
                rst1.MoveLast
            End If
            rst1.MoveNext
        Loop
    End If

    Me.cboTrainer.RowSource = sql
    Me.cboTrainer.Requery
End Sub

Hope this will help someone else at some point.

Brandon