Microsoft Access Listbox Query

Question

I have 2 tables in Microsoft Access.

Table 1:

ID    Val1    Val2    Val3
 1      a       b       c
 2      e       f       g 
 3      h       i       j   

Table 2:

ID    Val Chosen
 1        a
 2        f
 3        j

In the Val Chosen field, I want Access to create a listbox in Val Chosen that contains the values in Val1, Val2, Val3 for the respective id. For example, when the user clicks the Val Chosen listbox for the record with ID 1, it should contain "a", "b", and "c". The Val Chosen listbox for the record with ID 2 should contain "e", "f", and "g".

In design mode for "Table 2", the display control is set to list box. The following query is used as a row source and incorrectly returns all possible values.

SELECT Val1
FROM Table1
UNION
SELECT Val2
FROM Table1
UNION
SELECT Val3
FROM Table1

The actual database has many records so I would prefer to preserve the structure of the database if possible.

Answer 1

I think that in this case you better do this in a form. The problem with your qeury is that it lacks where clauses. For id 1 for example, the selects to union should be: SELECT Valx FROM Table1 WHERE ID=1

On a form you can do this for example by setting the row source in the on current event depending on the value of ID.

And yes, the structure of your database could need an update. Wich can be done with one simple query.

Answer 2

Have you tried using WHERE criteria in row source query? It is not a good idea to deal directly in the tables, instead use the forms. You can do this easily using forms combo box. And without WHERE criteria, you cannot filter the contents. You can follow the following method:

1. Create a form (form source table 2) with a bound combo box with row source value chosen
2. Create subform with data source table 1 and insert either text boxes or combo boxes bound to val1, val2, val3.
3. Insert the subform into previously created form.
4. Use link master and child field (simple) OR VBA code (versatile) to filter the contents of subform on basis of selected val chosen.
5. If you want to follow this method, I can provide code and sample db.

In addition, you can also see this link(https://www.everythingaccess.com/tutorials.asp?ID=Build-a-search-criteria-form). Hope it helps.

Best Regards