How to Keep the value of a calculated field before it changes again.

[ManuelLavesa]

Hello All, My problem is as follows I have a calculated field called BackOrders: Iff(QtyOrdered > QtyInStock, QtyOrdered-QtyInStock,0). the problem I have is that this value changes as soon as the QtyInStock changes due to a Purchase Order. And then I don't know which invoices had the backorder. Or how many items were in backorder. Is there a way of keeping the previous value. That is the value of the initial calculation. So that I can use it as a filter for finding the invoices that need the items. Or is there a better way of doing this. That is keeping track of backOrders. Thank you for your help and ideas.

[June7]

Use code to save the calculated value. The real trick is figuring out what event to put the code into and when you want to allow this to happen. If you only want to happen when the record is first created, like:

If Me.NewRecord And QtyOrdered > QtyInStock Then Me.BackOrder = QtyOrdered - QtyInStock

And possibly in the QtyOrdered AfterUpdate event. Posted code is VBA but there is macro equivalent.

[pbaldy]

If the fields are in the same table, the calculated field data type may be an option.

[ManuelLavesa]

Thank you both for your answers. I tried June 7 suggestion and unfortunately it is not working. this is what I did. I wrote the following code into the AfterUpdate Event of QtyOrdered.

PrivateSub QrtOrdered AfterUpdate()
Dim QtyInStock As Integer
QtyInStock = Me!cbofkProductID.Column(3)
If me.NewRecord And Me!QtyOrdered >QtyInStock Then me.Parent!QtyBOrder= Me!QtyOrdered - QtyInStock
End Sub

The QtyOrdered field is in a continuous form with other Fields which are: Product, QtyOrdered, Price, Discount, Subtotal. Product is in a combobox. The problem I have is as follows. under Normal circustances First I enter the Product, then the QtyOrdered, Then It multiplies Quantity X Price to calculate subtotal, and if there is a Discount it calculates it too. Now when I placed the code above in the afterUpdate Event. The following happens. I enter the product, then the quantity and when I press enter the subform goes blank. The QtyBOrdered displays the proper number. The tables don't record the values for subtotal expected it just places a 0. And when I try to exit it says that it can not save the record because somebody else has modified it. When I finally exit the value for the QtyBOrdered is not recorded on the table, it just says 0. If I remove the code then every thing returns to normal. Do you have any ideas of what could be causing this. Thanks for your help

[June7]

Why do you reference Parent?

Where is the QtyInStock value coming from?

If you want to provide db for analysis, follow instructions at bottom of my post.

[ManuelLavesa]

FinalDatabaseOrderSales3.zipI reference the Parent because I placed the field QtyBOrder in the Main form and not in the subform. I am enclosing the database so that you can see it. I left the code for QtyOrdered AfterUpdate Event. To find the form go to the button that says "New Sale Order" this will open a form with a subform. The QtyInStock comes from a query called "qryStokInhand" The values come from a union query called "qryStockMovements". All these queries seem to be working ok. To see the problem enter a company, then go to the subform and enter a Product. Check the quantity available which is displayed in the Product combobox column 2. Enter a QtyOrdered which is more than the stockInHand. And you will see that the subform goes blank. the QtyBOrder Field displays the right amount of backorder. But the whole thing becomes unusable... And it does not record the result into the table OrderDetails... Thank for your help I hope you can find what I am doing wrong.

[June7]

Why do you have Order and OrderDetails and Product tables in both the main and subforms? Conventional arrangement would be main form bound to Orders and subform bound to OrderDetails.

I suggest you simplify the RecordSource for each form before continuing.