Refresh field on another field recalculating

If I understand you correctly: Use the AfterUpdate event of Me.Price to run this. This event will fire anytime the value in [Price] is changed by the enduser.

After Insert *only* is triggered when a New record is added (aka inserted).

Try the Before Update event instead:

Private Sub Form_BeforeUpdate (Cancel As Integer)
Me!PriceEach = Me!Price
End Sub

mx

No, we actually use this in the AfterInsert of the form itself. The [Price] cannot be manually changed.

Thanks,

Before update doesn't do anything either... the PriceEach just stays the same as it was.

AfterInsert is 'after' the fact ... after a Record has been Saved.  Setting a value at this time will only result in the record becoming Dirty (edited) immediately ...

mx

The Form BU event triggers and time a Bound control has been changed.

"Before update doesn't do anything either."
Then something else is going on.

mx

DatabaseMX: I don't understand what you are saying; is this a statement or a suggestion to try?

Which post are you referring to ?

mx

AfterInsert is 'after' the fact ... after a Record has been Saved.  Setting a value at this time will only result in the record becoming Dirty (edited) immediately ...

mx

The Form BU event triggers and time a Bound control has been changed.

"Before update doesn't do anything either."
Then something else is going on.

mx

My suggestion was @ http:#a34439058  ...

If that is not working, can you upload the db?

mx

The DB is 7MB and I don't know what I can remove to make it still reflect the issue.

1) Compact & Repair ... that will shrink.
2) Zip up ... more skrinkage
3) explaining exactly how to reproduce the issue ...

mx

I think this needs to go on the AfterChange of the selector field on the main form; the Price and PriceEach fields are on the subform. How can I test/try this with the subform first?

Subform ?  No previous mention of that.  Do you mean On Change ?  Not really.  That event triggers once  for *every* character typed into a text or combo box.

mx

I guess I said the first field was determined by a query; this query is related directly to the value of a field in the main form. When that one field changes, it changes the price in every field in the subform.

If you can upload ... that would probably get to this faster ... even 7MB ... ?

mx

I really don't want to upload this for the world to see; is there an email I can send it to? Or a way to delete it once you get it?

eMail not allowed to individual users. You can requested via the Request Attention button that it be removed when done.

So, you tried:

Private Sub Form_BeforeUpdate (Cancel As Integer)
Me!PriceEach = Me!Price
End Sub

or as LSM suggested

Private Sub YourControlName_AfterUpdate (Cancel As Integer)
    Me!PriceEach = Me!Price
End Sub

?

< When that one field changes, it changes the price in every field in the subform>

Is that what the trouble is? Or do you want this to happen?

If this is NOT what you want to happen: IF your subform is a continuous or datasheet form you'll not be able to get around this unless you bind that control to a field. An Access form used as a continuous or datasheet form is basically the same control repeated x number of times, and Access simply fills each of those controls with values from the bound source. If you include an unbound control, then Access considers that the SAME control with the SAME datasource, and therefore will show the SAME value in all controls.

MX: I have tried these, please specify exactly what item I should attach the code to? The form? The field I want to change? The field that gets changed first?

"I have tried these, "

How did you try these if you don't know where to attach the code ? And, not sure what I can say where ... beyond what I've already shown.

This code goes in the FORM BeforeUpdate event, which ends up looking like this:

Private Sub Form_BeforeUpdate (Cancel As Integer)
Me!PriceEach = Me!Price
End Sub

and this code goes in whatever the control name is when you want to make this update happen:

Private Sub YourControlName_AfterUpdate (Cancel As Integer)
    Me!PriceEach = Me!Price
End Sub

mx

MX -
I have tried putting the code in to many different areas. I am asking for your explicit direction so I can just do it right instead of trying it in 20 different spots.

When I put your code into the PriceEach field (which is the name of the field we need to change when the other field is changed) it does nothing.

Attached is the database.

