Help with this code please - critical to my work

[Mike]

HI,

I have taken this code from one of the users of this
newsgroup website but unfortunately i have lost his
contact information and the website. I have modified this
code to work with my application but it has some syntax
error in the From Cluas (This is what the VB tells me). I
need help to identify this syntax error and possibly the
correct syntax. This code gives the on hand amount of
items in stock in an Inventory database. I am very new to
VB but I really tried to resolve this error - I couldn't.

Thank you for your help: Here is the modified code:

Function OnHand(vProductID As Variant, Optional vAsOfDate
As Variant) As Long
'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 String 'vProductID as a
long.

'lngProduct is sting - text and not nummber
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 (DateReceived <= " &
strAsOf & ")"
End If
strSQL = "SELECT TOP 1 DateReceived, UnitsIn FROM
tblProduct " & _
"WHERE ((ProductID = " & """lngProduct"""
& ")" & strDateClause & _
") ORDER BY DateReceived DESC;"

Set rs = db.OpenRecordset(strSQL)
With rs
If .RecordCount > 0 Then
strSTDateLast = "#" & Format$(!
DateReceived, "mm\/dd\/yyyy") & "#"
lngQtyLast = Nz(!LastStckRcvd, 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(tblProduct.UnitsIn) AS
QuantityAcq " & _
"FROM tblProduct = tblProduct.ProductID " & _
"WHERE ((tblProduct.ProductID = "
& """lngProduct""" & ")"
If Len(strDateClause) = 0 Then
strSQL = strSQL & ");"
Else
strSQL = strSQL & " AND
(tblProduct.DateReceived " & 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(tblTransaction.Quantity) AS
QuantityUsed " & _
"FROM tblTransaction" & _
"tblTransaction.InvoiceNumber =
tblInvoice.InvoiceNumber " & _
"WHERE ((tblTransaction.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
End Function

Without this function I cannot complete my application -
Thank you in advance for your help.

Regards,

Mike

 

[JeffW]

Hi Mike,

This is who and what you're searching for:

http://allenbrowne.com/AppInventory.html

Jeff

 

[Adrian Jansen]

The code works ok, but you might have to fix the line wraps. In VB you have
to extend lines by ending the first line with an underscore _

When you copy from a website this often gets messed up.
And still worse, its hard to show what the correct syntax is from an email,
because that also breaks the lines in odd places.
Simplest is to reformat the lines in the VB editor, concatenating any lines
broken without an underscore, except where its obvious that its a new line -
obvious to a VB person, that is...
--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

 

[Mike]

HI,

The line ending are correct but I double checked to make
sure. I'm getting Syntax error on the FROM Cluas.

Here is the code again:

Function OnHand(vProductID As Variant, Optional vAsOfDate
As Variant) As Long
'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 String '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 (DateReceived <= " &
strAsOf & ")"
End If
strSQL = "SELECT TOP 1 DateReceived, UnitsIn FROM
tblProduct " & _
"WHERE ((ProductID = " & """lngProduct""" & ")" &
strDateClause & _
") ORDER BY DateReceived DESC;"

Set rs = db.OpenRecordset(strSQL)
With rs
If .RecordCount > 0 Then
strSTDateLast = "#" & Format$(!
DateReceived, "mm\/dd\/yyyy") & "#"
lngQtyLast = Nz(!LastStckRcvd, 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.
'Here I am trying to make the UnitsIn as the quantity
aquired last


strSQL = "SELECT Sum(tblProduct.UnitsIn) AS QuantityAcq "
& _
"FROM tblProduct" & _
"WHERE ((tblProduct.ProductID = " & """lngProduct""" & ")"
If Len(strDateClause) = 0 Then
strSQL = strSQL & ");"
Else
strSQL = strSQL & " AND
(tblProduct.DateReceived " & 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(tblTransaction.Quantity) AS
QuantityUsed " & _
"FROM tblTransaction" & _
"tblTransaction.InvoiceNumber =
tblInvoice.InvoiceNumber " & _
"WHERE ((tblTransaction.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
End Function