Building a form from a Juction table

[Vav]

I was wondering how you build a form off a junction table.

Meaning if the Junction Table, holds info on customers and book for exaple the fields in the juction table (tbl_customer_book) were customer_book_id , Customer_id and Book_id.

If I create a form called frmOrders which will track customer info and book info, how am i supposed to set up the Record Source of the form?

For clarity purposes the form must be able to display more then just the Customer Id and the Book Id. I would need to display the customers address and contact info as well as the book info such as title and description.

But how and where is that info stored, if the form "Record Sourse" is set to the juction table "tbl_customer_book"?

There will be way more txt boxes on the form then the table will have fields.

I have set up the relationships between the three forms already, but am unsure of the procedure with saving the info off the form.

If anyone can help it would be great.

VAV

 

[Rob.Mills]

I'm a little confused by what you're asking. Are you saying you have two tables, one has customers and the other has books? And you want to make a form that has a recordsource that combines the two tables. Let me know if I'm right on that.

 

[llkhoutx]

Use a query as the recordsource for your form, connect tables to your junction table to display the fields you want.

where

tbl_customer_book
customer_book_id
customer_id
book_id

tbl_customer
customer_id
scustomer
etc.

tbl_book
book_id
scustomer
etc.

You form recordsource query would be:

SELECT tbl_customer_book.customer_book_id, tbl_customer.scustomer, tbl_book.sbook
FROM (tbl_customer_book
INNER JOIN tbl_customer ON tbl_customer_book.customer_id = tbl_customer.customer_id)
INNER JOIN tbl_book ON tbl_customer_book.book_id = tbl_book.book_id;

Build the foregoing with the QBE frame and save it (qryJCNRecordSource) or use the sql string as recordsource for your form.

This ought to get you started.

 

[Vav]

Correct

Except i think it might actually help if i gave you the whole picture.

************************************
First off

Forget my original example, that was just dummy name of tables and forms. (I was trying to make it easier for people to understand)

************************************

My dilema is such:

I am making a database for a company which has many different products. Each product is made up of many components. (These components can be used in many of the different products as the parts are interchangeable.)

Therefore instead of the user having to type in the different components for the products each time, which would be very repetative and time consuming. I built a form where they can enter the Components (with part number and description) just once.

They are then able to go to the Product form and select from a combo box each component that makes up that particular product.

(Follow so far?)

Each component may or may not be manufactured in house, as some of the components are outsourced to be developed.

Similar to the components form, i have built a form that you can enter all the outsourced manufatures (ie address, contact person etc.)

Simply put What i want is to create a form that will allow me to select a component and asign a manufacture to it. The complication comes in that i want to build it with the functionality that one component might in the future be manufactured by more than one manufacture at the same time.

I am not sure how to do this in a mindfull database space manor.


What i have created so far is the three tables: tblComponents, tblManufactures and a third table being a junction table tblComponents_Manufactures which are related to each other. The fields in the junction table are:

components_id
manufacture_id

What is my next step? How do i create the form inorder to assign a manufacture to a component, and how do i get the info to store in the right tables.

Do i need to make the record source of the form based off

- a query?
- several tables?
- or both?



I hope this makes it clearer....

VAV

 

[RichMorrison]

"Junction table" imples a many to many relationship. It sounds like you want Customer to Book(s) and Book to Customer(s).

IMO the best way to do this uses listboxes on a form. One list displays Books. You click on a Book in the list and see related Customers.

Another list displays Customers. You click on a Customer and see related Books.

Right ?

You can put a Customer to Book pair of listboxes on one form and a Book to Customer pair on another form. Or you can put both sets on one form although this is visually confusing to most users.

Implementing either list pair is about the same:
1) make a query that select ALL Customers and use the query as the Recordsource of the list
2) make a second query that joins the junction table to the Books table. This query contains a criteria that refers to the Customer list. Use this query as the Recordsource for the Books list.
3) make an OnClick event sub for the Customer list that requeries the related Books list

For Book to Customer you essentially reverse the actions listed.

HTH,
RichM