Hi all, I hope someone can help. I was meant to complete this for tomorrow but have hit a snag
I'm creating a form to process customer orders. It uses qselUnprocessedOrders. I have used expressions in a query to calculate the total weight of an individual order, which I need in the form frmOrderProcessing, to calculate the total weight of multiple orders for delivery (using checkboxes). The form itself displays everything I need it to about orders, such as the customer details, the order details and the order weight, using continuous forms.
The trouble is that now I have added the query which calculates order weights (qselOrderWeights) to this query for the form (qselUnprocessedOrders), the query is no longer updatable! I have verified it and removing the OrderWeights (as qselOrderWeights) allows all fields in qselUnprocessedOrders to be updated again. So I know this is a problem with that query and fields being reused for the expressions etc, but I don't know how to fix it.
The annoying thing is that I didn't need the query/form to be updatable except for one field! The field 'ProcessedForDelivery' is a yes/no field and is what is used as criteria to select orders in qselUnprocessedOrders.
The idea was to display unchecked orders in the form, then select the ones you wanted and a text box at bottom of form would total the order weights to see how much could be taken on a van. These would then be sent to a report and the form re-queried to remove those orders (now marked as yes).
My table structure for this part of the database is as follows:-
qselCustomerDetails
CustomerNumber (PK)
Name
Surname
Street
Postcode
etc etc
tblCustomerOrders
OrderNumber (PK)
CustomerNumber
OrderDate
TotalCost
DeliveryDate
tblCustomerOrderedItems
OrderedItemID (PK)
OrderNumber
ProductID
Quantity
tblProducts
ProductID (PK)
Product
ProductWeight
qselUnprocessedOrders uses qselCustomerDetails and tblCustomerOrders to bring together the customer details with order number, cost, order time etc. But then I needed to add order weight.
qselOrderWeights uses tblCustomerOrderedItems and tblProducts as follows:-
OrderedItemID, OrderNumber, Product, Quantity, ProductWeight. It then has a column for Quantity*ProductWeight. Then Sums that column and groups by order number. I was really pleased when I worked this out but now its giving me trouble
Any ideas how I can fix this so I can select the orders in frmOrderProcessing? Sorry for long post and many many many thanks for any help!!
I'm creating a form to process customer orders. It uses qselUnprocessedOrders. I have used expressions in a query to calculate the total weight of an individual order, which I need in the form frmOrderProcessing, to calculate the total weight of multiple orders for delivery (using checkboxes). The form itself displays everything I need it to about orders, such as the customer details, the order details and the order weight, using continuous forms.
The trouble is that now I have added the query which calculates order weights (qselOrderWeights) to this query for the form (qselUnprocessedOrders), the query is no longer updatable! I have verified it and removing the OrderWeights (as qselOrderWeights) allows all fields in qselUnprocessedOrders to be updated again. So I know this is a problem with that query and fields being reused for the expressions etc, but I don't know how to fix it.
The annoying thing is that I didn't need the query/form to be updatable except for one field! The field 'ProcessedForDelivery' is a yes/no field and is what is used as criteria to select orders in qselUnprocessedOrders.
The idea was to display unchecked orders in the form, then select the ones you wanted and a text box at bottom of form would total the order weights to see how much could be taken on a van. These would then be sent to a report and the form re-queried to remove those orders (now marked as yes).
My table structure for this part of the database is as follows:-
qselCustomerDetails
CustomerNumber (PK)
Name
Surname
Street
Postcode
etc etc
tblCustomerOrders
OrderNumber (PK)
CustomerNumber
OrderDate
TotalCost
DeliveryDate
tblCustomerOrderedItems
OrderedItemID (PK)
OrderNumber
ProductID
Quantity
tblProducts
ProductID (PK)
Product
ProductWeight
qselUnprocessedOrders uses qselCustomerDetails and tblCustomerOrders to bring together the customer details with order number, cost, order time etc. But then I needed to add order weight.
qselOrderWeights uses tblCustomerOrderedItems and tblProducts as follows:-
OrderedItemID, OrderNumber, Product, Quantity, ProductWeight. It then has a column for Quantity*ProductWeight. Then Sums that column and groups by order number. I was really pleased when I worked this out but now its giving me trouble
Code:
SELECT tblCustomerOrderedItems.OrderNumber, Sum([Quantity]*[ProductWeight]) AS [OrderWeight (Kg)]
FROM tblProducts INNER JOIN tblCustomerOrderedItems ON tblProducts.ProductID = tblCustomerOrderedItems.ProductID
GROUP BY tblCustomerOrderedItems.OrderNumber;
Any ideas how I can fix this so I can select the orders in frmOrderProcessing? Sorry for long post and many many many thanks for any help!!