• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

Another message box criteria question

I have a form which has a field named txtPart_No.  When the user has made an entry in this textbox I want a message box to appear IF certain criteria exists.  First of all a query has to run that looks like the attachment.  The query is named "qryScrapPercentForLastThreeOrders"

Then IF the field "Scrap Percentage" from top to bottom in the query  is descending in value, (in other words, if the first record was 20.1%, the middle one was 15.7%, and the last one was 8.9%), make the message box appear.

But the message box has to give the user the choice to either print a report (yet to be designed), or close the message box and proceed.

I don't know how to code the afterupdate event of the field txtPart_No

--Steve
3-Order-Query.jpg
0
SteveL13
Asked:
SteveL13
  • 11
  • 6
  • 2
2 Solutions
 
SteveL13Author Commented:
Note:  There will be cases where there will be only 1 or 2 records in the query.

If there are two and the first one is greater than the second then the same things has to happen as in the original post.

If there is only one record then nothing is to happen.
0
 
mbizupCommented:
Try this:


Dim rs as DAO.Recordset
dim dblPct as double
dim strCheck  as string

Set rs = currentDB.Openrecordset("qryScrapPercentForLastThreeOrders", dbOpenDynaset)

Select case rs.recordcount
          case 0
                 strCheck = "Fail"
          case 1
                strCheck = "Fail"
          Case else
                 do until rs.EOF
                      dblPct = rs("ScrapPercentage")
                      rs.MoveNext
                           if rs("ScrapPercentage") < dblPct then 
                                dblPct = rs("ScrapPercentage")
                           else
                                strCheck = "Fail"
                           end if
                  loop
End Select

if strCheck <> "Fail" then
       if MsgBox( "Print Report? ")  = vbYes then
               docmd.openreport ......  etc
       else
       end if
else

end if

Open in new window

0
 
SteveL13Author Commented:
I'm getting an error on this line:

Set rs = CurrentDb.OpenRecordset("qryScrapPercentForLastThreeOrders", dbOpenDynaset)

Runtime 3061
Too few parameters.  Expected 1.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SteveL13Author Commented:
I'm not sure what this means but if I hover over that line of code which is yellow I see "dbOpenDynaset = 2"

When I run the query there are three records which is how many I expect.
0
 
SteveL13Author Commented:
If this helps, here is the SQL for the query.  (Note that the real field name is "ScrapPercent" not "ScrapPercentage" as I originally posted).  

SELECT qryLastThreeOrdersForPartN.RecID, qryLastThreeOrdersForPartN.Part_No, qryLastThreeOrdersForPartN.Order_Qty AS [Order Qty], qryTotalScrapAllParts.Scrap, [Scrap]/[Order_Qty] AS ScrapPercent, qryLastThreeOrdersForPartN.Date_Entered, qryLastThreeOrdersForPartN.Customer_1, qryLastThreeOrdersForPartN.Order_Qty, qryLastThreeOrdersForPartN.Ack_Date AS [Ship Date], qryLastThreeOrdersForPartN.Pattern_Type AS [Molding Section], qryLastThreeOrdersForPartN.Weight_Cstg, qryLastThreeOrdersForPartN.Flask_Size, qryLastThreeOrdersForPartN.Patterns_On
FROM qryLastThreeOrdersForPartN LEFT JOIN qryTotalScrapAllParts ON qryLastThreeOrdersForPartN.RecID = qryTotalScrapAllParts.OpenOrdRecID;
0
 
SteveL13Author Commented:
And here is the SQL for the query that is nested in the other query...

SELECT TOP 3 tblOpenOrders.Part_No, *
FROM tblOpenOrders
WHERE (((tblOpenOrders.Part_No)=[Forms]![frmOpenOrders]![txtPart_No]) AND ((tblOpenOrders.Order_Qty)>0))
ORDER BY tblOpenOrders.RecID DESC;
0
 
mbizupCommented:
Its the form reference that is causing that problem.  Try changing it to this:

SELECT TOP 3 tblOpenOrders.Part_No, *
FROM tblOpenOrders
WHERE (((tblOpenOrders.Part_No)=Eval([Forms]![frmOpenOrders]![txtPart_No])) AND ((tblOpenOrders.Order_Qty)>0))
ORDER BY tblOpenOrders.RecID DESC;

Open in new window

0
 
