Adding Many Records to Unsaved One Record

[Guest]

I have a bibliographic database that stores book information. My client would
like to add a new table to the database that stores individual authors. One
book can have many authors.

At this stage, I'm thinking of having an Authors dialog box the users can
open by clicking a button on the Book form. My problem is that I need a saved
record before I can start linking authors to it, and it would be unusual for
users to have to add the authors after they have added a book rather than
during.

I could always save the record beore the dialog box opens, but my problem
there is that there are quite a few required fields in the Book table. I
think it would irritate a user to get a message saying "You must do this,
this and this before you can add an author".

Any thoughts?

 

[John W. Vinson]

I have a bibliographic database that stores book information. My client would
like to add a new table to the database that stores individual authors. One
book can have many authors.

And one author can write many books, of course!

At this stage, I'm thinking of having an Authors dialog box the users can
open by clicking a button on the Book form. My problem is that I need a saved
record before I can start linking authors to it, and it would be unusual for
users to have to add the authors after they have added a book rather than
during.

I could always save the record beore the dialog box opens, but my problem
there is that there are quite a few required fields in the Book table. I
think it would irritate a user to get a message saying "You must do this,
this and this before you can add an author".

I'd suggest not one but TWO additional tables: Authors and Authorship.
Authors would have (at least) fields for AuthorID, LastName,
FirstName; you might want to have additional biographical data if
that's of interest (birth and death dates, nationality, etc.); this
might help with duplicate names. Authorship would have a field for the
primary key of your books table (ISBN, or some other unique book
identifier) and the AuthorID, perhaps with an additional field for
sequence (first or second author) or other information about *this*
author with respect to *this* book.

For data entry you could have the Form based on the Books table, with
a continuous Subform based on Authorship. On the subform you could
have a Combo Box to select the author; it would display the author's
name while storing the ID. In its Not In List event you could open the
data entry form for Authors; so if you selected an existing name it
would just put it in, but if you typed a name new to the system you
could easily enter it.

John W. Vinson [MVP]

 

[Guest]

Thanks for replying James - what you've described is exactly what I have done
so far.

My problem is that users would not be able to enter an Author until after
the Book record is saved - and the Book record requires several fields to be
entered first. But it seems (to the users anyway) a disruption to data entry
to ask them to fill in all these required fields before they can start
listing authors (which is usually done at the start of adding a record).

 

[John W. Vinson]

Thanks for replying James - what you've described is exactly what I have done
so far.

It's John not James.

My problem is that users would not be able to enter an Author until after
the Book record is saved - and the Book record requires several fields to be
entered first. But it seems (to the users anyway) a disruption to data entry
to ask them to fill in all these required fields before they can start
listing authors (which is usually done at the start of adding a record).

Well, you have a problem then! The only solutions I can see are to:

1. Make the process of adding authors as easy as possible; the
NotInList event can capture what the user has typed and add it to the
Authors table as a new record. This will be a possible problem as you
will get spelling errors entered as new authors.

2. Remove the relational integrity constraints and allow free text in
the author field. I DON'T recommend this...

3. Train your users.

John W. Vinson [MVP]