Filter Combo Box by Form Using In() 1

[DevelopV]

I want to filter the records in a combo box (cboSupplier) using the In() function.
On the form I have an unbound control called "intFilter". It contains the following string: 23, 58

The SQL statement for the combo box is

SELECT SupplierID, SupplierName
FROM tblSupplier
ORDER BY SupplierName;

In the criteria for field SupplierId I want to use the In() function so that the only records returned by the combo box are those in "intFilter"
I have tried

 In (Forms![frmSupplierReport]![intFilter]

but this does not work.

If I put: In (23, 58) in the criteria it works, but I cannot hard code it because the string in "intFilter" will change on the fly!

How would I achieve this?

 

[dhookom]

You would need to write some code to update the Row Source. This code might be in the After Update event of intFilter.

Dim strSQL As String
Dim strWhere as String
If Not IsNull(Me.intFilter) Then
    strWhere = "WHERE SupplierID IN (" & Me.intFilter & ") "
End If
strSQL = "SELECT SupplierID, SupplierName " & _
         "FROM tblSupplier " & _
          strWhere & _
         "ORDER BY SupplierName;"
Me.cboSupplier.RowSource = strSQL

Duane
Hook'D on Access
MS Access MVP

 

[DevelopV]

Thanks

but why does:
In (23, 58)
work and not
In (Forms![frmSupplierReport]![intFilter])

 

[dhookom]

Access queries can't interpret the reference to the multiple values from the control.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

"intFilter" contains string: "23, 58"

When I look at the name [blue]int[/blue]Filter I would assume int stands for [blue]Int[/blue]eger. Consider changing it to [blue]str[/blue]Filter

Just a suggestion...



Have fun.

---- Andy