JezWaltersCommented:
No offense to mbizup, but the .RecordCount property of a Recordset isn't set until you go to the last record.  The algorithm you use should also ideally stop checking as soon as invalid data is detected (to improve speed).

The following approach should solve both of these problems:

Option Explicit
Option Compare Database

Private Sub txtPart_No___AfterUpdate()

    Const MAX_PERCENTAGE As Single = 100 / 100  ' 100% - set this to the maximum permitted percentage value!

    Dim blnValid As Boolean
    Dim rstRecordset As DAO.Recordset
    Dim sngScrapPercentage As Single

    Set rstRecordset = CurrentDb.OpenRecordset("qryScrapPercentForLastThreeOrders", dbOpenDynaset)

    With rstRecordset
        blnValid = True
        sngScrapPercentage = MAX_PERCENTAGE
        Do Until .EOF
            If !ScrapPercentage > sngScrapPercentage Then
                blnValid = False
                Exit Do  ' No need to check further!
            Else
                sngScrapPercentage = !ScrapPercentage
            End If
            .MoveNext
        Loop

        .Close
    End With
    Set rstRecordset = Nothing

    If Not blnValid Then
        If MsgBox("Data not valid!" & vbCr & vbCr & _
                  "Do you want to print a report?", vbQuestion + vbYesNo + vbDefaultButton1) = vbYes Then
            DoCmd.OpenReport "repYourReport"
        End If
    End If

End Sub

Open in new window


Note that the above code treats zero records as valid, and also allows unchanged ScrapPercentage values.

Do let me know if either of these assumptions is wrong!  :-)
0
 
SteveL13Author Commented:
This does not work:


SELECT TOP 3 tblOpenOrders.Part_No, *
FROM tblOpenOrders
WHERE (((tblOpenOrders.Part_No)=Eval([Forms]![frmOpenOrders]![txtPart_No])) AND ((tblOpenOrders.Order_Qty)>0))
ORDER BY tblOpenOrders.RecID DESC;

Maybe because Part_No is a text field?
0
 
mbizupCommented:
A text field would only be problematic if the form reference was in visual basic, but it should be ok in SQL.  Try this - the parentheses might have been mismatched:
SELECT TOP 3 tblOpenOrders.Part_No, *
FROM tblOpenOrders
WHERE tblOpenOrders.Part_No =Eval([Forms]![frmOpenOrders]![txtPart_No])  AND tblOpenOrders.Order_Qty > 0 
ORDER BY tblOpenOrders.RecID DESC;

Open in new window


Also, JezWalters is correct about needing to populate the recordset before using the recordcount property.  I forgot to include that in my earlier post:

Dim rs as DAO.Recordset
dim dblPct as double
dim strCheck  as string

Set rs = currentDB.Openrecordset("qryScrapPercentForLastThreeOrders", dbOpenDynaset)

rs.movelast
rs.movefirst

Select case rs.recordcount
          case 0
                 strCheck = "Fail"
          case 1
                strCheck = "Fail"
          Case else
                 do until rs.EOF or strCheck = "Fail"
                      dblPct = rs("ScrapPercentage")
                      rs.MoveNext
                           if rs("ScrapPercentage") < dblPct then 
                                dblPct = rs("ScrapPercentage")
                           else
                                strCheck = "Fail"
                           end if
                  loop
End Select

if strCheck <> "Fail" then
       if MsgBox( "Print Report? ")  = vbYes then
               docmd.openreport ......  etc
       else
       end if
else

end if

Open in new window


I also agree with him about best practices with exit points in general, and my coding here WAS indeed lazy in that respect... but with a maximum of three records here, I let it slide (any performance difference would be very small).  I've modified the Do Until loop to show how that best practice should be written in this code.
0
 
SteveL13Author Commented:
This still doesn't work.  Getting "This expression is typed incorrectly or too complex to be evaluated".

SELECT TOP 3 tblOpenOrders.Part_No, *
FROM tblOpenOrders
WHERE tblOpenOrders.Part_No =Eval([Forms]![frmOpenOrders]![txtPart_No])  AND tblOpenOrders.Order_Qty > 0
ORDER BY tblOpenOrders.RecID DESC;
0
 
mbizupCommented:
Try this -

SELECT TOP 3 tblOpenOrders.Part_No, *
FROM tblOpenOrders
WHERE tblOpenOrders.Part_No = getPartNo()  AND tblOpenOrders.Order_Qty > 0 
ORDER BY tblOpenOrders.RecID DESC;

