[Solved] Use listbox w/junction table, select multiple items

[Doranwen]

I just received some guidance here to create my database as a main table with four element tables, each with a junction table between it and the main table, because for every record in the main table, it will have one or more elements from at least one of the element tables associated with it: viewtopic.php?f=61&t=103530

The problem is that I cannot figure out how to set up the listboxes correctly to do this. I know I managed to figure out (mostly just with great help) how to do it all with my first database, but that one was different enough that I'm having a hard time adapting things to this one. For that one, I actually had a macro set up to filter the options based on what I had selected for other such boxes, whereas here, all options will be available to all records on the main table, so no filtering is necessary. I do want to display a description field instead of the main field for the ID numbers, but I can trade that out later, I was just experimenting.

I attached what I have so far, but I know I've got something wrong in terms of the SQL or setup or *something*. I started with the Meta box as it's the smallest list and I have at least two things I can mark on it for the test word I put in there. Would love to know what I'm doing wrong and how to fix. I tried to follow a tutorial on how to do the listboxes but couldn't make sense of it, and everything I could see looked like I was doing things the same as any of the demo dbs I've seen, but obviously I've missed something. :/

[UnklDonald418]

Actually, it looks like your List Boxes are properly designed, but there are 2 other problems.

1)Junction tables work best with a compound Primary Key but that is beyond the capabilities of the Table Design GUI, so junction tables are best created with SQL.

Code: Select all

CREATE TABLE  "Words_Meta"(
"WordID" INTEGER,
"MetaID" INTEGER,
CONSTRAINT "FK_WD1" FOREIGN KEY ("WordID") REFERENCES "Words" ("WordID"),
CONSTRAINT "FK_ME1" FOREIGN KEY ("MetaID") REFERENCES "Meta" ("MetaID"),
CONSTRAINT "PK_WM1" PRIMARY KEY ("WordID","MetaID" )
);

Note that the constraints each need a unique name.
After executing the SQL command at Tools>SQL be sure to inform Base of the changes by selecting View>Refresh Tables.

2) For a table control on a Form to be editable there must be a column for the Primary Key. Since Words_Meta has a compound Primary Key, there must also be a column for "WordID".
Right click on a blank area to the right of the column named Meta and select Insert>Formatted Field
Right click on the new column and select Column to open the Properties: Formatted Field dialog
On the Data tab select WordID as the Data field.
Right click again on the new column and select Hide Column.

Now the list box should work on that SubForm.

[Doranwen]

Actually, you CAN use the GUI to create a compound primary key - you just have to select *both* fields simultaneously and right-click to choose primary key (having removed the relationships first). You saying that reminded me I had just forgotten to do that, oops! But I'd done that with the first database I created, so the memory of how to do it came back to me and I was able to do it again.

Ahhhh, the second column! I knew I was missing *something*. Now it definitely does work. Thank you!

Though I have one further question: Is there any way to make the dropdown *larger* so I can see more at once? It seems to be proportional to the overall box size instead of a specific limit - the Meta one has only 13 elements in all and there's plenty of space to see them all, so it will be annoying if I constantly have to scroll down to get to one of the last ones. Plus the others I have plenty of space for - I work on a 23" monitor so it's not like I'm limited to a tiny laptop screen…

EDIT: I just now see the "Line count" option so I'm testing to see if that's it, lol.

EDIT #2: Yup! OK, so if anyone wonders how to make the dropdown larger or smaller, it's definitely the "Line Count" option. Niiiiice. Thank you again! This is now solved, and I think I'm ready to apply the split wizard to it, import the first block of words and frequencies, and start adding the rest of the info.

(Also, if anyone downloads the db I attached above, I forgot to link master and slave fields for the other three - I'd only tested thoroughly with the Meta box and had missed applying that to the rest - so I had to do that before it'd let me enter anything in any of the other three boxes.)