Solved

Another message box criteria question

Posted on 2013-12-06
19
380 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
ID: 39702302
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
ID: 39702392
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
ID: 39703181
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
ID: 39703201
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
ID: 39703209
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
ID: 39703211
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
ID: 39703243
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
ID: 39703263
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
ID: 39703397
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
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)

 
LVL 61

Expert Comment

by:mbizup
ID: 39703424
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
ID: 39703440
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
ID: 39703457
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
ID: 39703492
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
ID: 39703529
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
ID: 39703556
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
ID: 39703658
Outstanding!   Thank you very much.
0
 

Author Comment

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

Expert Comment

by:mbizup
ID: 39703708
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
ID: 39704168
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

914 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

19 Experts available now in Live!

Get 1:1 Help Now