Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Another message box criteria question

Posted on 2013-12-06
19
Medium Priority
?
423 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: 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!

 

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 1000 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
 
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 1000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

618 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