Showing Fields & Creating Duplicate Problem

[DaniBoy]

HELP, So simple yet I can get it to work!!!

Hello, I am trying to do a simple invoice database!! Just 4 tables, Customers, Products, Invoice, Invoice Detail. When I make my form for the invoice everything seems to be ok, I pull up my customer and then I add products to the invoice. Fisrt problem is that I cant make the price show on the detail, second is that if i pick the same product twice it tell me that cannot make changes or save record because it will create a duplicate bla,bla,bla. Can someone take a look at this and tell me what am doing wrong?!! I know it has to be something stupid that I cant see!!!

Thanks
Daniboy

 

[digi_kiwi]

Hi, there's a couple of things you need to do. First, the table, tblInvoiceDetail will need a composite Primary key of (InvoiceID, InvoiceDetailID). InvoiceDetailID should probably be an autonumber. ProductID should be a foreign key not part of the Primary key. This will mean that each record in tblInvoiceDetail should be unique. Edit this in your table design and your relationships. (ie InvoiceID - InvoiceID, not InvoiceDetailID - InvoiceID).

To fix the price problem, drop price from tblInvoiceDetail and base your subform on a query between tblInvoiceDetail and tblProducts. eg.

SELECT tblInvoiceDetail.InvoiceDetailID, tblInvoiceDetail.ProductID, tblInvoiceDetail.Qty, tblProducts.ProPrice
FROM tblProducts INNER JOIN tblInvoiceDetail ON tblProducts.ProId = tblInvoiceDetail.ProductID;

I have attatched a fixed up version. I hope it helps.

 

[Mile-O]

Fisrt problem is that I cant make the price show on the detail, second is that if i pick the same product twice it tell me that cannot make changes or save record because it will create a duplicate bla,bla,bla.

Condensing that into a reasonable title would have been much better than HELP, So simple yet I can get it to work!!!. How do you know it's so simple, anyway?