Go to frmMasterOrderForm
In there, you will see a form for entering order information with a subform below for entering the order data. Currently, it is working properly up to this point... the user chooses a Price Level and then enters their order, the detail defaults the Price/Override to the Price, but allows the user to Override it if they choose to.

What I need to happen is when the user changes the Price Level, that both the Price and the PriceEach default back to that price. This works for the Price right now (after you refresh the form), but the PriceEach just stays the same. I want the PriceEach to follow the Price, but still allow an override if necessary. 2011-SHOW-SEASON-ORDER-DB-1.mdb

I get the error message below when trying to open this db.

If you have an ACCDB upload that

mx
Capture1.gif

I don't know what an ACCDB is; we don't have anything special to open it?

What version of Access are you working in ?

If A2003 or prior, then you need to upload a working db.  I was unable to open the mdb you uploaded.  The error message sort of implies it was maybe created in A2007/10 and converted back to A2003 ...?

mx

Ok, yes, it was created in 2007 but saved as 2003 so more people could use it. Attached is it saved back to 2007. 2011-SHOW-SEASON-ORDER-DB---2007.accdb

ok ... I will look at it in a bit ...

mx

Sorry ... I don't see anything called Pricing Level ... do you mean PRICING combo box?

If so ... lead me through exact steps to reproduce the issue, including expected results ... referring to main form name, subform names ... etc., as appropriate.

mx

Mx-

In frmNewMain the item that changes is indeed the PRICING (PriceStructure) combo box. When this combo box is changed it will affect the PRICE box in the QryPriceLookup subform below. What the problem is, is that we also need it to update the PriceEach in the same subform. Right now, if you change the PRICING and refresh the form it correctly updateds the PRICE, but not the PriceEach. PriceEach needs to retain it's ability to be overridden with a manually entered price after the PRICING is changed.

To run a test, start with the PRICING set to Master Distributor, enter a new item AR02-B. The PRICE is 10. Now, change the PRICING to Volume Dealer and hit refresh... the price of that AR02-B changes to 20. This is all correct, but again, we just need the PriceEach to update to 20 as well.

Sorry to be a pain, but I don't see anything called 'PriceEach' ... do you mean Price/Override ?

And anytime I change PRICING to a different value, whatever row I'm in on the subform below becomes Dirty.  And if I'm a New Record row ... I get an error "The value you entered isn't valid for this field"  ... and the row for the New Record becomes Dirty.

mx

PriceEach is the table name for Price/Override.

Also, the only 2 pricing levels that have pricing for AR02-B are Volume Dealer and Master Distributor; i just entered those for the trial.

Thanks

any thoughts on this?

hi ... sorry, I had to leave for the Consumer Electronics Show in Vegas ... rtn sun eve.  have it in my out look calendar for sun night ... if still not resolved.

mx

ok, thanks

"To run a test, start with the PRICING set to Master Distributor, enter a new item AR02-B. The PRICE is 10. Now, change the PRICING to Volume Dealer and hit refresh... the price of that AR02-B changes to 20. This is all correct, but again, we just need the PriceEach to update to 20 as well."

Well, I sort of see the problem ... but, to me it's kind of a Catch 22.  For a given line item, how are you expecting to distinguish an actual Price Override from a preset value that matches PRICE per PRICING ?  
If I 'run the test' ... then for QTY 10, we have  $10 (PRICE), $10 (PRICE OVERRIDE), $100 (LINE TOTAL) across the row. But what if I do an OVERRIDE to $15, such that LINE TOTAL is not $150.  Then, change to Volume Distributor, which makes PRICE $20.  But you already have an OVERRIDE in effect, so ... would not make sense to change that to $20.

?

The PRICE is the base price for that specific order, once a customers total order reaches a certain thresh hold, the salesman may make a decision to upgrade them to better pricing. The override will be used in only very limited situations so what we really want is if the PRICING gets changed that it defaults everything on that order back to that new base pricing, regardless of whether or not an override has been used. Can you help with this?

