Need Help

[richardplr]

Hi

Anyone can guide me for the following:

I have a program and on the openevent, I want to insert event procedure that when I open this form, the system will automatic scan the thru and if the stock on hand is lesser than the minimum, then the message will prompt say "!!!WATCH OUT, STOCK ON HAND IS TOO LOW, PLEASE REPLENISH IMMEDIATELY !!!"

How should I write the code.

Tks

 

[bjackson]

with out knowing exactly what your table structure is you
could try

Dim MinAmount as long
MinAmount= 10 ' or whatever your min stock amount is

if DSum("[field that holds stock qty]","[yourtablename]") < MinAmount then
beep
msgbox "WATCH OUT, STOCK ON HAND IS TOO LOW, PLEASE REPLENISH IMMEDIATELY !!!",vbcritical,"Low Stock Warning"
end if

of course you would also need to add some error handling as well

 

[richardplr]

Dim MinAmount as long
MinAmount= minimum (where minimum is a field, It depend on the product, different product has different minimum) ' or whatever your min stock amount is

if DSum("[stockonhand]","[inventory master list]") < MinAmount then
beep
msgbox "WATCH OUT, STOCK ON HAND IS TOO LOW, PLEASE REPLENISH IMMEDIATELY !!!",vbcritical,"Low Stock Warning"
end if


Is this correct. I have some compiler error. can you enlighten me.

Tks

 

[bjackson]

if you have to check several stock items then you will
have to approach things differently

you could either directly scan the table or set up a query to return
records whose stock qty was less than the minimum qty

I would use a query

Dim Dbs As DAO.Database
Dim Rst As DAO.Recordset
Dim Qdf As DAO.QueryDef
Set Dbs = CurrentDb()
Set Qdf = Dbs.CreateQueryDef("", " SELECT [inventory master list].stockonhand, [inventory master list].minimum, [inventory master list].Stockname" _
& " FROM [inventory master list]" _
& " WHERE ((([inventory master list].stockonhand)<[minimum]));")



With Rst
If Rst.BOF And Rst.EOF Then
' that means all stock qtys are ok
Rst.Close
Set Rst = Nothing
Set Dbs = Nothing
Exit Sub

Else
Rst.MoveFirst
Do Until Rst.EOF = True
MsgBox "WATCH OUT, STOCK ON HAND FOR" & ![stockname] & " IS TOO LOW, PLEASE REPLENISH IMMEDIATELY !!!", vbCritical, "Low Stock Warning"
Rst.MoveNext
Loop
End If
End With
Rst.Close
Set Rst = Nothing
Set Dbs = Nothing

you will need to substitute the field name ![Stockname] with the name of your field which describes your stock
hope this helps