Relationship between 3 tables

I have 3 tables → Company, People, Locations. A company can have multiple people working for it, Companies and people can have multiple locations each. But in a location (office building) might be several companies and people
Basically I would like to create a form with 3 table-controls. When I select an entry in one table-control the other two show the records related to it.
-select a company shows all its employees and locations
-select a person shows the company he/she is working for and the persons address(es)
-select a location shows companies and people found there.
My attempt so far have not worked. Created a linktable and subforms which shows all the entries in the tables but the filtering is not working.

Thanks, had a look at it. 3 tables are people, things and animals. but if I choose “cow” it does not show me who has cows and things related.

Open any of the forms. A form is a user interface to edit related data across tables.
Animal form: Navigate from animal to animal using the tab key or the navigation bar. The orange edit box shows the animal owner. The yellow listboxes show the animal owner’s items.

Persons form: Navigate from person to person using the tab key or the navigation bar. The blue list box shows the owner’s animal. The yellow listboxes show the animal owner’s items.

Things form: Navigate from item to item using the tab key or the navigation bar. The orange listboxes show owners of the current item. The blue list box shows the selected owner’s animal.

Any main form in the top-left corner shows an additional grid view when you focus the main form and click the last button on the navigator toolbar.

Now I can see. In form “Animal” when I move to Hamster then I can see that Max and Dolores have one. Selecting Max show he has a Screwdriver whereas Dolores has Hammer and Chisel.
I have to think through this, my brain is a bit slow today :wink:

The only tool you really need to know is the form navigation window (menu:Forms>Navigation Bar). It allows you to build up hierarchies of forms and subforms many levels deep.
The root element is [Forms] where you can attach indpendent main forms.
Forms can have subforms and controls.
Table controls have no data properties, no record set nor linked field. They inherit their parent form’s record set. A table control is a bundle of regular controls with linked fields. The controls are represented as table columns. In a table control you can display one date box for each row in a date field, one currency box for each row in a decimal field one listbox for each foreign key and so on.
Unfortunately, the form navigator does not show the form controls (columns) of a table control.

Thanks, you got me to sit down and learn about queries and sql.
I succeeded with one input field and searching across 3 tables and all fields. For each table I have a query and a subform with a table-control. Each subform has a button to refresh it. Works!
Testbase.odb (15.3 KB)

If you insert Mai then it finds in Persons somebody as well as a company.
The Mainform also has a button “Refresh all” but this is not working.
My objective now is to get rid of the 3 subform buttons and only have 1 button to refresh all 3 subforms - but how to do it.
You answered the same question in 2015 Refresh 3 subforms but putting the button on the mainform is not working.

Without macro code, this is the best I can get:
Testbase.odb (15.5 KB)

I changed the 3 queries to show all records when no search expression has been entered.
There is still a minor issue because you are promted to save the current record every time you modify the search expression. This could be solved with a little bit of macro code.

Testbase_AutoRefresh.odb (16.1 KB)

Second approach with a tiny Python macro.
I changed the form’s search box from multi-line to single line
I changed the push button from action “Refresh” to “Save record”, default button “Yes”, take focus “No”.
Added a hidden control (see forms navigator).
Assigned form event “After record action” to an embedded macro.

Now you can enter a search expression and hit Enter, edit search expression, hit Enter without using the mouse.

P.S. the original search form had a flaw. It allowed insertion of new records which made it possible to tab into the new record below the existing one. And a navigation toolbar makes no sense for this form. Just disallow everything on the “Data” tab except “Modify record”.