Page 1 of 1

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

Posted: Tue Jan 17, 2017 10:49 pm
by 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).
relationship.jpg
form.jpg
Hopefully, this'll be an easy one for someone that knows what they're doing :)

Re: Populate values from linked table in a List Box

Posted: Tue Jan 24, 2017 11:18 pm
by 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";

Re: Populate values from linked table in a List Box

Posted: Wed Jan 25, 2017 12:34 am
by 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 306 times

Re: Populate values from linked table in a List Box

Posted: Wed Jan 25, 2017 9:42 pm
by 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"

Re: Populate values from linked table in a List Box

Posted: Thu Jan 26, 2017 12:34 am
by nzeed
Top man, that works a treat. Thanks MTP.