Help needed in an order form in MS Access

[Matrouz]

I needed to create a database for my business, I’m almost done but I’m facing a problem in my 'New Order' form,
I'm using MS Access 2013, on windows 7.
I have created the following tables:

Products: (ID{PK}, Item, Volume, Quantity, Price)

Customers: (CustomerID{PK} ,Name , Phone, address, etc.)

Order: (OrderID{PK}, CustomerID{FK}, date)

OrderDetails: (OrderID{FK}, Item, Volume, Quantity, Unit Price, Price{Quantity* Unit Price})

I have created a form in which you insert the (Order) details and it includes a subform in which the source object is the table (OrderDetails), I can successfully add items, my problem is inserting the 'Volume' and 'Unit Price', which I want to be done Automatically source is 'Products' Table, because later on I want to add these together(Order Total).
I have tried messing with relationships but with no success.
Thanks in advance as I understand that this is a lot of info.

[NeoPa]

The simple advice is "Not to". IE. Don't store that data as it is generally not necessary. Actually, it's worse than unnecessary. It's a problem in itself.

See Database Normalisation and Table Structures for why storing the same information in more than one place can give you headaches you really don't need.

[Matrouz]

Thank you for your response, Ok i have corrected my tables and removed duplicate data, now i need someway to pull up the item info like Price and Volume into my New order form, then into an invoice, can that be done with a query/report or there is a better way of doing it ?

[NeoPa]

Queries will handle anything on a line by line basis - as can forms and reports - but queries are generally preferred at that level. Forms and reports can handle totals etc.

NB. Such items, quite naturally and correctly, are not updatable by the operator. Having said that, a query calculated field is updated automatically when one of the source fields that it calculates from, is updated.