Form Design with a Junction table- HELP PLEASE

[brucey54]

Hi Folks, need some help here trying to understand how to create a form with a Junction table. This design will allow a book to have more than 1 author.
Author table
Author ID
AuthorName

AuthorID table (Junction table)
AuthorFK
BookFK

Book Table
BookID
Book Name

Author table has a 1 to many relationship with AuthorID table and Book table has a 1 to many relationship with AuthorID table.
Now how do you create the forms? Do I need a main form FrmAuthor , subform FrmBook and a frmAuthorID - HELP

 

[pbaldy]

I would have a form bound either to the Author or Book table with a subform bound to the junction table. The master/child links would be set appropriate to whichever table the main form is bound to.

 

[brucey54]

If I have the Author table bound to a main form and the junction table bound to a subform with the master/child link set appropriate.
How would I add new book s onto the system via the form? Do I create subformbook and add it to the main form???

 

[jdraw]

I agree with Paul and would also suggest you consider renaming your junction table to something more meaningful such as tblAuthorOfBook.

good luck

 

[brucey54]

Hi jdraw, point noted but how would I add new books onto the system via the form, see post above.

Hows the weather in Florida, I'm from Scotland and its freezing/Raining

 

[pbaldy]

I would be using a combo in the subform to choose books, so I'd use the not in list event of that combo to add new books. Searching here on "notinlist" should turn up the 2 common methods.

 

[brucey54]

Hi Paul, do have a database example or a link to one that I could study with a forms and juction tables?

 

[jdraw]

Weather here is about 80 F 6:50PM EST. I know about the freezing rain (and snow). In Ottawa they're expecting 3-5 " of snow overnight.

 

[pbaldy]

Hi Paul, do have a database example or a link to one that I could study with a forms and juction tables?

I can't think of one offhand, but if you get stuck on yours post it and we'll sort it out.