Subform data is disappearing

[T Payne]

I have a main form called "Books" based on a table called "Books." I have a
subform called "Author subform" base on two fields (lastname, firstname) in
a table called "Authors."

I want to be able to enter Author information for each book in the Books
form, and have that information automatically update the Authors table. I
ALSO want the Author information to remain with each book.

I have managed to get the Author information from the subform to update the
Authors table, but if I leave and go back to that book, the Author
information is gone! It's in the Author table, but it doesn't seem to get
associated with the record in the Books table from which I entered it.

As far as I can tell, the relationships are all set up right. What am I
doing wrong??

Thanks for any help.

T

 

[John Vinson]

I have a main form called "Books" based on a table called "Books." I have a
subform called "Author subform" base on two fields (lastname, firstname) in
a table called "Authors."

Names are a Very Bad choice of link. They're not unique. I can't pull
any examples of authors out of my memory at the moment, but consider
Englebert Humperdinck (19th century composer, 20th century performer)
or Richard Burton (traveler and author, translated The Thousand Nights
and a Night) and Richard Burton (20th century actor). For another,
many books have multiple authors.

I want to be able to enter Author information for each book in the Books
form, and have that information automatically update the Authors table. I
ALSO want the Author information to remain with each book.

The Author information SHOULD NOT EXIST in the Books table. See below.

I have managed to get the Author information from the subform to update the
Authors table, but if I leave and go back to that book, the Author
information is gone! It's in the Author table, but it doesn't seem to get
associated with the record in the Books table from which I entered it.

As far as I can tell, the relationships are all set up right. What am I
doing wrong??

Two things: assuming that relationships automagically copy data to
store it redundantly in a second table (they don't), and setting up
your tables incorrectly.

Try instead:

Books
BookID <ISBN, Library of Congress ID, or some other unique ID>
Title
<other info about the book as an object>

Authors
AuthorID Primary Key <autonumber probably>
LastName
FirstName
DateOfBirth
<other identifying information>

Authorship
BookID <link to Books>
AuthorID <link to Authors>

You'ld use a Form based on Books, with a subform based on Authorship
(*not* on Authors); put a combo on the subform bound to AuthorID but
displaying the author's name. Use the combo's Not In List event to pop
up a data entry form for authors when a new author is needed.

John W. Vinson[MVP]

 

[T Payne]

Thank You!

Another "easy" question. How to get a combo box to display a different value
than the field it is bound to?

Thanks
T

 

[John Vinson]

Thank You!

Another "easy" question. How to get a combo box to display a different value
than the field it is bound to?

Two ways: Make the Bound Column something other than the first; or use
the ColumnWidths property to set the width of the bound column to
zero. For example, if you have a Row Source query

SELECT EmployeeID, LastName & ", " & FirstName AS EmployeeName
ORDER BY LastName, FirstName;

with Control Source = EmployeeID; Column Count = 2; Bound Column = 1;
and Column Widths = 0";1.25" you'll see "Jones, Mary" while the
computer sees her ID.

The trick is that the combo - when it's not dropped down - displays
the contents of the first non-zero width field.

John W. Vinson[MVP]