| |
You are here: Experts > Computing/Technology > Business Software > Using MS Access > Prevent a record from being captured when insufficient stock available
Expert: Scottgem
Date: 10/12/2008
Subject: Prevent a record from being captured when insufficient stock available
Question
Hi Scott
I have designed a stock control system using action queries and macros (I have VERY limited VB knowledge but very good Access skills) and I am stuck on one last thing... When an employee places an order (using a main frmEmployeesLogin & frmOrderSubform), I need it to alert if the stock level will fall below zero (tblStockList has the field for stock Qty on hand) and prevent them from adding the order… How do I go about getting that to happen?
A programmer took a look at the database for me and sent back the following solution (see code below) but it doesn't work :( Can you help please... Thank you, Sharon
Simply Paste the sub at the bottom of the code page of frmOrders_subform
Private Sub CheckQty()
Dim frs As Recordset
Set frs = Me.Recordset
Do Until frs.EOF
Dim dbQty As Integer
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT QTY FROM tblStockList WHERE Code='" & frs("Code") & "'")
dbQty = rs("QTY").Value
rs.Close
Set rs = Nothing
If dbQty - frs("Qty") < 0 Then
Dim Response As VbMsgBoxResult
Response = MsgBox("Item Code " & frs("Code") & " stock will fall below 0, this item will be removed.")
frs.Delete
End If
frs.MoveNext
Loop
End Sub
Second thing you need to do is on the two dropdowns at the top of the code page select qty and in the second select Lost Focus
This will create a new sub simply paste it so it looks like this.
Private Sub Qty_LostFocus()
Call CheckQty
End Sub
Third thing you need to do is on the two dropdowns at the top of the code page select qty and in the second select Change
This will create a new sub simply paste it so it looks like this.
Private Sub Code_Change()
Call CheckQty
End Sub
Answer
Well, first, I'm sorry to say that your application is not properly designed. You should NOT have a table like tblStockList. Stock on hand is a calculated value and, as a general rule, we don't store calculations.
The proper way to design an inventory application that will include order capability, is around a transactions table. This table will record ALL movement of stock, in and out. You then calculate stock on hand but adding all the incoming transactions and subtracting the outgoing ones. The way I build such apps, is to code all transaction types in groups so its easy to tell which is an incoming and whihc is outgoing. I then use a query to create and adj quantity so all outgoing transactions are negative amounts and incomng positive amounts. I then use that as the source of a Group By query which groups on the Product and sums the adjusted quantity. The result is a listing of stock on hand by Product.
To check the amount I would use a DLookup:
Dlookup("[SOH]","qrySOH","[ProductID] = " & Me.cboProductID)
I'm assuming that you are using a combo to select the Product.
In the After Upodate event of the Quantity control I would use code like:
Dim lngSOH As Long
lngSOH = Dlookup("[SOH]","qrySOH","[ProductID] = " & Me.cboProductID)
If lngSOH < Me.txtQuantity Then
MsgBox "T here is insufficent stock " & lngSOH & " to handle this order.", vbOKOnly
Me.txtQuantity.SetFocus
End If
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
Add to this Answer
Ask a Question
|
|
