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.

 

[Pat Hartman]

You said your problem is resolved but here is some advice anyway.

Rather than storing the calculated price, you should store the three fields:
UnitPrice (which you copy from the item table when you select it in the combo)
Qty
DiscountPct

Then calculate the SalesPrice from these fields as unbound fields in the form/query.

To pickup the UnitPrice, add one line of code to the Beforeupdate event of the combo where you choose the item.

Me.UnitPrice = Me.SCbo_ProductID.Column(2)

The .Column(x) should refer to the price field in the RowSource of the combo. The combo's RowSource is a zero-based array so the first column is (0), the second is (1), the third is (2), etc.

The reason I store the UnitPrice rather than just referencing it in the price history table is because sometimes, you need to sell at something other than the UnitPrice and the Discount doesn't apply or your system doesn't have a discountPct option so you need to override, one time, the UnitPrice. For example, if you are replacing a defective item, the UnitPrice would be $0

 

[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

 

[Pat Hartman]

I did not suggest storing a calculated value. In fact, I suggested the opposite.

You are assuming that storing the unit price in the sales record is a violation of third normal form but it isn't. You are also assuming that the unit price can NEVER be overridden on the fly. A replacement order is a simple example. The replacement price is probably 0 or it could be anything if its reduced for wear. The price is a point in time attribute and rarely gets updated in the sales item table. One instance where it might be updated is if your business rules say that if a price is lowered between order and shipping, the lower price will prevail. To support this business rule, the FK method would require that the FK be changed to point to the new price record. That would look odd since the date on the price record would be > the date on the sales record. It would also require that an entry be made in the price history table for every one-off price if prices can vary at the point of sale. Think car for example. You start with list and you end up where you end up.

The one wrong solution is to store a FK to the item table where history is not kept and the price can be changed. So a record that when created had a price of $1.98 could end up with a price of $2.12 and $2.15 and $2.20, etc depending on when you look at the order.

Using a history table makes sense whether you choose to store the unit price or use a FK, just keep in mind that using a FK to the price is much more limiting than storing the price at the point in time in the sales record. And since storing the price doesn't violate 3nf, I almost always store it. You do analysis that tracks prices over time by using the history table. You do analysis of sales by using the sales item table.

 

[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

 

[Pat Hartman]

Did you really name the field discount%?

 

[Dreamweaver]

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

 

[Pat Hartman]

I was talking about your solution for storing the price as a FK to a pricing table and why I don't do it.