Access Database Query by Combobox Multi

Question

I have a Combobox in a form where you can select multiple values in the list. I want to query results that match what options were selected in the combobox. Is this possible?

Answer 1

Yeah I would get a type mismatch. I just managed to fix it by using nested select statements to lookup the id by the value. Thanks for the help!

One other thing: I have the combobox on the form that is bound to the lookup field, and it seems it will always default to the value of the first row of the lookup field. How can I change the value of the combobox to a specific row from the lookup field? Thanks

Answer 2

It works as written even if T2ID is a string.  Did you try it?

Answer 3

Anyone?

Answer 4

In my case, what you have as 'Table2.T2ID' is a string, not a number. As I understand it 'Table1.MVF.Value' is returning a list of ID's corresponding to the key of the MVF, so I get a type mismatch. How do I instead get 'Table1.MVF' as a list of strings so I can check if the string from Table2 is in it? Thanks

Answer 5

If it's a multiselect combo box, I believe that means the form must be bound and the combo box must be bound to a multi-value field.  In that case, you can query related records by referring to the multi-value field in the record to which the form is bound, so long as the form includes the primary key of that table.

I'll clarify by example.  Suppose you have table Table1, containing (among others) fields T1ID (the primary key) and MVF (the multi-value field to which the combo box on the form is bound).  Suppose also that Table2 contains records that are related to Table1 by having T2ID (the primary key) = one of the values selected in Table1.MVF.  Finally, suppose that you have a form, frmTable1, that is bound to Table1.  If you want the query to return those records in Table2 that are selected by field MVF for the record that is current on frmTable1, then the query could have this SQL:

SELECT * FROM Table2 WHERE Table2.T2ID In

    (SELECT Table1.MVF.Value FROM Table1

     WHERE Table1.T1ID=[Forms]![frmTable1]![T1ID])