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.
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"
Me.QTY.BackColor = vbWhite
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?
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.
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.
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.
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.
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
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?
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
select ID, Description, Supplier from Products
left join suppliers on Suppliers.ID = Prodcuts.SupplierID
'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.
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
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