Full Version: Refreshing/Timing Help Needed
UtterAccess Discussion Forums > Microsoft® Access > Access Forms
SparrowCathy
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).

Ocan'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.

CODE
Private Sub cmdRefresh_Click()
nbsp;   DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    Me.Refresh
    Forms!XXXXX!XXXXBOMsfrmLineItem.Form.Refresh
    Forms!XXXX!XXXXBOMsfrmLineItem.Form.Requery
    If Me.ExtPriceMsg.Value = "1" Then
    MsgBox "WARNING - The price for this item EXCEEDS $2,500.00.  Please refer to the " & _
        "instructions on line item limits and revise this line item.", vbCritical, "Price Exceeded!"
    MsgBox "Your existing values for this line item will be deleted, you must revise before continuing."
    Me.QTY = ""
    Me.Price = ""
    Else
    End If
    'MsgBox Me.SumMsg.Value
     [color="red"]Call cmdRecalc_Click[/color]
End Sub
[color="blue"]Private Sub cmdRecalc_Click()
    Me.Refresh
    If Me.SumMsg.Value = "1" Then
    Const cTIME = 1000 'in MilliSeconds
    Call sSleep(cTIME)
    MsgBox "WARNING - The total extended value for this order (including TAX and FREIGHT), " & _
        "EXCEEDS $7,500.00.  Please refer to the instructions on line item limits and revise line it" & _
        "ems as needed.", vbCritical, "Value Exceeded!"
    Else
    End If
    Me.Requery
End Sub[/color]

Edited by: NoahP on Wed Jun 13 10:09:09 EDT 2007.
Doug Steele
Try moving your sSleep function immediately after the Refresh, rather than after you've checked the value of SumMsg.
!--c1-->
CODE
Private Sub cmdRecalc_Click()
Const cTIME = 1000 'in MilliSeconds
    
  Me.Refresh
  Call sSleep(cTIME)
  
  If Me.SumMsg.Value = "1" Then
    MsgBox "WARNING - The total extended value for this order " & _
      "(including TAX and FREIGHT), EXCEEDS $7,500.00. " & _
      " Please refer to the instructions on line item limits and " & _
      "revise line items as needed.", vbCritical, "Value Exceeded!"
  End If
  
  Me.Requery
  
End Sub
SparrowCathy
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.
Doug Steele
How is the value for SumMsg set?
SparrowCathy
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.
irst 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")
NoahP
PMFJI...
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' wink.gif
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.
SparrowCathy
Thanks, that makes sense, it doesn't take much for me to confuse myself . . . sad.gif
umMsg 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.
SparrowCathy
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
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.