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
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
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
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'.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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
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
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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
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.
Last edited by June7; 03-31-2013 at 02:19 PM.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.