multiple instances where inactive names should not show on list

[bikermo]

I borrowed code from the thread titled "Inactive names dont show on old records" in this forum, and it works GREAT! (one post away from being able to add links, sorry!)

But one tiny problem: we have a form that will have one combo box for Associates (like Employee in the OPs DB) which I've got set to show only active Associates for new entry per the tips in the above thread, but we'll also have a combo box for Developer which I need to function the same way. I don't know how to add that part to the On Current event, can anyone help?

adapted code:

Private Sub Form_Current()
Dim strComboRowSource As String
        If Me.NewRecord Then
        strComboRowSource = "SELECT T_Associates.AssociateID, T_Associates.Associate, T_Associates.Active  " & _
                            "FROM T_Associates " & _
                            "WHERE (((T_Associates.Active)=True));"
    Else
        strComboRowSource = "SELECT T_Associates.AssociateID, T_Associates.Associate, T_Associates.Active " & _
                            "FROM T_Associates; "
    End If
    Me.cboAssociateID.RowSource = strComboRowSource
    Me.cboAssociateID.Requery
End Sub

Thanks-

 

[bikermo]

Well I added the sql language where you recommended, but i am now getting "sytax" error and the new section of code is the culprit. everything is spelled correctly and all the fields referenced do actually exist, so I'm usure what I've done wrong. Any thoughts?

Private Sub Form_Current()
Dim strComboRowSource As String
        If Me.NewRecord Then
        strComboRowSource = "SELECT T_Associates.AssociateID, T_Associates.Associate, T_Associates.Active  " & _
                            "FROM T_Associates " & _
                            "WHERE (((T_Associates.Active)=True));"
    Me.cboDeveloperID.RowSource = "SELECT Table_Developers.DeveloperID, Table_Developers.Developer, Table_Developers.Active  " &
                            "FROM Table_Developers " & _
                            "WHERE (((Table_Developers.Active)=True));"
    Else
    strComboRowSource = "SELECT T_Associates.AssociateID, T_Associates.Associate, T_Associates.Active " & _
                            "FROM T_Associates; "
    Me.CboDeveloperID.RowSource = "SELECT Table_Developers.DeveloperID, Table_Developers.Developer, Table_Developers.Active " & _
                            "FROM T_Associates; "
    End If
    Me.cboAssociateID.RowSource = strComboRowSource
    Me.cboAssociateID.Requery
    Me.CboDeveloperID.Requery

End Sub

 

[bikermo]

i'm missing something. your response added these two lines to my original code:

        Me.cboDeveloperID.RowSource  = "some SQL string"

and

        Me.cboDeveloperID.RowSource  = "some other SQL string"

plus the requery at the bottom. i thought that meant i should put the sql to call the fields from the Developers table, which is what i did, and that first line is the first instance giving me a compile error (syntax error).