Stock\inventory Control

[tweety]

i havnt tried it at all yet, iam working on validation the whole database at the min, will look it up after the that, thanks for asking

[tweety]

i have had a look on the net for DLookup, but can not apply it to my system, can you explain more, what i need to do please, so i may have a better go at it again

thanks

[June7]

Don't know your data structure, but if you want the balance on hand for a specific product, something like:

DLookup("Balance", "Products", "ProductID=" & [ProductID])

This expression could be in a query or in textbox on form or report.

Access Help has info on domain aggregate functions. Search Help for 'domain aggregate'.

[tweety]

is the dlook to validate if any stock is left? is there a way to check the stock e.g if -0 the display at error message?

thanks

[June7]

You can use the result returned by the DLookup in many ways. It by itself doesn't validate anything. Could use it in VBA code like:

If Nz(DLookup("Balance", "Products", "ProductID=" & [ProductID]),0) <= 0 Then
MsgBox "Stock level at or below zero."
End If

[tweety]

i have added to the code in both places (the textbox on the form) and the following


Private Sub Quantity_Exit(Cancel As Integer)
If Nz(DLookup("Quantity", "tblProduct", "ProductID=" & [ProductID]), 0) <= 0 Then
MsgBox "Stock level at or below zero."
Else
CurrentDb.Execute "UPDATE tblProduct SET Quantity = Quantity - " & Me.Quantity & " WHERE ProductID = " & Me.ProductID
End If
End Sub

but the stock is still going < -1 and not displaying a message

[June7]

Without being familiar with your latest version can't be more specific. You said you need to lookup a value in a table then DLookup is one way to do it. Step debug, follow the code as it executes, determine why it deviates from expectation. See link at bottom of my post for debugging guidelines.

But frankly, saving aggregate data is usually a bad idea. In a properly structured db raw data is saved and balances are calculated when needed. This has been pointed out already by several replies in this thread.