Results 1 to 13 of 13
  1. #1
    MichaelA is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    139

    Create a Message box with a warning anytime a criteria is met in a query.

    I have a form based on a query. It contains PartID_PK, PartName, QtyPurchased, QtyUsed, QtyOnHand, LowLimit. I would like to create a message box with a warning anytime the the number in qtyOnHand drops below the number in LowLimit. How is this done?

    Thank you all for your assistance.
    Michael

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,511
    So code in some event. Real trick is figuring out which event(s) to put code into. Possibly AfterUpdate of QtyUsed.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,124
    Could also consider conditional formatting to highlight the form field in case you'd get a message box multiple times for multiple takes once the limit is reached. Not sure I'd want to drop to the limit, get a message, then do 5 more orders from remaining stock and get 5 more messages for the same thing.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  4. #4
    MichaelA is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    139
    Micron, I want to try it first and see if I do get a lot of errors. If I do, I'll remove it. I did do some conditional formatting but I would like for the error message to run just the same.

    I have a button on one form saving a record and then opening the form I referenced earlier that is based on the query. I have the following on that second form (FrmQtyOnHand)

    Private Sub Form_Open(Cancel As Integer)
    If Me.QtyOnHand < Me.txtLowLimit Then
    MsgBox Prompt:="The on hand quantity has dropped below the Low Limit for one or more parts.", Buttons:=vbOKOnly + vbInformation
    Else
    End If
    End Sub

    It only works one time if I change the < to a > and then change it back again. It doesn't make sense to me. I also tried "QtyOnHand" < 0. That did not work.

    What is wrong with this?

    Thank you!

  5. #5
    MichaelA is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    139
    OK, I have part of it figured out. The query based form (FrmQtyOnHand) is a datasheet with numerous rows. Each row represents a different part. The code I posted above works but it is only looking at the first row. I need it to look at all rows and return the msgbox if the < operator is met for any part.

    Thank you!

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,511
    Have a textbox in footer section with expression: =Abs(Sum(QtyOnHand < LowLimit))

    Now your VBA code looks at footer textbox. If it is > 0 then you know 1 or more parts is below limit.

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,124
    It only works one time if I change the < to a > and then change it back again. It doesn't make sense to me.
    Sounds like you're going into design view to change that, then back again. That will perform a new calc when the form opens. Not sure if that is a new issue or what because you didn't mention it originally. Sounds like you want this message thing on the form Current event, not open or load. Also sounds like your qty on hand is a stored calculation but I could be wrong about that. If that is the case, I don't see a need for a calculation in the footer - except that storing calculations is generally not a good idea and that is where it should be - as a calculation, not stored.
    Last edited by Micron; 05-29-2020 at 09:34 AM. Reason: clarification
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,905
    MichaelA,
    Can you tell us where this form fits in your "business processing"?
    I believe the concept you are dealing with is called ReOrder Point (reOrderLevel). That is where your current inventory of that product has been used or drops below some stated level and now you want to trigger a ReOrdering (ReOrder Quantity). You may say that when I have 5 or less of Product Z in inventory, then trigger a new order from the supplier for ReOrderQuantity (some amount based on your sales) of that Product.

    In general, you would check the quantity of Product available before completing a Sale. Probably in the BeforeUpdate event where you can check things and invoke your rule before saving/committing the Order. If the Order was for 10 units and you have 8 in stock, what is your business rule? Do you reject the Sale because you can not supply the Product? Or do you issue x amount (where x<=8) and do a product substitution or backorder to provide the additional units once your Order for more Product from your Supplier arrives.

    I think one of the Northwind databases(maybe all) deal with ReOrderPoint/ReOrderLevel and ReOrderQuantity or similar concept.
    Last edited by orange; 05-29-2020 at 10:55 AM. Reason: spelling

  9. #9
    MichaelA is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    139
    Quote Originally Posted by Micron View Post
    Sounds like you're going into design view to change that, then back again. That will perform a new calc when the form opens. Not sure if that is a new issue or what because you didn't mention it originally. Sounds like you want this message thing on the form Current event, not open or load. Also sounds like your qty on hand is a stored calculation but I could be wrong about that. If that is the case, I don't see a need for a calculation in the footer - except that storing calculations is generally not a good idea and that is where it should be - as a calculation, not stored.
    I'm guessing it is what you would call a stored calculation. The form is based on a query and pulling the numbers from there. If that is what you mean.

  10. #10
    MichaelA is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    139
    Quote Originally Posted by orange View Post
    MichaelA,
    Can you tell us where this form fits in your "business processing"?
    I believe the concept you are dealing with is called ReOrder Point (reOrderLevel). That is where your current inventory of that product has been used or drops below some stated level and now you want to trigger a ReOrdering (ReOrder Quantity). You may say that when I have 5 or less of Product Z in inventory, then trigger a new order from the supplier for ReOrderQuantity (some amount based on your sales) of that Product.

    In general, you would check the quantity of Product available before completing a Sale. Probably in the BeforeUpdate event where you can check things and invoke your rule before saving/committing the Order. If the Order was for 10 units and you have 8 in stock, what is your business rule? Do you reject the Sale because you can not supply the Product? Or do you issue x amount (where x<=8) and do a product substitution or backorder to provide the additional units once your Order for more Product from your Supplier arrives.

    I think one of the Northwind databases(maybe all) deal with ReOrderPoint/ReOrderLevel and ReOrderQuantity or similar concept.
    These are parts that get used in house. We would order more if the stock level drops below a certain number for each part. Each part has its own LowLimit number. The message is just to let us know that we have used so many of a part that we are now below the LowLimit and it is time to order more.

    Thanks!

  11. #11
    MichaelA is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    139
    Quote Originally Posted by June7 View Post
    Have a textbox in footer section with expression: =Abs(Sum(QtyOnHand < LowLimit))

    Now your VBA code looks at footer textbox. If it is > 0 then you know 1 or more parts is below limit.


    I did this and I'm still getting the same result. It works only if the first row, (PartID_PK, 1) drops below the LowLimit.

    Thanks!

  12. #12
    MichaelA is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2019
    Location
    Wisconsin
    Posts
    139
    As long as we're at it, is there a way to check the query instead of the form and only open the form if one of the Parts has dropped below the LowLimit?

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,511
    Okay, did some testing. Code works for me.

    Could use domain aggregate function to check a value in table or query. Explore DCount and DLookup functions.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query generating Warning Message
    By tck0633 in forum Queries
    Replies: 4
    Last Post: 08-10-2018, 03:26 AM
  2. Pop Message Box Warning
    By data808 in forum Programming
    Replies: 4
    Last Post: 01-11-2014, 04:43 PM
  3. Access Warning Message
    By marksnwv in forum Access
    Replies: 1
    Last Post: 06-01-2012, 01:46 PM
  4. Access warning message
    By John Southern in forum Access
    Replies: 2
    Last Post: 05-28-2010, 06:01 AM
  5. Records deleted with NO warning message.
    By evanscamman in forum Access
    Replies: 2
    Last Post: 12-14-2007, 11:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums