Simple bound form question

[zakster]

Here is what I hope a relatively simple issue/question (first posted in the Queries forum under the title QBF Issues, but is more suitable here):

I have three tables:
tblContact - contact information
tblQuote - quote information (links to contact)
tblProduct - product information (links to quote)

Each product has an associated quote, and each quote has an associated contact.

I am trying to develop a form (frmProduct) for tblProduct. At the moment I have frmProduct bound to tblProduct.

I don't have any issues displaying all the normal tblProduct fields in frmProduct. But I would also like to display the contact associated with the Product (i.e. through the product->quote->contact link). This would not be an editable control on the form.

I have come to the conclusion that I really do not know the correct way of doing this.

Can anyone lead me in the right direction for this.

Thanks a heap,
Alicia.

 

[Rabbie]

You should base your form on a query that gets its data from your tables. This means you will get the correct contact. It would be helpful if you could post a cutdown version of your DB so we can point you in the right direction without too many generalities.

 

[zakster]

Thanks for the reply Rabbie.

I tried this earlier - but then I couldn't seem to edit any of the product information. When you use a query as the data source are you still able to edit the underlying records?

Thanks,
Alicia

 

[Rabbie]

It all depends on whether your query will allow updates. if you open your query in datasheet mode then if you are able to add a new record you should be able to change the product info.

This will depend on the way your query has been written. If you look at your query in SQL mode and post that here I can have a look at it for you.

 

[zakster]

Thanks again Rabbie.

The SQL code is as follows:

SELECT p.*, c.CompanyName
FROM Product AS p, Quote AS q, Contact AS c
WHERE p.Quote = q.ID AND q.Client = c.ID

I want the user to be able to update everything from the product table, but not the chrCompanyName field from the contact table.

Thanks,
Alicia.

 

[Rabbie]

Your query should look like

SELECT P.*, C.ContactName
From Product AS p, Qupote as Q, Contact AS c

(C INNER JOIN Quote ON C.ID = Q.ID) INNER JOIN P ON Quote.Client = P.ClientId;

 

[zakster]

YAY!!!!

The joins worked! Thank you so much for helping out.

Now two more questions

(1) Why do you need to use joins in order for editing to be allowed?
(2) I have a very similar form that I am building but I would need a conditional join in order for it to work,

The following tables exist:

Table Job
- OrderItem
- Quote

Table Quote
- ID
- Client

Table Order
- ID
- Client

Table OrderItem
- ID
- Order

Table Contact
- ID
- CompanyName

Basically I want to develop a form for the Job table - the only thing is that a job will EITHER have an order item
attached to it or a quote. So what I tried to do is the following SQL statement:

SELECT Job.*, Contact.chrCompanyName, Contact.chrContactName, Contact.chrEmail, Contact.chrPhone, Contact.chrFax, Contact.chrMobile
FROM Contact INNER JOIN (Quote INNER JOIN Job ON Quote.ID=Job.Quote) ON Contact.ID=Quote.Client
WHERE Job.OrderItem Is Null

UNION

SELECT Job.*, Contact.chrCompanyName, Contact.chrContactName, Contact.chrEmail, Contact.chrPhone, Contact.chrFax, Contact.chrMobile
FROM Contact INNER JOIN (Order INNER JOIN (OrderItem INNER JOIN Job ON OrderItem.ID = Job.OrderItem) ON Order.ID=OrderItem.Order) ON Contact.ID=Order.Client
WHERE Job.Quote Is Null;

This brought up the correct data, but it did not allow editing.

Again - thank you

Alicia.

 

[zakster]

Any advice???

 

[Rabbie]

Union Queries are not updateable so thats why you cant edit.

(1) Why do you need to use joins in order for editing to be allowed?

because in that case you were using data from two tables and it needs to know the realtionship between the tables as defined by the JOIN