Table update using combo box.

[Spencer]

My table, tbl_Files, has several fields titled "Keyword1, Keyword2, etc...".
These fields all lookup to a master keyword table, tbl_Keyword. On my form
when I enter information I need to put in keywords about what I have entered.
Sometimes there will be one word, sometimes several for an entry. If I enter
a new keyword in one of the keyword fields, which are combo boxes, how do I
get it to update the master table, tbl_Keyword? Also, though I won't be
putting in the keyword twice, I would like it to update the other keyword
fields as well on the form so the new keyword appears in the combo boxes.

Thanks

 

[Jeff Boyce]

Spencer

If your table has 'repeating fields' ("Keyword1, Keyword2, ..."), you have a
.... spreadsheet, not a database!

In a relational database, a one-to-many relationship (One "File", zero, one
or more "Keywords") is represented with TWO tables. The "parent" table
holds the "one" side (?File). The "child" table holds the "many" side rows
(?Keyword).

This way, when a given File has 37 Keywords, you don't need to add 37
columns to your table. Instead, you add 37 rows to your "many" table, each
one consisting of the (same) FileID, plus each separate Keyword.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Spencer]

I was missing that, thanks. I've created the additional table,
tbl_FileKeywords with the primary key being the primary keys of the related
tables, tbl_File and tbl_Keywords.

I'm not sure how to make the form work. I enter details of a file, its
location, its description, category, etc..., and then need to enter keywords
about that file. How do I create the multiple keyword drop down fields
pulling previously entered keywords from tbl_Keyword, and adding a new
keyword if it is not found?

Does this make sense?

 

[Jeff Boyce]

Spencer

The common approach is to use a main form (which you'd point to the File
data), and a subform, which you'd point to the new table with both keys.
When you embed the subform within the main form, Access asks you to indicate
which field(s) they share ... and they share the FileID field!

Regards

Jeff Boyce
Microsoft Office/Access MVP