Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Apr 2008
    Posts
    9

    Unanswered: Alert Messages/Flags

    Hi All, Literally at the end of my teather now, wondering if anyone can help me.

    I've been looking through the forum for days now and cant find nothing solid. I Hope some of you can help me or at least point me in the right direction.

    One of the forms I'm working on (Stock Maintenance) has two controls one named QTY & the other named Min QTY.

    What I want is, When the QTY is below (<10), the Min QTY is flagged then a message box/alert flag appears automatically saying "Stock Low".

    Ideally, I was looking for a way that would inform an administrator via email when the Stock is Low, Alternativly a report would be good that lists the Part No and the description of the Stock that needs Ordering, so the administrator knows exactly what needs to be ordered.

    Any Help On this matter would be greatly appricated.

  2. #2
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    check the form's Form_Current() event. each time a record is displayed, you can evaluate the value of your QTY textbox and do what you need to do.

    for the report, just make sure you gather the right data for the RecordSource. Whether you're just needing those where MinQty is true, or whether you want those where QTY<10.

  3. #3
    Join Date
    Apr 2008
    Posts
    9
    Hi Jmahaffie, thank you for your reply. with a little bit of help from others, i've managed to set my OnCurrent event to;

    Private Sub Form_Current()
    Dim intAnswer As Integer

    If Me.QTY <= 10 Then
    Me.QTY.BackColor = vbRed
    intAnswer = MsgBox("Stock Low!" & vbCrLf & "Send Email?", vbQuestion + vbYesNo)
    If intAnswer = vbYes Then
    DoCmd.SendObject acSendForm, "Stock Maintainence"
    End If
    Else
    Me.QTY.BackColor = vbWhite
    End If
    End Sub

    Unfortunetly, everytime a user opens up the form, the message appears "Stock Low, Send Email".

    am I Using the wrong event or is there an easier way around this problem?

    thank you again

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    several approachs
    the best I'd suggest is that the adminstrator has a form unique to themselves which identifies what stock is at or below warning level. they log in say daily, or whatever the form has a quey which reports all items which are at or below warning level, Id also suggest you group together other items formthe same manufacturer/supplier which are approaching the warning threshold in case there is a minimum invoice value for that supplier.

    I'd let the administrator have some fields that allow them to identify what items are still at warning but they have taken action on.... say an order is placed then the administrator would then not want to be kept reminded that an item is below safety level. Whether you do that by recording a proposed order (or order placed or not delivered is upto you). there may be a freeze on ordering prodcuts so the genuine response formteh admin may be order not required.. item phased out.......

    you could have a overnight or say weekly process which identifies anything problematical and send that as an email, or even a report to that persons nearest (network) printer.

    Im not a fan of raising an email for each and every item.. the adminsitrator may get email overlaod and miss a specific lines warnign message. they don't need re-reminding each time an item is sold that would push the stock further below the MoQ. Ive seen this sort of system that looks great but the burden on the administrator as each and every alert message comes through can be too much. One system that had that was generating an email nearyly every 20..30 seconds at peak times.. frankly it was crazy. The eamil appraoch works if you have few items that trigger an email.. but if as in this case there were hundreds per day it was silly... especailly silly as many if the itmes would trigger repeated attention seeking emails.. if the buyer knows that item X is in short supply, they don't need to be reminded each and every time item X is sold form ther on out.. they may need to knwo quite how close to the knuckle its going to get so they can adjust MoQ's.

  5. #5
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    you're not using continuous forms, are you? where it shows more than one record on the screen?

  6. #6
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    look at what healdem is saying...think about maybe having a button or something or some code triggered in the onclose event of the main database window that will ask that question "do you want to email all the min qty items?" other ways, too, but you probably don't want to ask EVERY time the guy passes over the record.

  7. #7
    Join Date
    Apr 2008
    Posts
    9
    Hi all, thank you for all your help.

    Jmahaffie - I am not using a continouous form, just a simple form with a few controls and combo boxes.

    Healdem - I Totally agree with what your saying about the adminstrator having there own unique form and think it's a great idea, but im afraid the adminstrator wants to be kept out of this database all together and only wants to be notified via email when the stock QTY is low, which is abit of a pain but not alot you can do.

    I have taken into account what you said and have added an Email button next to Min QTY, so when the user is ready at there own choice can then email the adminstrator and say "Stock QTY Low, Please Order New Stock". Which stops the adminstrator getting emails 24/7.

    Is there something that i could do or set to the form that automatically changes the Min QTY to red or that flags up a warning sign, that indicates to the users that Stock is Low? then they can click on the button and email the adminstrator.

    Really appricate your help.

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    The Current event for continuous forms works just as well as it does for single-forms.

    That code in the OnCurrent event should work. The only reason it should come up with the message immediately on opening the form is if the first record's QTY form control was 10 or less.

    Healdem is right though, the application shouldn't be spamming the poor admin's inbox. There should be something like a report that shows all stock items that need replenishment. That report should be sent to the administrator once a week or so.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by BlankReg
    ...
    Healdem - I Totally agree with what your saying about the adminstrator having there own unique form and think it's a great idea, but im afraid the adminstrator wants to be kept out of this database all together and only wants to be notified via email when the stock QTY is low, which is abit of a pain but not alot you can do...
    fine so bombard the barsteward, and wait for the complaints.. document your proposal, document the response.

    By doing it the Email approach you have an issue of system integrity.. you don't know what the adminstrator has done, ie have they taken action or not.

    as a first cut if I were you I'd run an overnight process which reports all flagged items (whether that report is emailed or not is upto you.)
    Id set a flag when the stock actaully drops below trigger tevel.. so its only reported once), clear that flag after the report is generated, or better still store in the db aniother flag whcih identifies when the report was generated / administrator notified.. CYA, CYA, CYA.

    Personally I'd store the report locally, and send a URL by email to eh adminstrator: reason.. other people can pick up the report if the admin is away, on a sickie or leaves the company. It also help if the report is read only so the administrator can't delete the report and feign ignorance. Whether your report is a PDF or snapshot is upto you

    I'd include in the stock out report items which are dramatically below MinStock.. say 50% of min stock.. just to remind bozo that some items are near stockout.

    Again I wouldn't trigger an email each and every time an items goes below threshold (unless you have few iterms per day.. say less thant 10..20 items)

    EMail is a great system when it works... however its a passive medium.... you may well know your system has sent an email to someone.. you have no easy way of knowing if someone has received the email, and perhaps more importantly acted on it. its fine for a "heads up", in my books its not good enough for a stock control system, unless the recipient is absolutely responsible and competant.. but you don't know they are, or if their replacement is....

    if there a stock snafu you can bet the blame wont be going to the person it will go to the system.. cos computers screw up, people don't

  10. #10
    Join Date
    Apr 2008
    Posts
    9
    haldem - totally agree with what your saying yet again. spot on.

    There are over 500+ items that we have a stock, let's just say that 498 of them are below <10 and need stock straight away, is there anything i can do that notifys me, insted of scrolling through all 500?

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    run an update query thats sets a flag or run a query that extracxts all items which are on or below minimum stock quantity

    its all int he where clause

    eg
    select ID, Description, Supplier from Products
    left join suppliers on Suppliers.ID = Prodcuts.SupplierID
    WHERE StockOnHand<=MinimumOrderQuantity

    'obviosly you will need to modify the query to replace with whatever values you need


    the problem is how you calculate your stock on hand.. do you use a stored field (naughty) but easy, or do you calculate it on the fly.

    Although the theory says you should always calculate it on the fly.. it seems daft to me to go back to day one. SO i tend to have a transcation file which indictaes stockmovements, and put in a monthly/quarterly adjustment which in effect is the stock count for the previous period. and then do my sum based on that date forward.

  12. #12
    Join Date
    Apr 2008
    Posts
    9
    Hey, thanks for replying.

    I Hope you dont mind, but would you able to run me through how i would go about doing the update query, i've only ever done normal queries and some with parameters, so it's abit above my head.

    I have on my form QTY, Min Qty, Part No & Description, they would be the only things that would need to be emailed to the adminstrator in order for them to order new stock.

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are settign a flag then you need an update query
    if you dont want toset a flag but just identify what rows are at or below minimum stiock quantity then a simple query is good enough

    I tend to use SQL rather than the query designer (open the query designer, select the SQL view second line far left button.. its a tristate button)

    select QTY, [Min Qty], [Part No], Description from <mytable> where
    QTY<=[MinQty]
    order by [part no]

    incidentally Id reccommned that you dont use the sapce character in mnaiming columns.. makes it a bit oif a pain haivng to use [] to excapusalte the column name.... in place use either the _ character or just truncate to MinQty. persoanlly I do the latter using CaMeL CaSiNg eg MinQty or AltHomePhoneNo

    if you want to have ahuman friendly name set a value in the caption property for the column when you define / edit the table.. that willpull the caption throuugh in future reports and forms.. it won't work on existing forms and reports (it may if you add the column though)

    you can create a human friendly value in your query

    select QTY as "Quantity on Hand", [Min Qty] as "Minimum Stock Level", [Part No], Description from <mytable>
    where QTY<=[Min Qty]
    order by [part no] 'or whatever other oreder you want

    HTH

  14. #14
    Join Date
    Apr 2008
    Posts
    9
    thank you so much for your help.

    this is what i have so far;

    SELECT QTY, Min QTY, HorizonPartNo, Description FROM "Stock Maintainence"
    WHERE QTY<=Min QTY
    ORDER BY HorizonPartNo


    but it keeps saying "Syntax error (missing Operator) in query expression "Min QTY"

    i dont understand what's wrong, maybe you could shed some light

  15. #15
    Join Date
    Apr 2008
    Posts
    9
    Please ignore that previous message, i've managed to get the query working and this is what i have so far;

    SELECT QTY AS ["Quantity on Hand"], [MinQTY] AS ["Minimum Stock Level"], [HorizonPartNo], Description
    FROM [Stock Maintainence]
    WHERE QTY<=[MinQTY]
    ORDER BY [HorizonPartNo];

Posting Permissions

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