Seeking help with list box in subform - 2 tables?

[UncleRobin]

I have a small database of Asian dramas and movies I’ve watched. Because each drama can have many genres, and each genre can be linked to many dramas, I have a created a join table to link the Dramas table and the Genre table, as in the screenshot

This works well in the subform for which it was created, here:

BUT, it requires me to enter the Genre ID. My workaround has been to keep a list of the Genres and IDs open so that I can enter the correct number into the field when adding a new drama.

I have been researching my issue here, and found a very helpful post here:

Using List/Combo Boxes in a datasheet subform for data entry

Your description of what you want on the form is a bit confusing. It appears you actually have two tables to enter here. The first is the Receipts. Simple enough - ID (auto), Date and Name. Next you have another table record to complete - Sales (guessing items from receipt). This is just a table grid containing the mentioned fields. Now you also have a table containing the different available items which you handle the entry through a different form. The tables are linked as Receipt.ID->…

That post helped me with formatting the column I wanted, but I think I’m still missing something.

I have created the list box as here:

but I’m stuck figuring out how to link it to the Genre table. Currently, both the “Drama-ID” column and “Genre-ID” column have the ‘tDrama-Genre’ table as their data source. I can’t work out how to show the genre NAMES (from the ‘tGenre’ table), while linking them to the Genre-ID field in the tGenre-ID table - the table that links genres to Drama IDs.

This is only a minor inconvenience, I’ve entered more than 500 dramas so far with the form as is, but I’d love to learn more about how to get more from Base, and this seems like a good opportunity. Also, Ratslinger’s excellent answer to the related question (linked above) encouraged me to think that it is probably workable, somehow. Any assistance will be gratefully received. Thank you.

[RobertG]

Isn’t Drama-ID part of the main form? So this value will be given from main form. You won’t need it in the table control.

For Genre you will need to change the field to a list box: Click on the header “Genre-ID”, right mouse click → Replace with → Listbox.
SQL-code for listbox would be
SELECT "GENRE", "ID" FROM "tGenre" ORDER BY "GENRE"

[UncleRobin]

You are a prince among men, thank you!