Combo Box Filter

[jgelpi]

I have a combo box with a couple different report options. However, not all of the reports correspond with another value on the form. I need a way to filter out one of the form options if the form value in the other text box is a certain value(s).
i.e.

 
if text1 = 1 or text1 = 2 or text1 = 3 then
combo1 list = a, b, c, d
else
combo1 list = a, b, c

I am using an SQL statement to choose which report options are visible in the "combo1" as I have a table that defines the reports, and which is viewable by specific users (Managers and Employees).

[tlittell]

Tests for textbox value; assigns the combo box rowsource based on the query you assign. Let me know if this helps.

 
Private Sub Combo1_GotFocus()
Combo1.RowSourceType = "Table/Query"
    If Text1.Value = 4 Or Text1.Value = 3 Then
        Combo1.RowSource = "select test1.total from test1 where test1.count = 4"
        Else
        If Text1.Value = 5 Then
        Combo1.RowSource = "select test1.total from test1 where test1.count = 5"
        Else
        Combo1.RowSource = "select test1.total from test1"
        End If
    End If
End Sub

[jgelpi]

I'm not sure if I'm understanding how that code works. I am actually using an SQL statement in the combo box to filter out the options I want. Here is the statement that I have set up, but something in the "IIF" statement keeps triggering the error "This expression was typed incorrectly, or it is too complex to be evaluated."

SELECT TblReports.LetterName, TblReports.ScreenName, TblReports.RptNo, TblReports.EmpType FROM TblReports WHERE (((TblReports.RptNo)=IIf([Forms]![frmNonITAS_RO]![txtAcctType]=1 Or [Forms]![frmNonITAS_RO]![txtAcctType]=2,[RptNo],[RptNo]<>144)) AND ((TblReports.EmpType)="RO")) ORDER BY TblReports.ScreenName;

[tlittell]

I'm assuming you are referencing your query in the Rowsource property of the combobox. If that is true, a simple query wont look at your form to determine the value of your txtAcctType textbox. When you click the combobox does it give you a pop up asking for the value of txtAcctType?

You need to use some code. Add this code to you form. Replace Combo1 with the name of your combo box. Add line breaks in queries as needed.

Private Sub Combo1_GotFocus()
Combo1.RowSourceType = "Table/Query"
    If txtAcctType.Value = 1 Or txtAcctType.Value = 2 Then
        Combo1.RowSource = "SELECT TblReports.LetterName, TblReports.ScreenName,TblReports.RptNo, TblReports.EmpType
 FROM TblReports
 WHERE TblReports.EmpType="RO" ORDER BY TblReports.ScreenName"
        Else 
 Combo1.RowSource = "SELECT TblReports.LetterName, TblReports.ScreenName,TblReports.RptNo, TblReports.EmpType
 FROM TblReports
 WHERE tblReport.RptNo<>144 and TblReports.EmpType="RO" ORDER BY TblReports.ScreenName"
    End If
End Sub

Hope this helps.