Create an Acces Form for the tables Book /Author in a library Database

Your structure doesn't allow for a book to have more than one author. This might be a bit limiting for you in the future.

However, in a book form, you would add a combo box using the combo box wizard to select an AuthorID from the Author table. If you want functionality to add a new Author, consider reviewing this video from Crystal

First of all thank you for your answer.

This is just a first example in an introductory course on databases, so this database has no future and limitations are not a problem.

After viewing the video on VBA I will downsize my ambitions.

Still would it be possible in the Book Form to access the AuthorID by enterind his/her name, instead of picking up a number from a list in a combo box ?
(provided that the author is already in the Author Database, of course)

You might like to take a look at Library.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

The physical model for this little demo file is as below:

As you see this caters for a publication having more than one author by modelling the many-to-many relationship type between Publications and Authors with the Authorship table, which resolves the many-to-many relationship type into two one-to-many relationship types.

PS: In the subform for inserting the author(s) for each publication, to add an author not yet represented in the database just type in their name in the combo box. Code in the control's NotInList event procedure will insert a new row into the Authors table and add the new author to the combo box's list.

Still would it be possible in the Book Form to access the AuthorID by enterind his/her name, instead of picking up a number from a list in a combo box ?
(provided that the author is already in the Author Database, of course)

You are teaching a course on databases and you are looking to create BAD table design? I'm sorry to be so harsh, but this is not the way to teach.

A book table NEEDS a foreign key for the Author (more on that later). It does NOT need to repeat the Author name over and over. The database user does NOT pick a number, they pick a name from an Authors table, however, what is stored in the Book table is the AuthorID. This is done via a combobox that displays a name but returns the ID.

In actuality, however, a book may have multiple authors. Therefore, provision should be made to deal with what is a many too many relationship. This requires a junction table that models that relationship Yes, I understand this is a teaching exercise and you want to use a simpler example. By doing you, however, you are promoting bad design..

I'm sorry but I will stick to one author per book.

I do not understand what you mean by the need of a foreign key for the Author in the Book table, if you look at my ERD it has a foreign key for the author.

My question is how to implement in the Book Form the possibility to pick a name from the Author Table and to return the AuthorID, what you suggest at the end your second paragraph is exactly what I want but I don't know howto do it.

> how to implement in the Book Form the possibility to pick a name from the Author Table and to return the AuthorID

Duane's earlier reply tells you exactly how to do this.

"I'm sorry but I will stick to one author per book."

Please, for the sake of your students' learning, make sure they understand you are asking them to create a limited design that would not work in the real world outside the classroom.

Please, for the sake of your student's further development, make sure they understand WHY you are asking them to create such a version, rather than a more realistic one.

The only answer I see to my question is :

"However in a book form you would add a combo box using the combo box Wizard to select an authorID from the Author table."

This is exactly what I want to do, but I don't know how.

I tried to add the combo box on my book form (in Design View), the Combo box Wizard opens, I select the table Author then I select LastName and FirstName as the fields for the combo Box, I sort the list by LastName and set the width of the columns then click on Store that value in the field AuthorID and then Finish (I'll change the Label directly on the Design View)

But I do not want to store LastName:FirstName in the field AuthorID but AuthorID in AuthorID.


I am lost.

But I do not want to store LastName:FirstName in the field AuthorID but AuthorID in AuthorID.

You'd set up a combo box like this:

ControlSource:   AuthorID

RowSource:     SELECT AuthorID, FirstName & " " & LastName FROM Authors ORDER BY LastName, FirstName;

BoundColumn:   1

ColumnCount:    2

ColumnWidths:  0cm

Alternatively, you can concatenate the names so that the last name is first, which would be better with a large list of names as it allows the user to enter the initial characters of the last name and progressively go to the first match as each character is entered:

RowSource:     SELECT AuthorID, LastName & ", " & FirstName FROM Authors ORDER BY LastName, FirstName;

Thank you so much for this answer.

Is there a way to put a combo box on a form without going through the Combo Box Wizard, then ?

Because I clicked on the properties of my combo box (obtained through the Wizard), I added the concatenation of LastName FirstName (much clearer) set column width to 0cm (Autofit ?), nb of coloumn to 2, check the control Source (which seems to be set to ID by default by the wizard...)...

And it worked perfectly, thanks again.

But is there a way to get it right from the beginning instead of correcting the result of the Wizard ?