Trouble with Combo Box

[RobertJohn]

Hi all.

I am a beginner using Access 2007 to try to develop a small home library application to store details of my book collection. So far I have three tables as follows:

Table 1 Books has fields Book_ID (AutoNumber, Primary Key) and Title.
Table 2 Authors has fields Author_ID (AutoNumber, Primary Key), First_Name and Last_Name.
(These will be extended to include details like Publisher, fiction or non-fiction etc when I get my basic structure right.)
Table 3 Books_Authors with fields Book_ID and Author_ID (both Number fields and both Primary Keys).

This third table (I think it is called a junction table) is there to enable a many-to-many relationship between Books and Authors, as one author can have several books and one book may have several authors. I have created a one-to-many relationship between Books and Books_Authors on the Book_ID field, and a similar one-to-many relationship between Authors and Books_Authors on the Author_ID field.

I have developed a data entry form based on my Books table which allows me to enter a book title and then go to a subform to enter author details for that book. That works fine. But because I have several books by the same author I don't want to have to enter the author details every time. Instead when I get to the Author subform I would like to be able to use a lookup table in a Combo box, (based on my Authors table) using the author's full name { [First_Name] & " " & [Last_Name]} so that I can just click on the author I want. If the author I want is not on the list already I will use a button on the form to take me to another form to enter a new author. That part is OK.

I was sent a sample database which was very similar to the one I want and it worked OK. However when I tried to replicate it, by building it up from scratch, I was unable to achieve the same result. As I am trying to learn about Access I don't want to just copy someone else's database, although I thought at the time that I would be able to re-create what I was sent. Unfortunately that didn't work out.

What I need now is for someone to walk me through the steps in creating the form and subform to achieve the desired result.

I presume that I will need to create a query in order to use the Full Name
"field", but should that query be based on fields only from my Authors table, or do I need to use one or both of the fields from the junction table (Books_Authors)? I am also confused about the various settings for the combo box.

Can anyone help, please?

Thanks in advance

Robert

 

[jzwp22]

It sounds like you are on the right track. Your main form should be based on the book table. The subform should be based on the junction table (not the author table). You would then use a combo box in the subform to select the author. The combo box would be based on a query that, in turn, is based on the author table. I would also recommend having the author's name displayed with the last name first and then order them by last name so that the combo box displays the names in alphabetical order by last name not first name. So your query may go something like this:

SELECT authorID, [Last_Name] &" "& [First_Name] as Author_Name
FROM tblAuthors
Order by [Last_Name] &" "& [First_Name]

With regards to the settings of the combo box, you want the bound column to be the authorID since this is what you want stored in your junction table. You do not need to display the authorID since your users should not care what it is. Look for the column width property of the combo box. You would set it like this 0";2" A zero tells Access not to display that field, and the 2" tells Access to display the second field and allow 2 inches for it. (This assumes that you are using inches for your database settings).

 

[RobertJohn]

jzwp22

Thanks for the quick reply.

I have just tried it and I managed to make it work. At last, after so much frustration!!!

Thanks again.

Robert.

 

[jzwp22]

Glad I could help out. Good luck on your project!

 

[RobertJohn]

I thought I had this sorted, but not so!

I am happy with the project so far, but now want to extend it. As well as the Books, Authors, and Books_Authors tables, I now want to include a Volumes table. This is to allow for physical books which contain more than one title.
These would include so-called omnibus editions which have several titles (in my terminology, "Books") by the same author, and also editions like the Readers Digest sets of condensed books which have several titles ("Books") by different authors in the one volume.

So I have added a tblVolumes with fields Volume_ID (Autonumber, Primary key), Title (for example "The Complete Sherlock Holmes collection"), and other fields to do with the publisher of the volume. Because there is a link between Volumes and Books I have created another junction table tblVolumes_Books with fields ID (Autonumber), Volume_ID and Book_ID (both primary keys), and set up the relevant one-to-many relationships to create a many-to-many relationship between Volumes and Books (since one Volume may have several Books and one Book may appear in several Volumes.)

Where I come unstuck is in the data entry form (again!!). I am trying to use a form, based on the Volumes table, with a subform (the subform being the main form, with its own subform, that I used in the first part of this project, ie the Books form with its Authors subform.) It all works except that I don't get anything stored in my Volumes_Books table.

I would once again appreciate any help with this matter. There is clearly something which I am not understanding about these links between tables.

Thanks in advance for any help.

Cheers

Robert

 

[jzwp22]

You would tie your form to the book-volume junction table not the volume table. If you plan on entering mainly the book information and then tying it to a volume then you would use the book-volume form as a subform. If you plan on entering mainly the volume information and then tying it to the book, then your book-volume form would be the main form and your previous main form would be the subform.

 

[RobertJohn]

You would tie your form to the book-volume junction table not the volume table. .... If you plan on entering mainly the volume information and then tying it to the book, then your book-volume form would be the main form and your previous main form would be the subform.

Still having trouble. The help from jzwp22 has been wonderful (thanks again) but after several days of reading about relationships, forms and subforms and lots of trial and error (mostly error!!) I still can't get it to work.

My preference is to start with a form based on Volume information, eg Volume title, publisher name, date etc and then use my earlier form frmBooks (the one which let me enter a book title and one or more authors via a subform) as a subform on my Volume form. I have tried all sorts of combinations of fields from the Volumes-Books table and the Volumes table but I still can't get it all together. I can get everything to work except for the entries into the Volumes table, ie the form appears to work, but when I check the tables there is nothing in the Volumes table.

I would greatly appreciate it if I could get a bit more detail in the help already supplied.

Thanks in advance

Robert

 

[jzwp22]

The problem comes in with trying to enter new records when you are dealing with the volumes and the books belonging to those volumes. It is not a problem to just display the volume information in forms. So to do the data entry, you can have your users enter all the book information first and then tie them to the volume using a separate form/subform. But if I were a user, I would want to enter the volume info then enter the books and authors for that volume. I have been playing with a prototype DB in my spare time at work; it is attached. It does involve VBA coding.

Also, a volume is essentially a book, so it too would be in the tblBooks as well. I set it up like that in the attached DB. I added another field to identify whether a book was a volume or just an individual book.

Hopefully this will give you some ideas.

 

[RobertJohn]

Thanks again.

I did manage to get my database working, using a Volume-Book-Author form-subform-subsubform structure. It looks OK as far as data entry is concerned, but the crunch will probably come when I try to produce the reports.

I will look closely at what you have sent, and will probably then be able to make some changes to my work.

Thanks again.

Robert