Problem with my database

[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, this is due to an opertaional requirment. A group or line discount field is no good.

I Since creating my Price table I cannot acces the add order details subform to choose my products, and there prices.
I set the AddOderDetailsS ubform's Rowsource for the Products combo to be::p

SELECT DISTINCTROW Products.Produc tID, Price.Price
FROM Products INNER JOIN Price ON Products.Produc tID = Price.ProductID
WHERE (((Price.Custom erID)=[Forms]![Add an Order and details]![CustomerID]))
ORDER BY Products.Produc tName;

In the After Update event of the control I put
:eek:
Me.Price = Me.ProductID.Co lumn(2)
Im now getting an error where the Add order details subform is not working, I keep getting an sql error saying value after end.
I know nothing of sql/vb, I checked all the forms in the VB window but could not find any data after the end sub.
I am in a real pickle here guys and will be on here all night till I sort it.

I look forward to hearing from you.

Regards
Mark

[Denburt]

You have two columns you post isn't very clear but I do believe that you should be referencing column 1 as Me!roductID.Col umn(0) Column 2 as Me!ProductID.Co lumn(1)

Your statement should read:
Me!Price = Me!ProductID.Co lumn(1)

[Mark12345]

You have two columns you post isn't very clear but I do believe that you should be referencing column 1 as Me!roductID.Col umn(0) Column 2 as Me!ProductID.Co lumn(1)

Your statement should read:
Me!Price = Me!ProductID.Co lumn(1)

Hi I changed that thank you for the help.
What I have found is that when I go into my add order details form I keep getting boxes that as for a parametor value, im dont know why.
I think the db is complex, can you take a look and tell me what I need to do.
Trying to find errors when you cant identify them is proving a nightmare

Thanks
Mark
.

[Denburt]

O.K. first look at the recordsource and verify you can open that with no issues. Then make sure the controls on that form have a control source that match up with the fields in the recordsource. hope this helps.

[Mark12345]

HI thanks
I have checked the row source in the products combo box it currently reads
SELECT DISTISELECT DISTINCTROW tblproducts.Pro ductID, Price.Price
FROM Products INNER JOIN Price ON Products.Produc tID = Price.ProductID
WHERE (((Price.Custom erID)=[Forms]![AddanOrderandDe tails]![CustomerID]))
ORDER BY Products.tblPro ductName;.

can you see anything wrong with this.
The add new order form and the sub order form wont open without repeatdly asking me for certain values, like a search field.

I dont know if its my queries that are wrong, maybe that is why the forms dont work.
I have just made sure that all fields within my table have a value in them.

Dont really know where to go from here, I think the problems could be narrowed down to queries, forms, & onfiguration of events.

Thanks
Mark

[Denburt]

Right off I would suggest that you avoid naming a field the same as that of your table (Price.Price), that said.

The add new order form and the sub order form wont open without repeatdly asking me for certain values, like a search field.

Does it say "search" field on the prompt and are you sure it is this combo box? What exactly does the text on this prompt state. I am sure it probably has the name of a field it is looking for. Root around in the recordsource to start then hit all your combo boxes rowsources.

[Mark12345]

Right off I would suggest that you avoid naming a field the same as that of your table (Price.Price), that said.



Does it say "search" field on the prompt and are you sure it is this combo box? What exactly does the text on this prompt state. I am sure it probably has the name of a field it is looking for. Root around in the recordsource to start then hit all your combo boxes rowsources.

Hi 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 custoners a discount on every line of stock.
Then realised after completion that this is no good. The only option is to give each customer there own price list.

I have made big changes at the table level and the inherent errors are crawling can someone take a look at it and tell me what to do to sort it, pretty pls.

Thanks
Mark

[Mark12345]

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
PurchaseOrderNu mber
ShippingMethodI D
Payment Received
Comment

tblPrice
PriceID Key
ProductID
Price
CustomerID

tblproducts
productID Key
ProductName
Cost
If you r not getting the info you need can I send you copy of the db.
Thanks
Mark

[Denburt]

Open your order form in design view look at the properties section and view the recordsource. I am sure you will see several fields in there that contain what you are looking for. Such as "products.u nit price" change to COST and "Discount Price" remove or replace with necesary field name....

[Mark12345]

Open your order form in design view look at the properties section and view the recordsource. I am sure you will see several fields in there that contain what you are looking for. Such as "products.u nit price" change to COST and "Discount Price" remove or replace with necesary field name....

Hi it dsays
SELECT OrderDetails.Or derDetailID, OrderDetails.Or derID, OrderDetails.Pr oductID, OrderDetails.Qu antity, OrderDetails.Di scount FROM OrderDetails;

so im gonna edit it to

SELECT OrderDetails.Or derDetailID, OrderDetails.Or derID, OrderDetails.Pr oductID, OrderDetails.Qu antity, OrderDetailss;

[Denburt]

Uh it looks like you only grabbed part of that SQL statement.

[Mark12345]

Uh it looks like you only grabbed part of that SQL statement.

erm
Sorry you lost me.

[Mark12345]

The record source for the addorderdetails subform is
SELECT OrderDetails.Or derDetailID, OrderDetails.Or derID, OrderDetails.Pr oductID, OrderDetails.Qu antity, OrderDetails.Di scount FROM OrderDetails;
The record source for the order details subform is
Total Price of Order Details

Does this help.
thanks
mark

[Denburt]

Fix bold tags

so im gonna edit it to
SELECT OrderDetails.Or derDetailID, OrderDetails.Or derID, OrderDetails.Pr oductID, OrderDetails.Qu antity, OrderDetailss;

No way that will work.

First I asked for the recordsource form the Orders form and you posted:

SELECT OrderDetails.OrderDetailID, OrderDetails.OrderID, OrderDetails.ProductID, OrderDetails.Quantity[b], OrderDetails.Discount[/b] FROM OrderDetails;

It Should read:

SELECT OrderDetails.OrderDetailID, OrderDetails.OrderID, OrderDetails.ProductID, OrderDetails.Quantity FROM OrderDetails;

Then for the subform you pasted the same...
On your main form you would use the orders table the subform would have the details table. If you remove the bolded statement then you should look on the form for a control that has a controlsource to Discount and remove that also. I think we are making some headway.

[Denburt]

tblOrderDetails
OrderDetaiID Key
OrderID
ProductID
Quantity
Price

Is orderdetailID mispelled only in here or in your table also?