Updating related tables

[Guest]

I'm trying to create a database of quotations from various ancient authors,
categorised by language, author, work, reference, quotation etc.

I have a table containing authors, a related table containing a list of
works (many works belonging to one author), a table containing quotations
(many quotations from a single work) and then a table listing grammatical
constructions (many constructions for a single quotation and many quotations
for a single construction).

However, it makes sense for a user to be able to enter all this information
in a single form. I have a form which contains all the relevant fields and
which displays authors and works as combo boxes.

(1) How can I ensure that once the user selects an author, only the works
listed for that author in the works table, are then displayed in the works
combo box?

(2) How can I design the form so that when the author and/or work is not in
the list (because this is the first quotation to be added from that source)
the user can type the name into the combo box and a record will be created in
the correct table for it?

 

[TC]

(1) How can I ensure that once the user selects an author, only the works
listed for that author in the works table, are then displayed in the works
combo box?

Base the works combo on a SELECT statement which filters records
depending on the author selected in the author combo: SELECT * FROM
TBLWORKS WHERE AUTHOR=FORMS("THISFORMNAME")![AUTHORCOMBOCONTROLNAME],
or somesuch.

Then have the AfterUpdate event of the author combo, requery the works
combo: ME![WORKSCOMBOCONTROLNAME].REQUERY. Then, whenever you select
an author, the works combo will only how the works for the slected
author.

(2) How can I design the form so that when the author and/or work is not in
the list (because this is the first quotation to be added from that source)
the user can type the name into the combo box and a record will be created in
the correct table for it?

Check out the NotInList event.

HTH,
TC (MVP Access)
http://tc2.atspace.com