Open in new window


And add this function to a module:

Function GetPartNo() as string
        getPartNo = NZ([Forms]![frmOpenOrders]![txtPart_No], "") 
End Function

Open in new window

0
 
SteveL13Author Commented:
Ok, I copy/pasted this:

SELECT TOP 3 tblOpenOrders.Part_No, *
FROM tblOpenOrders
WHERE tblOpenOrders.Part_No = getPartNo()  AND tblOpenOrders.Order_Qty > 0
ORDER BY tblOpenOrders.RecID DESC;

and also added the module.

But now the afterupdate event on the form gives me a new error:

Error 3021 No current record.  Gets stuck on:

If rs("ScrapPercent") < dblPct Then
0
 
SteveL13Author Commented:
Here is what I've done.  I've stripped out everything I could in the original database and let what is needed to test with in the attached database.

Please open the form frmOpenOrders and enter part # 001680.  It'll be there already so just delete it and reenter it.

Since this part # has scrap that is increasing (as is evident if you run qryScrapPercentForLastThreeOrders), the code should ask if the report should be run.

Instead I get Error 3021 No current record.  Gets stuck on:

If rs("ScrapPercent") < dblPct Then

Note:  The code I've commented out works fine for part #9N429A since that part number is a different situation.
Example.accdb
0
 
mbizupCommented:
Try this:

    Dim rs As DAO.Recordset
    Dim dblPct As Double
    Dim strCheck  As String

    Set rs = CurrentDb.OpenRecordset("qryScrapPercentForLastThreeOrders", dbOpenDynaset)

    rs.MoveLast
    rs.MoveFirst

    Select Case rs.RecordCount
          Case 0
                 strCheck = "Fail"
          Case 1
                strCheck = "Fail"
          Case Else
                 dblPct = rs("ScrapPercent")
                 rs.MoveNext
                 Do Until rs.EOF Or strCheck = "Fail"
                      
                           If rs("ScrapPercent") < dblPct Then
                                dblPct = rs("ScrapPercent")
                           Else
                                strCheck = "Fail"
                           End If
                           
                      rs.MoveNext

                  Loop
    End Select

    If strCheck <> "Fail" Then
       If MsgBox("Print Report? ", vbYesNo) = vbYes Then
               DoCmd.OpenReport "rptScrapAlert", acViewPreview, , , acWindowNormal
       Else
       End If
    Else

    End If

Open in new window

0
 
SteveL13Author Commented:
Outstanding!   Thank you very much.
0
 
SteveL13Author Commented:
Mbizup: Did you use any of JezWalters code or suggestion?
0
 
mbizupCommented:
Yes -

In my comment at http:#a39703424 (and continuing in the rest of the code in this thread) I added rs.movefirst/ movelast code to populate the recordset, and also a condition to exit the loop immediately after determining that the records are not in descending order (best practice) -- both per his comment
0
 
JezWaltersCommented:
Bear in mind that the solution you have accepted won't work if your qryScrapPercentForLastThreeOrders query can ever return zero records.

This is because you can't execute the .MoveLast method when you're already at the end of a Recordset.

Also, it's generally best practice to use a Boolean variable for a flag (to indicate valid/invalid data), rather than a String as mbizup has it.

Finally (although you don't have to do this), it's a good idea to explicitly .Close each Recordset that you open.  Tidy as you go!  ;-)

Dim blnValid As Boolean
Dim rstRecordset As DAO.Recordset
Dim varScrapPercent As Variant

Set rstRecordset = CurrentDb.OpenRecordset("qryScrapPercentForLastThreeOrders", dbOpenDynaset)

With rstRecordset
    blnValid = True
    varScrapPercent = Null
    Do Until .EOF
        If !ScrapPercent > varScrapPercent Then
            blnValid = False
            Exit Do  ' No need to check further!
        Else
            varScrapPercent = !ScrapPercent
        End If
        .MoveNext
    Loop

    .Close
End With
Set rstRecordset = Nothing

If Not blnValid Then
    If MsgBox("Data not valid!" & vbCr & vbCr & _
              "Do you want to print a report?", _
              vbQuestion + vbYesNo + vbDefaultButton1) = vbYes Then
        DoCmd.OpenReport "repYourReport"
    End If
End If

Open in new window

0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 11
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now