Help pls my forms wont work 7 am deadline please help

• • Reply

  13 years ago

  by mark12345

  Mark Caine

  , United Kingdom

  Joined 13 years ago

  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
  OrderDetaiID 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 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
  
  
  

  Report abuse
• • Reply

  13 years ago

  by Kassem

  Kassem Baalbaki

  Riyadh, Saudi Arabia

  Joined 14 years ago

  Hi,

   

  The problem you are facing is not relevant to information you have provided as fields generating errors are not mentioned in your input (Unit price??). In all cases this problem is resulted from the query the form is based on and not from the form it self. To test it copy the query syntax to the query design sql view and run it this shall give you a clear image where the error is.

  Feed me back with what happens.

  I hope I will be able to help you.

  Report abuse
• • Reply

  13 years ago

  by mark12345

  Mark Caine

  , United Kingdom

  Joined 13 years ago

  Hi the prob;em starts with the Addnew ordre & details form.

   

  I have looked at the sql for my queries

  AddNewrderForm

     SELECT Orders.CustomerID, Orders.OrderDate, Orders.PurchaseOrderNumber, Orders.ShippingMethodID, Customers.VATTaxRate AS Expr1, OrderDetails.OrderID, OrderDetails.ProductID, OrderDetails.Quantity, Products.UnitPrice AS Expr2, [OrderDetails].Discount AS Expr3
  FROM (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN (Products INNER JOIN OrderDetails ON Products.productID = OrderDetails.ProductID) ON Orders.OrderID = OrderDetails.OrderID;
  
  
  

  for the price query sql reads: SELECT Price.ProductID, Price.Price
  FROM Price;
  

  TotalPriceOfOrderDetails sql reads         

  SELECT OrderDetails.OrderID, Customers.CompanyName, Products.productID, Products.UnitPrice AS Expr1, OrderDetails.Quantity, CCur(IIf([Quantity]=0,1,[Quantity])*[UnitPrice]) AS SubTotal, OrderDetails.Discount AS Expr2, CCur([SubTotal]-[Discount]) AS [SubTotal With Discount], Customers.VATTaxRate AS Expr3, CCur([SubTotal With Discount]*(1+[VatTaxRate])) AS [Total Price], Customers.CustomerID
  FROM (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN (Products INNER JOIN OrderDetails ON Products.productID = OrderDetails.ProductID) ON Orders.OrderID = OrderDetails.OrderID
  ORDER BY OrderDetails.OrderID;
  
  
  
  

  Total Price of order sql: SELECT [Total Price of Order Details].OrderID, [Total Price of Order Details].CompanyName, Sum([Total Price of Order Details].SubTotal) AS [Order SubTotal], Sum([Total Price of Order Details].[Total Price]) AS [SumOfTotal Price], [Total Price of Order Details].CustomerID
  FROM [Total Price of Order Details]
  GROUP BY [Total Price of Order Details].OrderID, [Total Price of Order Details].CompanyName, [Total Price of Order Details].CustomerID
  ORDER BY [Total Price of Order Details].CompanyName;
  
  There ar more queries but theres a large cumk here.
  
  
  
  
  

   

  Thanks

   

  Mark

  Report abuse
• • Reply

  13 years ago

  by mark12345

  Mark Caine

  , United Kingdom

  Joined 13 years ago

  I know nothig of sql, just thought I would mention it

   

  Thank

  Mark

  Report abuse
• • Reply

  13 years ago

  by mark12345

  Mark Caine

  , United Kingdom

  Joined 13 years ago

  I have gone through the queries and the forms makinng sure that all the tbl entries are in place.

  Still problems.

  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 tblCustomer
  
  
  
  
  
  
  
  
  

  Query Add New Order & Details

  SELECT tblOrderDetails.OrderID, tblCustomers.CompanyName, tblProducts.productID, tblProducts.UnitPrice AS Expr1, tblOrderDetails.Quantity, CCur(IIf([Quantity]=0,1,[Quantity])*[UnitPrice]) AS SubTotal, tblOrderDetails.Discount AS Expr2, CCur([SubTotal]-[Discount]) AS [SubTotal With Discount], tblCustomers.VATTaxRate AS Expr3, CCur([SubTotal With Discount]*(1+[VatTaxRate])) AS [Total Price], tblCustomers.CustomerID
  FROM (tblCustomers INNER JOIN tblOrders ON tblCustomers.CustomerID = tblOrders.CustomerID) INNER JOIN (tblProducts INNER JOIN tblOrderDetails ON tblProducts.productID = tblOrderDetails.ProductID) ON tblOrders.OrderID = tblOrderDetails.OrderID
  ORDER BY tblOrderDetails.OrderID;
  
   
  
  
  
  

  ---

  I still keep getting asked to enter a value for Unit Cost, cost, ect

   

  Thanks

  Mark

  Report abuse