Full Version: Not In List Function Error
UtterAccess Forums > Microsoft® Access > Access Forms
I have a form for Purchases that has a not in list function that is supposed to open the products form when a part number is entered that is not a current part in our inventory. The product form has code for quantity on hand, quantity allocated and quantity on order. Because of this coding, when the form opens (for any new record, whether it be through the purchasing form or the new record directly in the product form) it inputs a null into the code and posts the following error...
un-time error '94':
Invalid use of Null
On the debug option it takes me to this line of code
lngProdID = Me.ProductID
Below is the full code...
Private Sub Form_Current()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim lngProdID As Long
Dim dteStockTakeDate As Date
Dim lngLastStockTakeQuantity As Long
Dim lngAcquiredQuantity As Long
Dim lngUsedQuantity As Long
Dim lngInStock As Long

lngProdID = Me.ProductID

strSQL = "SELECT TOP 1 tblStockTake.StockTakeDate, tblStockTakeDetails.CountedQuantity " _
& "FROM tblStockTake INNER JOIN tblStockTakeDetails ON tblStockTake.StockTakeID = tblStockTakeDetails.StockTakeID " _
& "WHERE tblStockTakeDetails.ProductID = " & lngProdID _
& " ORDER BY tblStockTake.StockTakeDate DESC"

Set db = CurrentDb

Set rs = db.OpenRecordset(strSQL)
With rs
If .RecordCount > 0 Then
dteStockTakeDate = rs!StockTakeDate
lngLastStockTakeQuantity = rs!CountedQuantity
Me!LastStockTakeDate = dteStockTakeDate
Me!LastStockTakeDate = Null
End If
End With

strSQL = "SELECT Sum([tblPurchasingDetails].[QuantityReceived]) AS AcquiredQuantity " _
& "FROM tblPurchases INNER JOIN tblPurchasingDetails ON tblPurchases.PurchaseOrderID = tblPurchasingDetails.PurchaseOrderID " _
& "WHERE tblPurchasingDetails.ProductID = " & lngProdID & " AND tblPurchasingDetails.DateReceived >= #" & dteStockTakeDate & "#"
Set rs = db.OpenRecordset(strSQL)
lngAcquiredQuantity = Nz(rs!AcquiredQuantity, 0)
strSQL = "SELECT Sum([tblProjectDetails].[QuantityPicked]) AS UsedQuantity " _
& "FROM tblProjects INNER JOIN tblProjectDetails ON tblProjects.ProjectID = tblProjectDetails.ProjectID " _
& "WHERE tblProjectDetails.ProductID = " & lngProdID & " AND tblProjects.DeliveryDate >= #" & dteStockTakeDate & "#"
Set rs = db.OpenRecordset(strSQL)
lngUsedQuantity = Nz(rs!UsedQuantity, 0)
lngInStock = (lngLastStockTakeQuantity + lngAcquiredQuantity) - lngUsedQuantity
Me!QuantityOnHand = lngInStock
Set rs = Nothing
Set db = Nothing
End Sub
Any suggestions on how to fix this??? Greatly appreciate the help.
Me.Product is returning a null value and the Long datatype cannot handle a null value. Try:
ngProdID = Nz(Me.ProductID,0)
Then you may want to check if the lngProdID has a value greater or less then 0 to run any more code.
Changing my code to the way you suggested made it to where my product quantity coding doesn't work at all, I still get the error and the frmProducts no longer shows the data in its corresponding table. I'm pretty sure that didn't work.
It looks like you are off to a bad start since that is basically the first line of code. I take it there is a value in Me.ProductID or at least appears to be. If the text box is bound, it might not really have that value in the field yet. Change you code to this to get the Text property:
lngProdID = Me.ProductID.Text
Using SetFocus allows you to read the .Text property which would be what you see in the textbox even if the record is still uncommited.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.