Were you able to get this figured out after my last post?

Sorry ... so slammed ... have not forgotten about it, as well as a couple of other more complex Q's ...

mx

Ok, I just don't know what else to try and assume you had an easy fix just a matter of explaining why we do it that way.

Does anyone else have any suggestions on this since mx is busy? I need to have it functioning this week.

Sorry ... I will try to look at this tonight ...

mx

thanks!

Sorry again ... IT has refreshed several of the computers in our group to newer units ... and I have been slammed with fixing little issues.  Hope to have time tonight when I get home ...

mx

OK, thank you for the update... just REALLY need it by the end of the week if possible.

mx- I am desperate for this... I haven't explored any other options because you have been going to look at it for several weeks... do you have any suggestions?

thanks!

I am really sorry.  Extremely tapped out the last couple of weeks, not much time at home.  I can try and look again maybe tomorrow (sat).  In the meantime, you might want to hit the Request Attention button and as the mods to sent this out to additional experts to look at ...

mx

Try this. Two queries were added and event for price selection was changed 2011-SHOW-SEASON-ORDER-DB---2007.accdb

als315,

Thank you for responding, but when I open the frmMasterOrderForm, it keeps asking for a parameter value for Combo11. Did you test this from the frmMasterOrderForm or did you create something completely different?

Thanks,

I have not worked with this form. All changes were made in form frmNewMain. If you need to have it in frmMasterOrderForm, copy event from frmNewMain

I tried frmNewMain and it doesn't work either

Sorry. Change query "QryPrice" to:
SELECT DISTINCTROW tblItemPrices.ItemID, tblItemPrices.Price
FROM tblItemPrices
WHERE (((tblItemPrices.PriceSheet)=[Forms]![frmMasterOrderForm]![frmOrder].[Form]![frmNewMain].[Form]![Combo11]));

Sorry, I don't understand. Where is "QryPrice"?

Also, I  don't see how I can make this work for frmMasterOrderForm correctly... it keeps asking for Combo11 before that would ever be established...

It is better to use this
2011-SHOW-SEASON-ORDER-DB---2007.zip

It will not let me change the price level to test?

Ok, nevermind... it will let me do it now that I saved rather than a Read-Only copy... the Price/Override changes perfectly now, but anyway we can have the Price Change as well like it did prior?

Thanks!!!

You will be able edit price, of you return query "QryPriceLookup" to your original version:

SELECT tblOrderDetail.OrderItemID, tblOrderDetail.OrderDetailID, tblOrderDetail.ItemID, tblOrderDetail.Qty, DLookUp("Price","tblItemPrices","ItemID=" & [ItemID] & " AND PriceSheet=" & [Forms]![frmMasterOrderForm]![frmOrder]![frmNewMain]![Combo11]) AS Price, [PriceEach]*[Qty] AS LineTotal, tblOrderDetail.PriceEach
FROM tblOrderDetail;

Next version, with original QryPriceLookup
2011-SHOW-SEASON-ORDER-DB---2007.zip

Ok, it is still not changing the "PRICE" field, but I think I can get that with the previously made comments above. I will attempt to figure out what new Queries/Commands you added and implement this into our current working DB.

Perfect! Thanks so much!

I don't need points, but you had to select correct answer for other users

I don't if I understand; I clicked ACCEPT AS SOLUTION for your solution because that is the only thing that worked?

You can cancel request:
"To cancel this request and generate a request in Community Support for Moderator review, state your reason for objecting and click the 'Object' button. "

I didn't mean to award the solution to myself. Please open so i can award points to the correct expert. Also, i would like to request that the attachments be removed from the question.

Please, help author:
https://www.experts-exchange.com/expertsZone.jsp#34764942
https://www.experts-exchange.com/expertsZone.jsp#34762060

I think you can now close question again