help: many-to-many, data entry via ComboBox

[coast.foundation]

From an O'Reilly book, I've created a sample database. the relevant
tables are: Author, Book and AuthorBook.

obviously, AuthorBook is a "junction" and only contains foreign keys
(ISBN and AuthorID) in addition to it's own key. My form for data
entry uses comboBoxes to create the pairings in AuthorBook. The form
displays the key to AuthorBook, the two foreign keys to AuthorBook,
and then uses ComboBoxes to select from books and authors. all this
is very nice and straightforward, I think. (admittedly, the fields
from AuthorBook should be invisible, but I've left them visible for
now.)

However, how do I enter *new* authors from *this* form? I'm typing
away, *selecting* author/book pairings for data entry, but now I need
to enter a new, unheard of author. The comboBox gives an error
whenever I attempt to do so.

the relevant links are:

http://safari.oreilly.com/0596002734/ the raw data
http://www.geocities.com/files/BookAuthorPublisher.mdb/ my sample


thanks,

(e-mail address removed)

 

[Allen Browne]

You don't enter new authors directly into this combo box. It lacks the
fields you need to enter the names and other details of the author.

What we generally do is provide drill-down through a double click. A
double-click of the combo box performs these actions:
1. Undo the combo (in case it contains something invalid).
2. Open the Author form, to a new record.

In the Author form's AfterUpdate event, check if the previous form is open,
and if so, requery the combo so it hears about the new author. Typically
that's
Forms!MyOtherForm.MySub.Form.MyCombo.Requery

It is also possible to use the NotInList event of the combo, but that's best
reserved for cases where there is only a single field to enter, and that's
the field that is visible to the user. Details in article:
NotInList: Adding values to lookup tables
at:
http://allenbrowne.com/ser-27.html