FredixBlog

Quertion: Hi,
I have two tables. One contains a list of books and another contains a list of authors. The author table has a primary key, AuthorID . There is a column in the books table, AuthorID to show who the books author is.
I have a form that displays the authors details. When you go to the next record it displays the next authors details.
I would like a button this form that displays all of the books by this author. Please can you tell me a good method for doing this. I don’t know if it is possible to have all of an authors books displayed on screen at the same time. If it is not then I imagine that after clicking on the ‘Show all books by this author’ button, a new form is opened that displays the tile of one of the authors books and you can click on next record to see the others (if any).
Thank you for your help.
Andrew

Fredix gave this response on 11/11/2000:
Sure you can display the books of the author on the same form. There are several steps, but this is very easy.
1) With the form wizard, create a form based on the Books table, choose the tabular style.
2) save this form, this will be a subform of the Authors form.
3) Open the Authors form in design view, make it bigger at the bottom, so that it can receive the books form. Make sure you make grow the Detail Section, not the Form Footer.
4) Drag the Books form from the database window into the authors form. (keep mouse button pressed)
5) Your authors form now has a subform with the books he has written. Resize it as needed
6) To make sure of this, look at the properties of this new subform (right-click, choose properties). There are two properties: Link Child fields and Link Master Fields. Both should have AuthorID. If not, type AuthorID in both.

Save and test your form. Is this what you want? The magic now is that you can add/modify/delete books details with this form, but there is a little more thing to do:

1) In ‘Tools’ menu, choose ‘Relationships’ command.
2) Add both Authors and and Books tables, using the button with a yellow plus sign.
3) By dragging the AuthorID field from the Authors table to the AuthorID field of the Books table, create a new relationship.
4) Double-click this new line to open its properties.
5) Click on the ‘Join Type’ button. Choose second option (Don’t try to understand the text on the right, or if you understand it, explain it to me, I don’t understand it ). This option is simply to tell that an author can have no books entered yet. This called an outer join, or according to Access terminology, a left/right join.
6) Close and save this relationship.
7) Verify that there is an arrow on the side of the books table. If not, return to Join Type and choose third option instead of second.
Now, we enter the difficult part of the job: Einforcing relationship integrity. Normally, this is done at the very beginning of database design, with no data in the tables. Doing it now can fail because you may have books with no authors entered.
9) No matter, lets try: Double-click the relation again and check the ‘Einforce integrity’, the ‘Cascade Update’ and the ‘Cascade delete’ checkboxes. (Cascade delete is optional, but I feel if you delete an author, you would delete its books too).
10) Close and save the relationship. No message? You are the lucky one because your data is reliable.
11) Doesn’t work? Just ask a follow up question, and I will tell you how to remedy. Don’t forget to give me the error message.

I hope this makes sense to you and wish you full success with your books database.”

No related posts.