Can a table be used as a filter on a form?

[mystuff]

Hi,

Can a table (or query) be used as a filter on a form? The record source of my form has a Product ID. I have another table that also has a product ID. I would like to use the Product IDs in this second table as a filter to my form.

I know I could join the 2nd table in the record source, but for various reasons, I don't want to do that.

I want to filter the records based on the values in the 2nd table.

Is this possible?

Thanks.

 

[fneily]

Not sure why you need two tables to do what you want. Create a combobox without the wizard. On the Rowsource of the combobox place:

SELECT DISTINCT [ProductID] FROM FirstTable ORDER BY [ProductID] WHERE [ProductID] is not null;

Then on the AfterUpdate event place:
Private Sub comboboxname_AfterUpdate()
Dim R As DAO.Recordset
Dim SQLText
Dim holda As Integer
SQLText = "Select * From [FirstTable] Where [ProductID] = " & Me![ComboboxName]
Forms![YourFormName].RecordSource = SQLText
Me![comboboxname] = Null
End Sub

 

[JoeAtWork]

Assuming:
TableA = source for the form
TableB = filter table

You could create a query as the data source for your form that is something like:

SELECT * FROM TableA WHERE ProductID IN (SELECT DISTINCT ProductID FROM TableB)

 

[fneily]

Since your pulling the same ProductID's, why go to the second table? It's slick, but redundant.

 

[JoeAtWork]

I've inferred from the OP's original post that TableB has a subset of ProductID's that are found in TableA. For example, maybe TableA is a products table and TableB is an order details table, and he is trying to get a list of products that have been oredered at least once.

I don't understand why a simple JOIN couldn't be used instead, but this is what he asked for.

 

[fneily]

Isn't it funny how different people see the same sentence different ways? I thought that TableA had a subset of TableB! Oh well.