Urgent please? Expressions caused query/form to not be updatable. Only need one field (1 Viewer)

[Garindan]

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

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

 

[Garindan]

Don't worry, I have fixed it by using a DLookup in the query rather than the offending table

 

[boblarson]

By the way, look here for what makes a query non-updatable. And in this case the likely thing was the GROUPING. You can't have Grouping and have a query be updatable.

 

[Garindan]

Ok thankyou