Trouble with Book Database

[funkymuppet]

Hi,

I'm trying to build a book database (have previously posted on a fly fishing hook database, but gladly that is all working now - thanks to the excellent help from the forum) - and am sure this is a common challenge, but I cannot get the form entry to work.



I've structured the database right for my purposes, but am trying to build a form that allows entry of the book detail and then addition of existing author names - and because of the many-to-many relationship between Authors to Books, I am having difficulty. I want a form that lists the book fields, and then has a sub form in datasheet view format, in which I can select the Author names and have them create a record in the tblBookAuthor table assigned to that BookID.

The relationship structure is below - I've tried creating queries that aggregate all the fields needed, and also building a form with all the fields - but to no avail - if anyone had some quick suggestions I would be very grateful.

Thanks,

JW

[June7]

A form can enter/edit data for only one table.

You already indicated using a subform so I am not sure what your confusion is about.

Main form bound to tblBook.

Subform bound to tblBookAuthor.

Use a combobox on subform to select author.

If what you want is to also enter a purchase record for the book, that does complicate.

[azhar2006]

Hello
The solution is to set up a switchboard
Add buttons with their designations
Propagation Forms
You can not create only one query per tables
You can see this
http://office.microsoft.com/en-us/te...001018643.aspx

[orange]

I agree with June7's approach. What exactly is the difficulty? Data model seems very well done.

Good luck with your project.

[funkymuppet]

Hi June7/orange - thanks. I got the subform working, so I can now enter new book details and in going to the subform, can pulldown the combobox to select an Author, storing the AuthorID in tblBookAuthor - all works great.

However, what I can't figure is a way to show (once an Author is selected) any more than the last name of the Author in the subform - I'd like to be able to check real-time the other details of the Author selected in each row/record of the subform - like first name, nationality and other details that I begin to add. I've tried adding a TextBox, but can't link it to data on another table - and then starting trying with a query, but can't link that easily either - is there any quick recommendation you can give me?

Sample of the pull-down in the combo box:


Sample of the full frmBook sheet, showing the subform:

[orange]

Adjust the rowsource of the combo to a query with all the fields you want/need.

[funkymuppet]

orange - thanks, but I think that is for showing all the related fields when the combo box is being pulled down? What I was looking to do was add columns to the subform itself, so that when AuthorID has been selected and the cursor focus moves on to something else, the cells to the right of the combobox are filled in with data from tblAuthor that relates to the Author ID selection that was made - does that make sense? And does the solution above apply for doing that?

[June7]

Options for displaying related author info:

1. include related author info as columns of combox, textboxes refer to columns of combobox

2. include tblAuthor in the form RecordSource, join type "Include all records from tblBookAuthor and only those from tblAuthor that match - bind textboxes to the tblAuthor fields and set them as Locked Yes, TabStop No

3. DLookup()

[Bob Fitz]

Adjust the rowsource of the combo to a query with all the fields you want/need, as orange instructed in post #6.
Create a column for each piece of data that you need to display.
Set the Control Source property of these to
= ComboName.Column(2)
The number in ()'s would need to be changed for each form column because it is used to reference the column of the combo box that has the required data.
Remember though, that the combo box column count is zero based, so the first column is (0), the second is (1) etc.
The columns of the combo box can be hidden, if required, by setting the Column widths property to 0cm.

[funkymuppet]

Hi all - realized that, after having solved the problem with the help here, I never got back to confirm. All works great - solution was:

1. The subform has BookID as the Master and Child Fields
2. In the subform (Datasheet view) I have a combo box that picks the Last name, and that shows all the fields so I can check the right last name
3. And then the other two fields (text boxes in design view, that translate to columns in datasheet view) are filled automatically by

   =[ComboAuthorID].[Column](X)

   etc. in the Control Source property.
4. The intermediary form is then completed automatically with a record for each match between BookID and AuthorID

The resulting form is below.
Thanks again everyone,
JW