SubForms Problem

[LearnersPermit]

I have a form which is based on one table (tblBooks) and have created a subform which is based on a query involving 3 tables (tblAuthors, tblCitations and tblBooks). Whenever I create a listbox on the subform based on the query I am unable to show more than 1 author for each book. The subform will only display the first author selected for each book_ID. I have cheated and created a second field for authors in order to display the second author for each book. Of course if there are more than 2 authors I will need to create additional fields. I have based the subform on the tblAuthors table and have tried to use combo boxes all with different problems occuring which don't resolve my problem.

Is there a better fix for my problem?

 

[JerryDennison]

Your relationships are not correct, either at the relationship(i.e. table) level or at the form/subform level. Also, it appears that you're trying to use the same table, tblBooks, as a child to itself. While this is not unheard of I've never seen it applied to something like books before. Anytime you have a recursive join it is usually because there is a direct parent/child relationship between one record in the table and one or more other records in the same table. i.e. a true parent/child relationship where you have fields for MotherID and FatherID in the table and you have records for an individual linked to other records in the same table that belong to his/her mother and father.

In your case you should establish a many-to-many relationship between authors and books. Since it's possible that one author can write many books and that any given book can have more than one author. Since Access doesn't have a way of defining a many-to-many relationship directly you must use an intermediate table. this table would have foreign key links to the pk of the books and authors table. These should then be set as a compound primary key to disallow duplicating the same author for the same book. You can find more on many-to-many relationships by search this subject on this website.

 

[LearnersPermit]

Thank you for your help. I continue to have some problems that you can probably assist me with. I have three tables:
tblAuthors - Author_ID, FirstName, LastName
tblCitations - Author_ID, Books_ID
tblBooks - Books_ID and a number of other fields not related to my problems.

We want to create a form listing each of the Books and their related authors. We created a form based on tblBooks as it's control source and placed a subform using tblCitations as it's control source. On the Citation subform we created a listbox using tblAuthors.Author_ID as it's control source. Our goal was to see a listing of the Authors by first and last name, unfortunately we get a list of the Author IDs for a particular book.

Created a form which had as it's control source a query based on the three tables, we got the query that the listbox was based on rather than the Author name or even the Author ID.

Thank you for your help.

 

[JerryDennison]

This is easier than you might think. All you need do is change what is displayed in the combobox for Authors. Open the subform in design view, select the AuthorID control and change the Row Source property, this should open the query wizard for this rowsource. Add the author name field to the QBE grid, if the name is split into two fields(i.e. first and last names) then you may use a calculated field to concatenate them into either a first and last name or last and first name separate by a comma:

FullName: [FirstName] & " " & [LastName]

or

FullName: [LastName] & ", " & [FirstName]

Close and save the query. Then change the Column Count property to the number of columns you now have in the query. Change the column widths property so that you hide the bound column. This is done by setting it's width to '0"'. So if you now have two columns you'd set their widths as follows:

0"; 3"

Now, even though you're actually storing the pk(which is what you should do) of the author you'll display their name.

 

[LearnersPermit]

Thank you - this worked just great.

Thanks for your help.

 

[JerryDennison]

You're welcome.

 

[gusbrunston]

When I undertook to learn Access I bought a book, Running Access2000. It contains a sample database about books (for a book store) that tells more about books, authors, photos of authors, Library of Congress numbers, etc. than I ever wanted to know. With your interest in a database about books, you might find the $44.99 for the book and several sample databases on cd worth the dinero.
Gus Brunston
An old PICKer
padregus@home.com