Field Cannot be updated

[Dreamweaver]

I have a number of sub forms on a orders form and also a PoOrders form
My Problem is when I do the following I get the message in title but after oking the message the field Updates without a problem
But this only happens:

1. When I use an exppression in The data query like
   1. Round(([Price]*[Qty])-([Price]*[Qty])*[Discount],2)
   2. ([Price]*[Qty])-([Price]*[Qty])*[Discount]
   3. And From Northwind 2000 CCur([Price]*[Qty]*(1-[Discount])/100)*100
2. Or If I include another Query to get product the details Like Below:

SELECT tblProducts.ProductID, tblProducts.ProductName, [UnitName] & " (" & [tblProducts].[Qty] & ")" AS Units
FROM tblProducts INNER JOIN tblUnits ON tblProducts.UnitID = tblUnits.UnitID;

It works Perfectly If I use the query below with field expressions like:

SELECT tblOrdersLineItems.OLineID, tblOrdersLineItems.OrderID, tblOrdersLineItems.ProductID, tblOrdersLineItems.Qty, tblOrdersLineItems.Price, tblOrdersLineItems.Discount, tblOrdersLineItems.Shipping
FROM tblOrdersLineItems;

1. =[SCbo_ProductID].[Column](2)
2. =[SCbo_ProductID].[Column](3)
3. =Round(([STxt_CostPrice]*[STxt_Qty])-([STxt_CostPrice]*[STxt_Qty])*[STxt_Discount],2)

But because of having to use the above I cannot sum the list and have had to use =IIf(IsNothing([OrderID]),0,Nz(DLookUp("BillTotal","QryOrdersTotals","[OrderID]=" & [OrderID]),0)) to get the Order Total

I will upload and post a link if needed

thanks mick

 

[The_Doc_Man]

Are you saying that you cannot perform an update of the field by inserting a value or are you saying that when you make a change elsewhere, the field being displayed does not update with a new value even though its aggregate function encompasses the changed value?

If you mean the second case, you need to somehow know that an update of a contributing value has occurred and thus you need to .REQUERY the form that holds the particular uncooperative value.

You COULD make the assumption that if you enter (Form_Enter event) the form after being in the sub-form, you always need to do the requery anyway. Look up the Form_Enter and Form_Exit events to see what I'm talking about.

 

[Dreamweaver]

Hi @The_Doc_Man Thanks Sorry I didn't manage to desribe the probem better

I have a order line items table with a combination Key OrderID, ProductID
When I start a new record by selecting the Product From the productID Combo After selecting but before updating the field a message pops up say the field cannot be updated but once I select OK The field updates.

I have tried everything I can think to correct it but the only way was to remove the calc from the query and stip it back to it's back elements as In OP

I have a work around which works but it's not the standard method for order items subforms.

 

[The_Doc_Man]

The question is whether the sub-form .RecordSource is unable to update because of relational integrity vis-a-vis the parent record, which probably hasn't been saved yet in the context you describe.

But to be honest, I'm guessing because your set up isn't 100% clear to me and I'm kind of whupped right now after taking my daily walk in the middle of a heat wave so I might not be firing on all cylinders.

 

[Dreamweaver]

No Problem You are right I haven't saved the form and will play tomorrow to see if that sorts it but as I said it works when the expessions are added to the controls and not the Query.

I have it working nicely at the moment as am just adding test data from the old northwind

 

[Dreamweaver]

As a after thought it can't be because the main record is not saved as I remember now when I was testing it I tried added records to old orders that had been completed it always happens when adding a new row.

 

[Dreamweaver]

I've now added over 120 orders and think I prefer my workaround to the normal way It seems a lot smother when adding record.

 

[Dreamweaver]

Rather than storing the calculated price, you should store the three fields:

Sorry pat but I never store calculated values.
The unit price in this instance come from an effective price list I.e query I can explain better if needed on that but on my tab at the moment.
I always store the unit price as they can chance at any time and mess the numbers up if not stored

doesn't have a discountPct option

As above the example uses an effective price list pic below.
The northwind gallery is Here

 

[Dreamweaver]

On each order detail line entry in this dB the following 3 values are saved Qty, discount% and the unit price plus the productID

 

[Dreamweaver]

No I'm still trying to figure out what your posts have to do with my OP