Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2004
    Location
    Edinburgh Scotland
    Posts
    23

    Unanswered: Message Box Alert

    Hope you guys may be able to help
    I have a form with a sub form
    On the sub form I have #Ordered, #Supplied & #Used. These fields calculate to give the user the # in stock.
    This figure is displayed on the form. On the form I also have the Reorder level which is set by the user.
    I would like to be able to make a message alert pop up when the database is opened when any of these records show the # in stock is lower than the Reorder level, ensuring that we do not run out of stock.

    Many thanks for any help you can give

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Under normal circumstances the check for remaining stock would be done as the order is taken to ensure there is in fact enough stock to fill the order. If there isn't an alert is posted. If there is enough stock to fill the order but it falls below the acceptable 'Remaining Stock Level' while the order is filled then an alert should be posted indicating as such as well.

    The same method you use to check stock levels while filling a order can also be applied when the database is initially opened. It doesn't have to be fancy and it can be generic. It all depends upon how in depth and detailed you want to get with checking 'Remaining Stock Level' in comparison to the 'ReOrder Stock Level'.

    By generic, I mean any stock item. It doesn't have to be specific. Once one is detected you then have the option to view them all.

    For example:

    Code:
    Private Sub CheckStockLevels ()
       If Nz(DLookup("[StockID]", "StockTable", "[StockLevel] < [ReOrderLevel]"), 0) > 0 Then
          If MsgBox("Some stock levels are Low and ReOrdering is required." & vbCrLf & _
             "Do you want to view these items now?", vbCritical + vbYesNo, _
             "Low Stock Levels Detected") = vbYes Then
             DoCmd.OpenForm "StockFORM", acNormal, , "[StockLevel] < [ReOrderLevel]", _
             acFormReadOnly
             ' Have a ReOrder Button in your Stock Form so as to
             ' add each item to a ReOrder list as required (or do
             ' it all automatically).
          End If
       End If
    End Sub
    Place this procedure (or something like it) somewhere into the code module for your Main Form. In the OnCurrent event for your Main Form, place this:

    Call CheckStockLevels

    Now...when the Database is started and every time you move to another record the Stock Levels are checked and if any ONE stock is detected to be low, then you have the option to view all low stock levels.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Oct 2004
    Location
    Edinburgh Scotland
    Posts
    23
    Many thanks for your help. The OnCurrent event on the main form is already in use, so I placed this on the subform.
    Placing Call CheckStockLevels on the OnCurrent event gives the error message 'Microsoft Access can't find the macro Call CheckStockLevels'.

    I'm a bit confused, but I'm further forward than I was yesterday and I'm sure I'll get there in the end.
    Many thanks again

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    You are getting the Error simply because Access can not find the Procedure CheckStackLevels. It's because you placed the procedure into the Main Form Code Module but placed the call into the SubForm's OnCurrent event. If you are going to stick with this then you will need to remove the CheckStockLevels procedure and move it to a Database code module then declare it as Public. For example:

    Code:
    Public Sub CheckStockLevels () If Nz(DLookup("[StockID]", "StockTable", "[StockLevel] < [ReOrderLevel]"), 0) > 0 Then If MsgBox("Some stock levels are Low and ReOrdering is required." & vbCrLf & _ "Do you want to view these items now?", vbCritical + vbYesNo, _ "Low Stock Levels Detected") = vbYes Then DoCmd.OpenForm "StockFORM", acNormal, , "[StockLevel] < [ReOrderLevel]", _ acFormReadOnly ' Have a ReOrder Button in your Stock Form so as to ' add each item to a ReOrder list as required (or do ' it all automatically). End If End If End Sub
    If there is already code in the Main form's OnCurrent event...so what...place it a the bottom of the code already there as long as it stays within the event and there is no premature event exit.

    If the Main Form's OnCurrent event contains a the name of a Function like

    =SomeFunctionName()

    then find the function and place the call within it.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  5. #5
    Join Date
    Oct 2004
    Location
    Edinburgh Scotland
    Posts
    23
    Everything was placed onto the sub form initially. I've now moved everything to the main form and am receiving the same error message

  6. #6
    Join Date
    Nov 2003
    Posts
    1,487
    Go to the actual database window, you know, the window that lists all the Tables, Queries, Forms, Reports, Pages, Macros, and yes...Modules.

    Select the Modules item then select New at the top of the window. A new database code module will open up within the IDE. Copy and paste the procedure into this new module then close the IDE. Select the save button in your toolbar and provide a descriptive name...something like ;Main Module'.

    Try your application now.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •