Access ComboBox Query Question
-
It's been a while since I've used Access. I have an Access database with three tables summarized as follows. Table1 - ID (Primary Key) - Title (Text) Table2 - ID (Primary Key) - Type (Table1.ID combobox lookup single select) - Other_Fields Table3 - ID (Primary Key) - Type (Table1.ID combobox lookup single select) - Data (Table2.Type combobox multiselect using a query) - Other_Other_Fields Without using a form, I would like the Data combobox in Table3 to display only the rows in Table2 where Table2.Type = Table3.Type (=Table1.ID). An inner join is what I'm using, but it's joined to Table1.
SELECT Table2.ID, Table2.Type, Table2.Other_Fields
FROM Table2 INNER JOIN Table1 ON Table2.Type = Table1.ID
WHERE (Table2.Type=Table3.Type);Here's the problem, according to most of the examples that I've 'oogled, this is done by using a form and requerying the data upon combo update. However, I'm trying to implement this in the table. The problem that I'm seeing is that when I click on any of the Data fields in Table3, it uses the Type ID of the very first row in Table3. If I change that value of the first row's Type field, the table gets repopulated using this new value for all rows. However, if I click on the Home | Records | Refresh menu (not Refresh All), the row that is selected stays selected and the combobox gets populated with the correct data using that row's Type ID. So my question is, is there a workaround for this without using a form or am I banging my head against a wall? Is this a "by-design feature" (aka bug)? Thanks!