Robust form input from joined tables (1 Viewer)

[KirRoyale]

I have a database with a main table, joined to a few other tables. The main table has 10 fields of which, initially, 4 had table level lookups on ID fields, pulling in the names (such as status and Region) from other tables. However, the more I played with advanced functionality, the more I realised just what a pain the table lookups are and I could no longer keep finding a work around.
So now I would like to have a robust form for data input. Currently, for the 4 ID fields I have combo boxes which show the ID values and, when selecting the value, also the related names. I also have additional controls pulled from the ‘underlying’ tables which display the names corresponding to the ID values.
The flaws with this are:
1. The ID fields (also showing the names when selecting) are not in alphabetical order – and the ‘company’ field has almost 200 entries to choose from.
2. I am not able to type in / select the name from the field coming from the underlying table.
I would like to be able to input a new record by selecting a value from a combo box showing the names (Region, status etc.) and for the ID field / control to be updated automatically – bearing in mind that e.g. the region name is in the region table and only the region ID is in the main table.
I would be grateful if somebody could point me in the right direction.

 

[Mihail]

I understand that you have not used the lookup wizard when you have designed the tables.
While the experts have good reasons to avoid this wizard, for a beginner is an amazing help.
But lets start from where you are.
So, you have a combo that show 2 fields when is in drop down state (ID , Name). When you select from the drop down list only the ID is shown.
Yes ? Is this your situation ?

 

[KirRoyale]

Yes, that’s correct – the ID is the only field shown from the combo box after selection.

 

[Mihail]

Select combo -> Property Sheet -> Format -> Column Count = 2 -> Column With = 0

 

[KirRoyale]

Excellent! thank you.
Do you know how to sort the ‘names’ alphabetically? I think the sort order of the ID numbers is showing.

 

[Mihail]

Select combo -> Property Sheet -> Data -> Row Source -> press the button with 3 dots -> Sort Name's field Ascending/Descending in the query

 

[KirRoyale]

Fantastic! Just what I was looking for!