My Bill Of Material Form/Subform contains a 'save' button with the following code to check for 2 values.
First, is the price over 2500? IF so, return message, delete values. 2nd - Look at the overall value of all
BOM items. Is the total (regardless of the line item) over 7500? If so, run 2nd message (blue sub below).

The problem is this: In testing, if I put a price value of 2499 and quantity of up to 3 and click the 'save' button,
it works fine since overall value is still under 7500. If I change quantity to 4 and click, I still don't get the overall
error message desired. However, if I click the button a 2nd time without doing anything else, I get the correct
results - the error message to the user. I have tried several variations, and adding refreshes and requeries, but
nothing seems to help. I added this line: 'MsgBox Me.SumMsg.Value to help me troubleshoot. If I change
quantity to 4, the first time I click the button, it gives me a value of '2' (the 'safe' value number I've given). If I
click the button a 2nd time without changing anything else, it gives me a value of '1' (the value that prompts the
error message).

I can't expect the user to click the button twice to get around this, and have tried to simulate that effect behind
the scenes. Where am I going wrong in my code or thinking? Thank you so much.



Edited by: NoahP on Wed Jun 13 10:09:09 EDT 2007.

Try moving your sSleep function immediately after the Refresh, rather than after you've checked the value of SumMsg.

Thanks Doug. So sorry, that doesn't seem to help. I did some further testing and orginally had thought (because I hadn't tested that direction yet) that going from qty of 3 to 4 didn't work because the starting 3 value was too low to trigger the message. So I started at 4, where I had clicked the button twice to trigger the message. The overall value was already over 7500, so I changed the quantity to 5, which was still over, and no error message on first click but yes, a message the 2nd click. Thanks.

How is the value for SumMsg set?

Ahhh . . . you may be on to the solution. My original criteria calculations were based on a line item total exceeding 2500 (price * qty). But I learned I was using the wrong criteria. What it should have been was, it doesn't matter what the line item total is, we only need to check for 2 things - does the price of any single item exceed 2500, and does the overall value exceed 7500? I had changed several queries involved but missed the form field in blue below. I am very turned around in my thinking on this . . . took me a long time to get it right the first time, then I learned I was using the wrong criteria, so am trying to correct things! Thank you so much.

First I find extended price with this:
ExtendedPrice: CCur(Nz([QTY])*NZ([price]))

Then I use this on my form field value, because there are 2 other values not in the line item listing (tax and freight are on overall order, not per line item: IIf(Sum(nz([extendedprice]))+nz([xxxxtax])+nz([xxxxfreight])>7500,"1","2")

PMFJI...

I would look at using True/False instead of "1" and "2". IMO it makes it easier to 'follow' what is going on instead of having to understand the 'magic numbers'

I'd probably do the calculation on the form level using an unbound control and do the whole check in VBA instead of using a column expression in the query.

Thanks, that makes sense, it doesn't take much for me to confuse myself . . .

SumMsg is an unbound text box on my form that currently has the previously listed calc: IIf(Sum(nz([extendedprice]))+nz([xxxxtax])+nz([xxxxfreight])>7500,"1","2"). I'll try the VBA solution and let you know if I bump into any problems, thanks.

Just wanted to report back to you and Doug that the issue is resolved, thanks to both your help. Changed from basing the message off the unbound text box with calculation to this solution. Seems to work ok, and really appreciate the help, thanks.

Dim strEXC As Currency
strEXC = (Nz([ExtendedPrice])) + Nz([AMEXTax]) + Nz([AMEXFreight])
If strEXC > 7500 Then . . . message