Need Help on Code- Attention Allen Browne Please

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi Allen,

I tried but couldn't fix the code to work for me. I only
have one table in each SELECT, FROM AND WHERE statements.
If some one please help me find the error in the from
cluas (it might be elsewhere too)I'll be more than
greatful.

Here is the code that will calculate amount on hand of an
specific product. I got the code from the Alllen's page
but hasn't been able to modify to work for me, my VB
knowldge isn't up to the challenge but I'm trying.

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 * FROM tblProductsIn " &
' "WHERE ProductID ='" & Me.ProductID & "'"

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" & _
"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

There are three tables: tblInvoice, tblProduct, and
tblTransaction.

Please let me know if more info is required.

Regards,

Mike
 
Please let me know if more info is required.
Click to expand...

Yes. Which of the dozen or so queries is giving you an error in its
FROM clause? Could you step through the code in debug mode? If you do
so, you can type

?StrSQL

in the Immediate window and view the actual SQL string that's causing
the error.
 
John,

In which windows? when I type that, it changes to Print
SQl!!

I get the debug message box three times and when I end it
three times, it goes out of the debug mode, but here is
what gets highlighted:

Set rs = db.OpenRecordset(strSQL)

right after the second Select statement in the code.

Regards,

Mike
 
John,

In which windows? when I type that, it changes to Print
SQl!!

I get the debug message box three times and when I end it
three times, it goes out of the debug mode, but here is
what gets highlighted:

Set rs = db.OpenRecordset(strSQL)

right after the second Select statement in the code.

Regards,
Click to expand...

Do you know how to set a Breakpoint, Mike? Open the VBA editor and
click in the grey bar at the left edge of the screen - a brown dot
will indicate a breakpoint. Put this right after the definition of
strSQL and before the Set rs argument.

Then type

?strSQL

(with the question mark) in the Immediate window.

I'm *guessing* that you have an extra ' or " in the string, but it's
confusing and I can't tell for sure even which strSQL is generating
the error!
 
Mike said:
Hi Allen,

I tried but couldn't fix the code to work for me. I only
have one table in each SELECT, FROM AND WHERE statements.
If some one please help me find the error in the from
cluas (it might be elsewhere too)I'll be more than
greatful.

Here is the code that will calculate amount on hand of an
specific product. I got the code from the Alllen's page
but hasn't been able to modify to work for me, my VB
knowldge isn't up to the challenge but I'm trying.

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 * FROM tblProductsIn " &
' "WHERE ProductID ='" & Me.ProductID & "'"

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" & _
"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

There are three tables: tblInvoice, tblProduct, and
tblTransaction.

Please let me know if more info is required.

Regards,

Mike
Click to expand...

I'm not Allen Browne, but a cursory glance shows that this line:
"WHERE ((ProductID = " & """lngProduct"""
Click to expand...

is wrong wherever it appears, in different variations. Either ProductID
is a number field, in which case the line should be

"WHERE ((ProductID = " & lngProduct

or ProductID is a text field, in which case the line should be

"WHERE ((ProductID = """ & lngProduct & """"

(thus enclosing the desired ProductID in quotes). The fact that the
variable *named* lngProductID is actually declared as a String, not a
Long as implied by the "lng" prefix, is somewhat confusing.
 
John,

I did that, and it tells me Method not valid without
suitable object

??

Regards,

Mike
 
Sorry John,

It did itthis time and I got this in the immediate window:



SELECT Sum(tnlProduct.UnitsIn) AS QuantityAcq From
tblProduct WHERE ProductID = "123NP124" ' AND
(tblProduct.DateReceived >= #11/02/2003#)

The ProductID and the DateReceiced are correct values.

Any thoughts???

Regards,

Mike
 
John,

With this proceedure, that is the breaklines and the
Immediate windows and the Watch window I was able to run
my code with no errors, but I'm getting funny and wrong
numbers as my OnHand result. It seems that My SELECT
statements for the UnitsIn and the Quantity are empty
when the code runs.

can you please take a look at he Select statements to see
what might be possible wrong?

I had a ' in the SQL statement as you mentioned, and
Thanks.


Regards,

Mike
 
can you please take a look at he Select statements to see
what might be possible wrong?
Click to expand...

There doesn't seem to be anything wrong with the last one you posted
(the one that's returning the error). Try copying and pasting the SQL
statements from the debug window into new Queries, in SQL view; open
the query and see if the data it's pulling from the tables is what you
expect.
 
John,

The code runs with no error but when I pasted the SQL
statement into a query, it didn't return any values, only
the heading and a cell with no value in it????

Regards,

Mike
 
John,

The code runs with no error but when I pasted the SQL
statement into a query, it didn't return any values, only
the heading and a cell with no value in it????
Click to expand...

Then clearly the SQL isn't returning what you expect. Care to post it
here?
 
Back
Top