[Solved] Populate values from linked table in a List Box

[nzeed]

Hi there,

I'm having an issue with populating some data into a form List Box. I have created a many-to-many relationship using a linking table and the form I have created displays that data fine. I used https://www.youtube.com/watch?v=GYawYO8u3_s to get this far. I would now like the form to display data from a further table that is linked.

In my example, the form is based on tbl_DummyEpisode and displays data from tbl_AdversariesTest. The Species value in tbl_AdversariesTest is a foreign key linked to tbl_SpeciesTest. In the form, the SubForm contans the relevant field mapping and the Adversaries List Box contains the SQL lookup code (the Data field is set to AdversaryID).

Code: Select all

"SELECT "Name", "ID" FROM "tbl_AdversariesTest""

As I said, that data is all displaying fine. However, I would like to display the Name value in tbl_SpeciesTest (instead of the current integar value from tbl_AdversariesTest).

Hopefully, this'll be an easy one for someone that knows what they're doing

[UnklDonald418]

In situations like this you are more likely to get a response to your question if you to upload a sample database, we hate having to create and populate a bunch of tables to test possible solutions.

That said try:
open your form in Edit mode then right click on the column"Species as text" to open the Properties: List Box dialog . On the Data tab select Sql as the Type of list contents then for the List Content enter

Code: Select all

SELECT  "Name", "ID" FROM "tbl_SpeciesTest";

[nzeed]

Thank you for responding. I'm getting some text in the "Species as text" feld now. However, it seems to be picking up the Name based on the ID from AdversariesTest, rather than from SpeciesTest (so in this example, "The Master" should be "Time Lords", not "Cybermen").

I've attached a sample database (thanks for the tip).

Who.odb

(14.21 KiB) Downloaded 281 times

[MTP]

Try this for the list content:

Code: Select all

SELECT S."Name", A."ID" FROM "tbl_SpeciesTest" S INNER JOIN "tbl_AdversariesTest" A ON S."ID" = A."Species"

[nzeed]

Top man, that works a treat. Thanks MTP.