Database (MS Access) terminology question

[KneadToKnow]

I don’t mind Googling and using the built-in help myself for the actual how-to on this (though if anyone’s willing, I’ll be happy to be saved that trouble). The problem is that I’m not Access-savvy enough to come up with the right terms. Here’s what I want to be able to do:

I have a table of book titles and I have a table of, for want of a better term, subject headings. Each book may have multiple subject headings and each subject heading maybe used by multiple books. So far, I can do this.

When the data is being entered into the database, it will be using a form for the book table with a subform (?) for the subject headings. I want the data-entry person to be able to select from available subject headings already in the database, but also to be able to add new subject headings which have not yet been used.

What am I describing that I can’t seem to find on my own in the built-in help system or via Googling?

[Mangetout]

Sounds like you’re looking for ‘limit to list’, or rather, it’s opposite: adding an item to a combo box on the fly.

How are you storing the multiple subjects per book? Not in separate columns, I hope?

[KneadToKnow]

@Mangetout

How are you storing the multiple subjects per book? Not in separate columns, I hope?

No, that’s exactly why I’m trying to get the person who wants this not to use Excel for it; that’s how she planned to do it. (And why she brought it to me, as I’m the local Excel smarty-pants.)

There’s a separate table for subjects. So, table Books has fields like BookTitle, BookAuthor, etc., and table Subjects has a field Subject. (There’s also an ID field for both, natch.)

[stephen59]

So in your form you want a combo box that has its row source as: “Table/Query List” with your Table as the source and the second column shown. With Limit To List set as “No” any entry will work but I don’t know if it will add to your Subjects table?

Do you have your ID field set to Autonumber?

[Mangetout]

@KneadToKnow

No, that’s exactly why I’m trying to get the person who wants this not to use Excel for it; that’s how she planned to do it. (And why she brought it to me, as I’m the local Excel smarty-pants.)

There’s a separate table for subjects. So, table Books has fields like BookTitle, BookAuthor, etc., and table Subjects has a field Subject. (There’s also an ID field for both, natch.)

So the multiple book/subject relationships are in another table of keys (BookSubjects?), containing nothing but pairs of foreign keys to a book and a subject/

[KneadToKnow]

@stephen59

So in your form you want a combo box that has its row source as: “Table/Query List” with your Table as the source and the second column shown. With Limit To List set as “No” any entry will work but I don’t know if it will add to your Subjects table?

That’s the objective, so that’s important.

Do you have your ID field set to Autonumber?

Yes.

@Mangetout

So the multiple book/subject relationships are in another table of keys (BookSubjects?), containing nothing but pairs of foreign keys to a book and a subject/

Not yet, no, but I can certainly make that happen.

[Chessic_Sense]

The magic words here are “combo box”. Put those on your form and use the table as your source.

[KneadToKnow]

I’ve used combo boxes before; I don’t remember them allowing me to add new data to a source table by default. What setting do I need to watch for as I’m adding the combo box to my form?

[K364]

Here’s some help that does exactly what you want to do

[Chessic_Sense]

@KneadToKnow

I’ve used combo boxes before; I don’t remember them allowing me to add new data to a source table by default. What setting do I need to watch for as I’m adding the combo box to my form?

I’d have to look it up. I don’t have Access on this computer. But when you’re in design mode, it should be something like “record source”. That’s where you tell it what table and column to use for the data. Alternatively, you can just type it in manually, using a semicolon to separate entries.