Forms - ComboBox - Sort/Edit Tables

[farner]

Hi,



So I'm looking to make a book collection database to catalogue my books. Nothing fancy, just books, their authors, and some minor details.

So far I have two tables:

Books:

-Title
-Author
-Translator
-Editor
-Pages
-Copies

Authors:

-Author (Primary Key)
-Author Title
-First Name
-Last Name

There is a one-to-many relationship with referential integrity from Authors.Author --- Books.Author

So just to clarify, I know absolutely nothing about writing code or macros or anything like that. The most I've done is play around a bit.

I'm hoping to be able to input my books, and sort them by author. I have a form that has the Books table as a subform, with an unbound combobox that selects the different records (authors) from the Authors table. The Link Master is Authors.Author and the Link Child is Books.Author

What I'm trying to do is have a combobox at the top of the form that allows me to input new authors when I need to and then input the book underneath, and when I want to choose a different author, I can select from the drop-down list in the combobox and the record will move to that selection, showing me the books for the selected author. Being able to also edit authors would also be convenient.

I did this once with an attendance database, but that was a while ago and I forgot everything

Other issues I'm having is regarding updating the form to show new authors and their records in the combobox. I know that it requires some programming (I've been googling) to requery or something like that.

Any help would be much appreciated. I've tried to attach my db but it's over 1 mb in size and won't let me.

[June7]

Run Compact & Repair, zip db, 2mb zip allowed.

Combobox used to input search criteria must be unbound.

Put code in the main form Current event: Me.comboboxname.Requery

You might need a many-to-many relationship if you want to allow books with multiple co-authors. This requires third 'junction' table that associates authors with books.

[farner]

Thanks for the reply June7!

So the combobox is unbound, and I added the code, but if I go to input a new book (starting with the author in the combobox) it doesn't create a new record when I enter anything into the box and then move on to the linked subform.

But if I add the field I want to the control it breaks everything. I'm very confused.

I'm not too worried about more than one author now, and I made the many-to-many relationship before in my other database, but thanks for the heads up.

Attached is the zip file with the db ( thanks for the advice)

[June7]

I would not use Author name as pk/fk.

Unbound combo box will not save data because it is unbound and just selecting author from list will not cause a new record to be created

Need other controls in the main form bound to the fields from Author table. Those will allow you to view/edit/enter Author info.

You might want to set the subform to Datasheet or Continuous view.

Review http://office.microsoft.com/en-us/ac...010098674.aspx