Order Form Wont work

[Mark12345]

Hi folks, thanks for looking. I have a database that takes down order details.
Each customer has there own priceid in the price table.
the errors that I get say enter parameter value for:
products.unit price
Unit Price
Discount Price

Maybe a bit of history would help with this, the user said originally that they wanted to be able to give customers a discount on every line of stock.
Then realised after completion that this is no good for there requirments. The only option is to give each customer there own price list.

I have the structure
tblCustomers
CustomerID Autonumber and key
then other non relevent field at the mo

tblOrderDetails
OrderDetailID Key
OrderID
ProductID
Quantity
Price

tblOrders
OrderID Key
CustomerID
OrderDate
PurchaseOrderNumber
ShippingMethodID
Payment Received
Comment

tblPrice
PriceID Key
ProductID
Price
CustomerID

tblProducts
productID Key
ProductName
Cost

Im really stuck here, my form wont work

The Record Source for the AddNewOrderandDetail form reads:
SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate, Orders.PurchaseOrderNumber, Orders.ShippingMethodID, Orders.FreightCharge, Orders.PaymentReceived, Orders.Comment, Customers.Address FROM Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID;
The Record
The Record Source for the AddOrderDetail is:
SELECT OrderDetails.OrderDetailID, OrderDetails.OrderID, OrderDetails.ProductID, OrderDetails.Quantity, OrderDetails.Discount FROM OrderDetails; .

I got to deliver this in the morning HELP PLEASE

Thanks
Mark

[Mark12345]

Hi guys I didnt mention I got to have this done for the morning.

Thanks
Mark

[Rabbit]

Well first thing is that your record source doesn't reference your tables correctly.
Your table is named tblOrderDetails NOT OrderDetails. And the same goes for the other record source.

[Mark12345]

This is the area I cant get into, im a newbie and I dont know how to write the correct details
Im gonna check through them, im not sure the queries are set up properly.

Thanks
Mark

[Mark12345]

Hi I have changed the record source for the addNewOrder to

I have changed the OrderDetailSubForm to


I haev gone through the queris and changed the sql to the values below headed by query name.

Query Add New Customer and Order Details

SELECT tblOrders.CustomerID, tblOrders.OrderDate, tblOrders.PurchaseOrderNumber, tblOrders.ShippingMethodID, tblCustomers.VATTaxRate AS Expr1, OrderDetails.OrderID, OrderDetails.ProductID, OrderDetails.Quantity, tblProducts.UnitPrice AS Expr2, OrderDetails.Discount AS Expr3
FROM (tblCustomers INNER JOIN tblOrders ON tblCustomers.CustomerID=tblOrders.CustomerID) INNER JOIN (tblProducts INNER JOIN OrderDetails ON tblProducts.productID=OrderDetails.ProductID) ON tblOrders.OrderID=OrderDetails.OrderID;

Query Add New Customer
SELECT tblCustomers.CustomerID, tblCustomers.CompanyName, tblCustomers.FirstName, Customers.LastName, tblCustomers.Address, Customers.[Post Code], tblCustomers.PhoneNumber, Customers.FaxNumber, tblCustomers.Notes, Customers.VATTaxRate AS Expr1
FROM tblCustomers;

more to come in a min

[Mark12345]

This is the record source within my AddAnOrder Form reads. tblOrders.FreightCharge, tblOrders.PaymentReceived, tblOrders.Comment, tblCustomers.Address FROM tblCustomers INNER JOIN tblOrders ON Customers.CustomerID=tblOrders.CustomerID;

Thanks
Mark

[Rabbit]

This is the record source within my AddAnOrder Form reads. tblOrders.FreightCharge, tblOrders.PaymentReceived, tblOrders.Comment, tblCustomers.Address FROM tblCustomers INNER JOIN tblOrders ON Customers.CustomerID=tblOrders.CustomerID;

Thanks
Mark

So... does that mean you've solved the problem?

[Mark12345]

I solved one problem which was when I opened the db I got an error screen, but that just required me to change the details in the startup menu area.

The rest of the problems still exsist.
When I go into my db I get Syntax error in join opperation.
I can only get into my form in design view.

Can I send it to you so you can tell me what I need to do.
Thanks
Mark

[Rabbit]

Well you're joining Customers.CustomerID when you don't have a Customers table. Like I already said in a previous post, it should be tblCustomers.