Filter by ID

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am trying to setup a filter on my form that filters when a command button
is clicked. I have a combo box that lists the various column names which are
products a company might have. When the button is pressed, I want it to look
at what the value is in the combo box, and then search through each record
and determine if that company has the chosen product. I am unsure how to
write the code for this. Any help is appreciated.

Thanks
 
Daniel said:
Hi,

I am trying to setup a filter on my form that filters when a command
button
is clicked. I have a combo box that lists the various column names which
are
products a company might have. When the button is pressed, I want it to
look
at what the value is in the combo box, and then search through each record
and determine if that company has the chosen product. I am unsure how to
write the code for this. Any help is appreciated.

Thanks
Click to expand...

To clarify: you are wanting to only display those companies that have the
product selected in the combo box, right? What is the RecordSource of the
form? How are companies and products related - one to one or one to many?
Normally, a company can have multiple products, so the table design is to
have a table for companies and another table for products, with the company
ID being a foreign key in the products table. Is this your setup?

Assuming it is, here is what I normally do. In the Click event of the
button, change the form's RecordSource to add the filter as a WHERE clause.
I've broken this into several parts so you can see what's going on:

Dim strSQL As String

strSQL = "SELECT * FROM [companies table]"
strSQL = strSQL & " WHERE [company id field] IN "
strSQL = strSQL & "(SELECT [company id field] FROM [products table]"
strSQL = strSQL & " WHERE [product id field]=" & combobox & ")"
Me.RecordSource = strSQL

Of course, you'll need to modify this to use your own table, field, and
control names. Also if the the company id and/or product id fields are text
instead of numeric, you'll need to add appropriate quotes around their
values when building the SQL string.

Carl Rapson
 
Back
Top