Hello,
I am trying to get the 5 comboboxes I need to relate to their previous one to populate a table. There are about 20 fields in the table but for those 5, Country; Region; Sub-region; Producer and Name, I have separate tables and only need there ID’s in the main-table.

Using the method in AC307 resulted in a very unstable Form. It works fine in the lesson because its unbound and only for displaying data from existing data. When bound to the main-table pulling data from the sub-tables for the use of storing new data doesn’t work well.
It looks like the data is stored but its very unstable in keeping them visible on the form. When I close the form and re-open it parts of the data disappears although there are values in the table(s). At first I thought the old data was the problem and I selected the values anew. With a little better result but also not stable enough. The new input is even worse, only parts of the data show up and there is no consistency to search for the problem.

Using the Not In List event from AC 324 gives a better but not perfect result. The “old” data from the main-table are displayed perfectly. On new entries with existing data I found a way to filter the next combo by copying the select statement from a query I build to see how to build the filter. SELECT FROM WHERE ORDER BY. And to get it work a Me.Refresh in the after-update event of the Combo’s.
Seems to work as it should but …… on closing and re-opening the form the first record is perfect, all the rest is a mess. The filter seems not to work as it should. Checking the main-table all the records are filled correct and the sub-tables also.
I went even as far as deleting all records in all tables and start anew; same result.

Quickly build a Form bound to the main-table and pulled in the fields; on opening it shows all the records with the ID’s. I didn’t bound them to the sub-tables.

Again build a new form with combo’s to get the data from the sub-tables.
This is working fine, all data shown as filled in.

Question: Where does it go wrong?
The Me.Refresh in the After-Update event might been the problem. Problem without the Me.Refresh the next combo in line will not filter on entering a new value (or am I on the wrong track?) But I am wrong, after removing the after updates the problem is still the same.

I am at the end of my knowledge and experience in this. The On Not In List does it job nicely for all the data shows up in the tables.

So who knows the right way?
(Other than make the form with the On Not in List and Me.Refresh a single record form just to use it as for adding a new record)

Eddy peanut