Solved

Another message box criteria question

Posted on 2013-12-06
19
363 Views
Last Modified: 2013-12-08
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
Comment
Question by:SteveL13
  • 11
  • 6
  • 2
19 Comments
 

Author Comment

by:SteveL13
Comment Utility
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
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
 

Author Comment

by:SteveL13
Comment Utility
I'm getting an error on this line:

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

Runtime 3061
Too few parameters.  Expected 1.
0
 

Author Comment

by:SteveL13
Comment Utility
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
 

Author Comment

by:SteveL13
Comment Utility
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
 

Author Comment

by:SteveL13
Comment Utility
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
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
 
LVL 17

Assisted Solution

by:JezWalters
JezWalters earned 250 total points
Comment Utility
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
 

Author Comment

by:SteveL13
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
 

Author Comment

by:SteveL13
Comment Utility
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
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
 

Author Comment

by:SteveL13
Comment Utility
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
 

Author Comment

by:SteveL13
Comment Utility
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
 
LVL 61

Accepted Solution

by:
mbizup earned 250 total points
Comment Utility
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
 

Author Comment

by:SteveL13
Comment Utility
Outstanding!   Thank you very much.
0
 

Author Comment

by:SteveL13
Comment Utility
Mbizup: Did you use any of JezWalters code or suggestion?
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
 
LVL 17

Expert Comment

by:JezWalters
Comment Utility
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now