Inventory control

[Mclaren]

HI i am trying to use the "Inventory Control" example as posted on Allen Browns website. i keep getting a "Argument not Optional Error when running the function.

I have all the tables set up as described, i have created a form that has a text box and a combo box that passes the date and productid to the function, i run the function using a button. I cannot find what i am doing wrong. I emailed Allen Browns website but they cannot help with the sample.

has anyone used this example successfully ? if so what am i doing wrong.

Below is the code for my form :

Option Compare Database

Function OnHand(vProductID As Variant, Optional vAsOfDate As Variant) As Long
On Error GoTo OnHand_Err

'Purpose: Return the quantity-on-hand for a product.
'Arguments: vProductID = the product to report on.
' vAsOfDate = the date at which quantity is to be calculated.
' If missing, all transactions are included.
'Return: Quantity on hand. Zero on error.
Dim db As DAO.Database 'CurrentDb()
Dim rs As DAO.Recordset 'Various recordsets.
Dim lngProduct As Long 'vProductID as a long.
Dim strAsOf As String 'vAsOfDate as a string.
Dim strSTDateLast As String 'Last Stock Take Date as a string.
Dim strDateClause As String 'Date clause to use in SQL statement.
Dim strSQL As String 'SQL statement.
Dim lngQtyLast As Long 'Quantity at last stocktake.
Dim lngQtyAcq As Long 'Quantity acquired since stocktake.
Dim lngQtyUsed As Long 'Quantity used since stocktake.

If Not IsNull(vProductID) Then
'Initialize: Validate and convert parameters.
Set db = CurrentDb()
lngProduct = vProductID
If IsDate(vAsOfDate) Then
strAsOf = "#" & Format$(vAsOfDate, "mm\/dd\/yyyy") & "#"
End If

'Get the last stocktake date and quantity for this product.
If Len(strAsOf) > 0 Then
strDateClause = " AND (StockTakeDate <= " & strAsOf & ")"
End If
strSQL = "SELECT TOP 1 StockTakeDate, Quantity FROM tblStockTake " & _
"WHERE ((ProductID = " & lngProduct & ")" & strDateClause & _
") ORDER BY StockTakeDate DESC;"

Set rs = db.OpenRecordset(strSQL)
With rs
If .RecordCount > 0 Then


strSTDateLast = "#" & Format$(!StockTakeDate, "mm\/dd\/yyyy") & "#"
lngQtyLast = Nz(!Quantity, 0)
End If
End With
rs.Close

'Build the Date clause
If Len(strSTDateLast) > 0 Then
If Len(strAsOf) > 0 Then
strDateClause = " Between " & strSTDateLast & " And " & strAsOf
Else
strDateClause = " >= " & strSTDateLast
End If
Else
If Len(strAsOf) > 0 Then
strDateClause = " <= " & strAsOf
Else
strDateClause = vbNullString
End If
End If

'Get the quantity acquired since then.
strSQL = "SELECT Sum(tblAcqDetail.Quantity) AS QuantityAcq " & _
"FROM tblAcq INNER JOIN tblAcqDetail ON tblAcq.AcqID = tblAcqDetail.AcqID " & _
"WHERE ((tblAcqDetail.ProductID = " & lngProduct & ")"
If Len(strDateClause) = 0 Then
strSQL = strSQL & ");"
Else
strSQL = strSQL & " AND (tblAcq.AcqDate " & strDateClause & "));"
End If

Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
lngQtyAcq = Nz(rs!QuantityAcq, 0)
End If
rs.Close

'Get the quantity used since then.
strSQL = "SELECT Sum(tblInvoiceDetail.Quantity) AS QuantityUsed " & _
"FROM tblInvoice INNER JOIN tblInvoiceDetail ON " & _
"tblInvoice.InvoiceID = tblInvoiceDetail.InvoiceID " & _
"WHERE ((tblInvoiceDetail.ProductID = " & lngProduct & ")"
If Len(strDateClause) = 0 Then
strSQL = strSQL & ");"
Else
strSQL = strSQL & " AND (tblInvoice.InvoiceDate " & strDateClause & "));"
End If

Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
lngQtyUsed = Nz(rs!QuantityUsed, 0)
End If
rs.Close

'Assign the return value
OnHand = lngQtyLast + lngQtyAcq - lngQtyUsed
End If

Set rs = Nothing
Set db = Nothing
Exit Function

OnHand_Exit:
Exit Sub

OnHand_Err:
MsgBox Error$
Resume OnHand_Exit
End Function


Private Sub cmd_Check_Stock_Click()

vProductID = Me.cbo_Product
vAsOfDate = Me.txtDate
Me.txtStockOnHand = OnHand


End Sub

[pbaldy]

Your problem is here:

Me.txtStockOnHand = OnHand

because the function is expecting you to tell it which product you want the on hand quantity of. It would look like this (without the two lines previous):

Me.txtStockOnHand = OnHand Me.cbo_Product, Me.txtDate

[Mclaren]

Okay, tahts giving me red text, so there is something wrong there, not sure what though.

[Mclaren]

Got it accepting the function:

Me.txtStockOnHand = OnHand (Me.cbo_Product, Me.txtDate)

but now have a problem with these 2 lines :

Dim db As DAO.Database 'CurrentDb()
Dim rs As DAO.Recordset 'Various recordsets.

i am using acess 2007

[pbaldy]

What's the problem? If memory serves, in 2007 the MS DAO reference is replaced by the Database Engine library or words to that effect. Make sure that's checked in Tools/References.

[Mclaren]

I am getting the following error:

Compile error
User-defined type not defined

If i check both: MS DAO 3.51 and 3.6 Object Libraries : i have no luck, i then get a :

Error in loading DLL

[Access_Blaster]

after you check these go back and look at the reference window, it might say what your missing.
MS DAO 3.51 and 3.6 Object Libraries

[pbaldy]

Did you try the one I mentioned? In 2007 it's:

Microsoft Office 12.0 Access database engine Object Library

[Mclaren]

that worked but now got a "too few parameters entered. expected1" error. am going through the code now to see if there is a requirement for a 3rd or more parameter.

[pbaldy]

On what line? I assume it's on one of these:

Set rs = db.OpenRecordset(strSQL)

If so, which one? Add this right before the line that errors:

Debug.Print strSQL

which will print out the finished SQL to the VBA Immediate window. If you can't spot the problem, post the SQL here. Oh, and you'll likely need to comment out this line:

On Error GoTo OnHand_Err

so that when the code breaks you can hit debug and be taken to the line causing the error. You'd want to un-comment it after you get it working.

[Mclaren]

I got it, my tables used the word QTY instead of Quantity.

It is now working a treat. Thanks guys for all the help.

[amitkumargokani]

hi
i want date wise opening and closing running balance report for accounting purposed

thanks