VB Help

  • Thread starter Simon Glencross
  • Start date
S

Simon Glencross

I have te following code which gives me the stock total for individual
items................................

Function Onhand(itemCode 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 strProduct 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(itemCode) Then
'Initialize: Validate and convert parameters.
Set db = CurrentDb()

strProduct = itemCode
Debug.Print strSQL
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 ((ItemCode = """ & strProduct & """)" & 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.ItemCode = """ & strProduct & """)"
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.itemcode = """ & strProduct & """)"
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

I have a field called invoicedetail.quanity where the user can enter in the
quantity of the item they are selling. What I would like to be able to do is
the following... when the quantity is entered access then checks the
quantity onhand is the item is in stock allow the user to continue entry but
if the item is out of stock warn the user but allow them to continue if the
select Yes from a YES/NO message box.

Any help/suggestions would be much appreacited.

Thanks in advance

S
 
T

TonyT

something like;

If Onhand(me.txtboxithPartNumberIn) < me.quantity Then
intQtyMsg = msgbox "You are trying to sell more items than you currently
have in stock, do you want to continue?", vbYesNo
If intQtyMsg = vbYes Then
do whatever you want here
Else do other thing here
End If
End If

btw is quantity the field name or invoicedetail.quantity? If the latter I
would seriously rethink naming controls with . in them as referring to them
later could be a headache.

TonyT..
 
S

Simon Glencross

Would you suggest entering this under the afterupdate event of the control?
 
S

Simon Glencross

Tony,

Could you please assist me in incorporating this code in to my database I
know very little about VB and its driving me up the wall!!

Thanks in advance

S
 
T

TonyT

I'll try,
Replace txtboxwithPartNumberIn to the name of a control on your form that
contains the unique identifyer for the part in question I'll call it txtPT
from here on in, I hope that your quantity entry field is called quantity
rather than as I suspect InvoiceDetail.Quantity (if it's the latter use
me.[InvoiceDetail.Quantity] instead or even better change the name to remove
the fullstop altogether.

Try this;

Private Sub TxtPT_AfterUpdate ()
dim intQtyMsg as Integer

If Onhand(me.txtPt) < me.quantity Then
intQtyMsg = msgbox "You are trying to sell more items than you currently
have in stock, do you want to continue?", vbYesNo
If intQtyMsg = vbYes Then
Exit Sub
Else
me.quantity = 0 <<or substitute other code here see ** below for
another way
End If
End If
End Sub

That alone should work (provided the function works) you could if you
preferred dim another variable let's say lngOnHandCnt as Long and start with
lngOnHandCnt = OnHand(me.txtPt)
and replace the If Onhand (me.txtPt) with just If lngOnHandCnt < .......
then the code under the Else part of the msgbox reply could be replaced with
me.quantity = lngOnHandCnt '(the maximum number of items *on hand*)

If you can't get this to work post your actual code and say what errors if
any you ae getting.

TonyT..
 
S

Simon Glencross

Tony,

Here is the code ...... Please see ****

Private Sub Quantity_AfterUpdate()
Dim intQtyMsg As Integer

If Onhand(tblinvoicedetail.itemCode) < Me.Quantity Then
intQtyMsg = msgbox "You are trying to sell more items than you currently
have in stock, do you want to continue?", vbYesNo ***** I get a syntex /
Compile error which highlights this line as being the problem



If intQtyMsg = vbYes Then
Exit Sub
Else
Me.Quantity = 0

End If
End If
End Sub

Thanks for all of your hep so far!!!

S

TonyT said:
I'll try,
Replace txtboxwithPartNumberIn to the name of a control on your form that
contains the unique identifyer for the part in question I'll call it txtPT
from here on in, I hope that your quantity entry field is called quantity
rather than as I suspect InvoiceDetail.Quantity (if it's the latter use
me.[InvoiceDetail.Quantity] instead or even better change the name to
remove
the fullstop altogether.

Try this;

Private Sub TxtPT_AfterUpdate ()
dim intQtyMsg as Integer

If Onhand(me.txtPt) < me.quantity Then
intQtyMsg = msgbox "You are trying to sell more items than you currently
have in stock, do you want to continue?", vbYesNo
If intQtyMsg = vbYes Then
Exit Sub
Else
me.quantity = 0 <<or substitute other code here see ** below for
another way
End If
End If
End Sub

That alone should work (provided the function works) you could if you
preferred dim another variable let's say lngOnHandCnt as Long and start
with
lngOnHandCnt = OnHand(me.txtPt)
and replace the If Onhand (me.txtPt) with just If lngOnHandCnt < .......
then the code under the Else part of the msgbox reply could be replaced
with
me.quantity = lngOnHandCnt '(the maximum number of items *on hand*)

If you can't get this to work post your actual code and say what errors if
any you ae getting.

TonyT..

Simon Glencross said:
Tony,

Could you please assist me in incorporating this code in to my database I
know very little about VB and its driving me up the wall!!

Thanks in advance

S
Click to expand...
Click to expand...
 
T

TonyT

Sorry omitted the brackets;
intQtyMsg = msgbox ("You are trying to sell more items than you currently
have in stock, do you want to continue?", vbYesNo)
also;
tblinvoicedetail.itemCode
can be shortened too;
me.itemCode

apologies for that,

good luck and let me know how it goes,

TonyT..

Simon Glencross said:
Tony,

Here is the code ...... Please see ****

Private Sub Quantity_AfterUpdate()
Dim intQtyMsg As Integer

If Onhand(tblinvoicedetail.itemCode) < Me.Quantity Then
intQtyMsg = msgbox "You are trying to sell more items than you currently
have in stock, do you want to continue?", vbYesNo ***** I get a syntex /
Compile error which highlights this line as being the problem



If intQtyMsg = vbYes Then
Exit Sub
Else
Me.Quantity = 0

End If
End If
End Sub

Thanks for all of your hep so far!!!

S

TonyT said:
I'll try,
Replace txtboxwithPartNumberIn to the name of a control on your form that
contains the unique identifyer for the part in question I'll call it txtPT
from here on in, I hope that your quantity entry field is called quantity
rather than as I suspect InvoiceDetail.Quantity (if it's the latter use
me.[InvoiceDetail.Quantity] instead or even better change the name to
remove
the fullstop altogether.

Try this;

Private Sub TxtPT_AfterUpdate ()
dim intQtyMsg as Integer

If Onhand(me.txtPt) < me.quantity Then
intQtyMsg = msgbox "You are trying to sell more items than you currently
have in stock, do you want to continue?", vbYesNo
If intQtyMsg = vbYes Then
Exit Sub
Else
me.quantity = 0 <<or substitute other code here see ** below for
another way
End If
End If
End Sub

That alone should work (provided the function works) you could if you
preferred dim another variable let's say lngOnHandCnt as Long and start
with
lngOnHandCnt = OnHand(me.txtPt)
and replace the If Onhand (me.txtPt) with just If lngOnHandCnt < .......
then the code under the Else part of the msgbox reply could be replaced
with
me.quantity = lngOnHandCnt '(the maximum number of items *on hand*)

If you can't get this to work post your actual code and say what errors if
any you ae getting.

TonyT..

Simon Glencross said:
Tony,

Could you please assist me in incorporating this code in to my database I
know very little about VB and its driving me up the wall!!

Thanks in advance

S


something like;

If Onhand(me.txtboxithPartNumberIn) < me.quantity Then
intQtyMsg = msgbox "You are trying to sell more items than you
currently
have in stock, do you want to continue?", vbYesNo
If intQtyMsg = vbYes Then
do whatever you want here
Else do other thing here
End If
End If

btw is quantity the field name or invoicedetail.quantity? If the latter
I
would seriously rethink naming controls with . in them as referring to
them
later could be a headache.

TonyT..

:


I have te following code which gives me the stock total for individual
items................................

Function Onhand(itemCode 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 strProduct 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(itemCode) Then
'Initialize: Validate and convert parameters.
Set db = CurrentDb()

strProduct = itemCode
Debug.Print strSQL
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 ((ItemCode = """ & strProduct & """)" &
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.ItemCode = """ & strProduct & """)"
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.itemcode = """ & strProduct &
""")"
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

I have a field called invoicedetail.quanity where the user can enter
in
the
quantity of the item they are selling. What I would like to be able to
do
is
the following... when the quantity is entered access then checks the
quantity onhand is the item is in stock allow the user to continue
entry
but
if the item is out of stock warn the user but allow them to continue
if
the
select Yes from a YES/NO message box.

Any help/suggestions would be much appreacited.

Thanks in advance

S
Click to expand...
Click to expand...
Click to expand...
 
S

SG

Tony have done as instructed and get the following error?

Expected List Seperator or )


Code is as Follows

Private Sub Quantity_AfterUpdate()
Dim intQtyMsg As Integer

If Onhand(Me.itemCode) < Me.Quantity Then
intQtyMsg = msgbox ("You are trying to sell more items than you
currently
have in stock, do you want to continue?", vbYesNo) **** This line is
ighlighted when I get the error

If intQtyMsg = vbYes Then
Exit Sub
Else
Me.Quantity = 0

End If
End If
End Sub

Any Ideas?



TonyT said:
Sorry omitted the brackets;
intQtyMsg = msgbox ("You are trying to sell more items than you currently
have in stock, do you want to continue?", vbYesNo)
also;
tblinvoicedetail.itemCode
can be shortened too;
me.itemCode

apologies for that,

good luck and let me know how it goes,

TonyT..

Simon Glencross said:
Tony,

Here is the code ...... Please see ****

Private Sub Quantity_AfterUpdate()
Dim intQtyMsg As Integer

If Onhand(tblinvoicedetail.itemCode) < Me.Quantity Then
intQtyMsg = msgbox "You are trying to sell more items than you
currently
have in stock, do you want to continue?", vbYesNo ***** I get a syntex /
Compile error which highlights this line as being the problem



If intQtyMsg = vbYes Then
Exit Sub
Else
Me.Quantity = 0

End If
End If
End Sub

Thanks for all of your hep so far!!!

S

TonyT said:
I'll try,
Replace txtboxwithPartNumberIn to the name of a control on your form
that
contains the unique identifyer for the part in question I'll call it
txtPT
from here on in, I hope that your quantity entry field is called
quantity
rather than as I suspect InvoiceDetail.Quantity (if it's the latter use
me.[InvoiceDetail.Quantity] instead or even better change the name to
remove
the fullstop altogether.

Try this;

Private Sub TxtPT_AfterUpdate ()
dim intQtyMsg as Integer

If Onhand(me.txtPt) < me.quantity Then
intQtyMsg = msgbox "You are trying to sell more items than you
currently
have in stock, do you want to continue?", vbYesNo
If intQtyMsg = vbYes Then
Exit Sub
Else
me.quantity = 0 <<or substitute other code here see ** below for
another way
End If
End If
End Sub

That alone should work (provided the function works) you could if you
preferred dim another variable let's say lngOnHandCnt as Long and start
with
lngOnHandCnt = OnHand(me.txtPt)
and replace the If Onhand (me.txtPt) with just If lngOnHandCnt <
.......
then the code under the Else part of the msgbox reply could be replaced
with
me.quantity = lngOnHandCnt '(the maximum number of items *on hand*)

If you can't get this to work post your actual code and say what errors
if
any you ae getting.

TonyT..

:

Tony,

Could you please assist me in incorporating this code in to my
database I
know very little about VB and its driving me up the wall!!

Thanks in advance

S


something like;

If Onhand(me.txtboxithPartNumberIn) < me.quantity Then
intQtyMsg = msgbox "You are trying to sell more items than you
currently
have in stock, do you want to continue?", vbYesNo
If intQtyMsg = vbYes Then
do whatever you want here
Else do other thing here
End If
End If

btw is quantity the field name or invoicedetail.quantity? If the
latter
I
would seriously rethink naming controls with . in them as referring
to
them
later could be a headache.

TonyT..

:


I have te following code which gives me the stock total for
individual
items................................

Function Onhand(itemCode 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 strProduct 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(itemCode) Then
'Initialize: Validate and convert parameters.
Set db = CurrentDb()

strProduct = itemCode
Debug.Print strSQL
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 ((ItemCode = """ & strProduct & """)" &
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.ItemCode = """ & strProduct &
""")"
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.itemcode = """ & strProduct &
""")"
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

I have a field called invoicedetail.quanity where the user can
enter
in
the
quantity of the item they are selling. What I would like to be able
to
do
is
the following... when the quantity is entered access then checks
the
quantity onhand is the item is in stock allow the user to continue
entry
but
if the item is out of stock warn the user but allow them to
continue
if
the
select Yes from a YES/NO message box.

Any help/suggestions would be much appreacited.

Thanks in advance

S
Click to expand...
Click to expand...
Click to expand...
 
T

TonyT

wordwrap if you copy and pasted?

can you copy and paste the code back here again please and highlight the
line (if different place casuing error)

TonyT

SG said:
Tony have done as instructed and get the following error?

Expected List Seperator or )


Code is as Follows

Private Sub Quantity_AfterUpdate()
Dim intQtyMsg As Integer

If Onhand(Me.itemCode) < Me.Quantity Then
intQtyMsg = msgbox ("You are trying to sell more items than you
currently
have in stock, do you want to continue?", vbYesNo) **** This line is
ighlighted when I get the error

If intQtyMsg = vbYes Then
Exit Sub
Else
Me.Quantity = 0

End If
End If
End Sub

Any Ideas?



TonyT said:
Sorry omitted the brackets;
intQtyMsg = msgbox ("You are trying to sell more items than you currently
have in stock, do you want to continue?", vbYesNo)
also;
tblinvoicedetail.itemCode
can be shortened too;
me.itemCode

apologies for that,

good luck and let me know how it goes,

TonyT..

Simon Glencross said:
Tony,

Here is the code ...... Please see ****

Private Sub Quantity_AfterUpdate()
Dim intQtyMsg As Integer

If Onhand(tblinvoicedetail.itemCode) < Me.Quantity Then
intQtyMsg = msgbox "You are trying to sell more items than you
currently
have in stock, do you want to continue?", vbYesNo ***** I get a syntex /
Compile error which highlights this line as being the problem



If intQtyMsg = vbYes Then
Exit Sub
Else
Me.Quantity = 0

End If
End If
End Sub

Thanks for all of your hep so far!!!

S

I'll try,
Replace txtboxwithPartNumberIn to the name of a control on your form
that
contains the unique identifyer for the part in question I'll call it
txtPT
from here on in, I hope that your quantity entry field is called
quantity
rather than as I suspect InvoiceDetail.Quantity (if it's the latter use
me.[InvoiceDetail.Quantity] instead or even better change the name to
remove
the fullstop altogether.

Try this;

Private Sub TxtPT_AfterUpdate ()
dim intQtyMsg as Integer

If Onhand(me.txtPt) < me.quantity Then
intQtyMsg = msgbox "You are trying to sell more items than you
currently
have in stock, do you want to continue?", vbYesNo
If intQtyMsg = vbYes Then
Exit Sub
Else
me.quantity = 0 <<or substitute other code here see ** below for
another way
End If
End If
End Sub

That alone should work (provided the function works) you could if you
preferred dim another variable let's say lngOnHandCnt as Long and start
with
lngOnHandCnt = OnHand(me.txtPt)
and replace the If Onhand (me.txtPt) with just If lngOnHandCnt <
.......
then the code under the Else part of the msgbox reply could be replaced
with
me.quantity = lngOnHandCnt '(the maximum number of items *on hand*)

If you can't get this to work post your actual code and say what errors
if
any you ae getting.

TonyT..

:

Tony,

Could you please assist me in incorporating this code in to my
database I
know very little about VB and its driving me up the wall!!

Thanks in advance

S


something like;

If Onhand(me.txtboxithPartNumberIn) < me.quantity Then
intQtyMsg = msgbox "You are trying to sell more items than you
currently
have in stock, do you want to continue?", vbYesNo
If intQtyMsg = vbYes Then
do whatever you want here
Else do other thing here
End If
End If

btw is quantity the field name or invoicedetail.quantity? If the
latter
I
would seriously rethink naming controls with . in them as referring
to
them
later could be a headache.

TonyT..

:


I have te following code which gives me the stock total for
individual
items................................

Function Onhand(itemCode 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 strProduct 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(itemCode) Then
'Initialize: Validate and convert parameters.
Set db = CurrentDb()

strProduct = itemCode
Debug.Print strSQL
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 ((ItemCode = """ & strProduct & """)" &
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.ItemCode = """ & strProduct &
""")"
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.itemcode = """ & strProduct &
""")"
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
Click to expand...
Click to expand...
Click to expand...
 
S

SG

Tony,

That works now BUT, if the item is in stock it resets to zero and will not
allow me to add a quantity?


TonyT said:
wordwrap if you copy and pasted?

can you copy and paste the code back here again please and highlight the
line (if different place casuing error)

TonyT

SG said:
Tony have done as instructed and get the following error?

Expected List Seperator or )


Code is as Follows

Private Sub Quantity_AfterUpdate()
Dim intQtyMsg As Integer

If Onhand(Me.itemCode) < Me.Quantity Then
intQtyMsg = msgbox ("You are trying to sell more items than you
currently
have in stock, do you want to continue?", vbYesNo) **** This line is
ighlighted when I get the error

If intQtyMsg = vbYes Then
Exit Sub
Else
Me.Quantity = 0

End If
End If
End Sub

Any Ideas?



TonyT said:
Sorry omitted the brackets;
intQtyMsg = msgbox ("You are trying to sell more items than you
currently
have in stock, do you want to continue?", vbYesNo)
also;
tblinvoicedetail.itemCode
can be shortened too;
me.itemCode

apologies for that,

good luck and let me know how it goes,

TonyT..

:

Tony,

Here is the code ...... Please see ****

Private Sub Quantity_AfterUpdate()
Dim intQtyMsg As Integer

If Onhand(tblinvoicedetail.itemCode) < Me.Quantity Then
intQtyMsg = msgbox "You are trying to sell more items than you
currently
have in stock, do you want to continue?", vbYesNo ***** I get a syntex
/
Compile error which highlights this line as being the problem



If intQtyMsg = vbYes Then
Exit Sub
Else
Me.Quantity = 0

End If
End If
End Sub

Thanks for all of your hep so far!!!

S

I'll try,
Replace txtboxwithPartNumberIn to the name of a control on your form
that
contains the unique identifyer for the part in question I'll call it
txtPT
from here on in, I hope that your quantity entry field is called
quantity
rather than as I suspect InvoiceDetail.Quantity (if it's the latter
use
me.[InvoiceDetail.Quantity] instead or even better change the name
to
remove
the fullstop altogether.

Try this;

Private Sub TxtPT_AfterUpdate ()
dim intQtyMsg as Integer

If Onhand(me.txtPt) < me.quantity Then
intQtyMsg = msgbox "You are trying to sell more items than you
currently
have in stock, do you want to continue?", vbYesNo
If intQtyMsg = vbYes Then
Exit Sub
Else
me.quantity = 0 <<or substitute other code here see ** below
for
another way
End If
End If
End Sub

That alone should work (provided the function works) you could if
you
preferred dim another variable let's say lngOnHandCnt as Long and
start
with
lngOnHandCnt = OnHand(me.txtPt)
and replace the If Onhand (me.txtPt) with just If lngOnHandCnt <
.......
then the code under the Else part of the msgbox reply could be
replaced
with
me.quantity = lngOnHandCnt '(the maximum number of items *on hand*)

If you can't get this to work post your actual code and say what
errors
if
any you ae getting.

TonyT..

:

Tony,

Could you please assist me in incorporating this code in to my
database I
know very little about VB and its driving me up the wall!!

Thanks in advance

S


something like;

If Onhand(me.txtboxithPartNumberIn) < me.quantity Then
intQtyMsg = msgbox "You are trying to sell more items than you
currently
have in stock, do you want to continue?", vbYesNo
If intQtyMsg = vbYes Then
do whatever you want here
Else do other thing here
End If
End If

btw is quantity the field name or invoicedetail.quantity? If the
latter
I
would seriously rethink naming controls with . in them as
referring
to
them
later could be a headache.

TonyT..

:


I have te following code which gives me the stock total for
individual
items................................

Function Onhand(itemCode 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 strProduct 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(itemCode) Then
'Initialize: Validate and convert parameters.
Set db = CurrentDb()

strProduct = itemCode
Debug.Print strSQL
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 ((ItemCode = """ & strProduct & """)" &
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.ItemCode = """ & strProduct &
""")"
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.itemcode = """ &
strProduct &
""")"
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
Click to expand...
Click to expand...
Click to expand...
 
T

TonyT

Do you also get the message telling you, you are trying to sell more than you
have in stock?
If yes, then put a breakpoint in the code and see what value the onhand
function is returning, if No, then I suspet a missing or mis aligned end If
or If somewhere, please re-post EXACT code as you have it now.

TonyT..

SG said:
Tony,

That works now BUT, if the item is in stock it resets to zero and will not
allow me to add a quantity?


TonyT said:
wordwrap if you copy and pasted?

can you copy and paste the code back here again please and highlight the
line (if different place casuing error)

TonyT

SG said:
Tony have done as instructed and get the following error?

Expected List Seperator or )


Code is as Follows

Private Sub Quantity_AfterUpdate()
Dim intQtyMsg As Integer

If Onhand(Me.itemCode) < Me.Quantity Then
intQtyMsg = msgbox ("You are trying to sell more items than you
currently
have in stock, do you want to continue?", vbYesNo) **** This line is
ighlighted when I get the error

If intQtyMsg = vbYes Then
Exit Sub
Else
Me.Quantity = 0

End If
End If
End Sub

Any Ideas?



Sorry omitted the brackets;
intQtyMsg = msgbox ("You are trying to sell more items than you
currently
have in stock, do you want to continue?", vbYesNo)
also;
tblinvoicedetail.itemCode
can be shortened too;
me.itemCode

apologies for that,

good luck and let me know how it goes,

TonyT..

:

Tony,

Here is the code ...... Please see ****

Private Sub Quantity_AfterUpdate()
Dim intQtyMsg As Integer

If Onhand(tblinvoicedetail.itemCode) < Me.Quantity Then
intQtyMsg = msgbox "You are trying to sell more items than you
currently
have in stock, do you want to continue?", vbYesNo ***** I get a syntex
/
Compile error which highlights this line as being the problem



If intQtyMsg = vbYes Then
Exit Sub
Else
Me.Quantity = 0

End If
End If
End Sub

Thanks for all of your hep so far!!!

S

I'll try,
Replace txtboxwithPartNumberIn to the name of a control on your form
that
contains the unique identifyer for the part in question I'll call it
txtPT
from here on in, I hope that your quantity entry field is called
quantity
rather than as I suspect InvoiceDetail.Quantity (if it's the latter
use
me.[InvoiceDetail.Quantity] instead or even better change the name
to
remove
the fullstop altogether.

Try this;

Private Sub TxtPT_AfterUpdate ()
dim intQtyMsg as Integer

If Onhand(me.txtPt) < me.quantity Then
intQtyMsg = msgbox "You are trying to sell more items than you
currently
have in stock, do you want to continue?", vbYesNo
If intQtyMsg = vbYes Then
Exit Sub
Else
me.quantity = 0 <<or substitute other code here see ** below
for
another way
End If
End If
End Sub

That alone should work (provided the function works) you could if
you
preferred dim another variable let's say lngOnHandCnt as Long and
start
with
lngOnHandCnt = OnHand(me.txtPt)
and replace the If Onhand (me.txtPt) with just If lngOnHandCnt <
.......
then the code under the Else part of the msgbox reply could be
replaced
with
me.quantity = lngOnHandCnt '(the maximum number of items *on hand*)

If you can't get this to work post your actual code and say what
errors
if
any you ae getting.

TonyT..

:

Tony,

Could you please assist me in incorporating this code in to my
database I
know very little about VB and its driving me up the wall!!

Thanks in advance

S


something like;

If Onhand(me.txtboxithPartNumberIn) < me.quantity Then
intQtyMsg = msgbox "You are trying to sell more items than you
currently
have in stock, do you want to continue?", vbYesNo
If intQtyMsg = vbYes Then
do whatever you want here
Else do other thing here
End If
End If

btw is quantity the field name or invoicedetail.quantity? If the
latter
I
would seriously rethink naming controls with . in them as
referring
to
them
later could be a headache.

TonyT..

:


I have te following code which gives me the stock total for
individual
items................................

Function Onhand(itemCode 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 strProduct 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(itemCode) Then
'Initialize: Validate and convert parameters.
Set db = CurrentDb()

strProduct = itemCode
Debug.Print strSQL
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 ((ItemCode = """ & strProduct & """)" &
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.ItemCode = """ & strProduct &
""")"
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)
Click to expand...
Click to expand...
Click to expand...
 
S

SG

Tony,

I get the later with no message see code below...


Private Sub Quantity_AfterUpdate()
Dim intQtyMsg As Integer

If Onhand(Me.ProductID) < Me.Quantity Then intQtyMsg = MsgBox("You are
trying to sell more items than you currently have in stock, do you want to
continue?", vbYesNo)



If intQtyMsg = vbYes Then
Exit Sub
Else
Me.Quantity = 0

End If
End Sub


P.S Thank you for your help so far much appreciated!

S


TonyT said:
Do you also get the message telling you, you are trying to sell more than
you
have in stock?
If yes, then put a breakpoint in the code and see what value the onhand
function is returning, if No, then I suspet a missing or mis aligned end
If
or If somewhere, please re-post EXACT code as you have it now.

TonyT..

SG said:
Tony,

That works now BUT, if the item is in stock it resets to zero and will
not
allow me to add a quantity?


TonyT said:
wordwrap if you copy and pasted?

can you copy and paste the code back here again please and highlight
the
line (if different place casuing error)

TonyT

:

Tony have done as instructed and get the following error?

Expected List Seperator or )


Code is as Follows

Private Sub Quantity_AfterUpdate()
Dim intQtyMsg As Integer

If Onhand(Me.itemCode) < Me.Quantity Then
intQtyMsg = msgbox ("You are trying to sell more items than you
currently
have in stock, do you want to continue?", vbYesNo) **** This line
is
ighlighted when I get the error

If intQtyMsg = vbYes Then
Exit Sub
Else
Me.Quantity = 0

End If
End If
End Sub

Any Ideas?



Sorry omitted the brackets;
intQtyMsg = msgbox ("You are trying to sell more items than you
currently
have in stock, do you want to continue?", vbYesNo)
also;
tblinvoicedetail.itemCode
can be shortened too;
me.itemCode

apologies for that,

good luck and let me know how it goes,

TonyT..

:

Tony,

Here is the code ...... Please see ****

Private Sub Quantity_AfterUpdate()
Dim intQtyMsg As Integer

If Onhand(tblinvoicedetail.itemCode) < Me.Quantity Then
intQtyMsg = msgbox "You are trying to sell more items than you
currently
have in stock, do you want to continue?", vbYesNo ***** I get a
syntex
/
Compile error which highlights this line as being the problem



If intQtyMsg = vbYes Then
Exit Sub
Else
Me.Quantity = 0

End If
End If
End Sub

Thanks for all of your hep so far!!!

S

I'll try,
Replace txtboxwithPartNumberIn to the name of a control on your
form
that
contains the unique identifyer for the part in question I'll call
it
txtPT
from here on in, I hope that your quantity entry field is called
quantity
rather than as I suspect InvoiceDetail.Quantity (if it's the
latter
use
me.[InvoiceDetail.Quantity] instead or even better change the
name
to
remove
the fullstop altogether.

Try this;

Private Sub TxtPT_AfterUpdate ()
dim intQtyMsg as Integer

If Onhand(me.txtPt) < me.quantity Then
intQtyMsg = msgbox "You are trying to sell more items than you
currently
have in stock, do you want to continue?", vbYesNo
If intQtyMsg = vbYes Then
Exit Sub
Else
me.quantity = 0 <<or substitute other code here see **
below
for
another way
End If
End If
End Sub

That alone should work (provided the function works) you could if
you
preferred dim another variable let's say lngOnHandCnt as Long and
start
with
lngOnHandCnt = OnHand(me.txtPt)
and replace the If Onhand (me.txtPt) with just If lngOnHandCnt <
.......
then the code under the Else part of the msgbox reply could be
replaced
with
me.quantity = lngOnHandCnt '(the maximum number of items *on
hand*)

If you can't get this to work post your actual code and say what
errors
if
any you ae getting.

TonyT..

:

Tony,

Could you please assist me in incorporating this code in to my
database I
know very little about VB and its driving me up the wall!!

Thanks in advance

S


something like;

If Onhand(me.txtboxithPartNumberIn) < me.quantity Then
intQtyMsg = msgbox "You are trying to sell more items than
you
currently
have in stock, do you want to continue?", vbYesNo
If intQtyMsg = vbYes Then
do whatever you want here
Else do other thing here
End If
End If

btw is quantity the field name or invoicedetail.quantity? If
the
latter
I
would seriously rethink naming controls with . in them as
referring
to
them
later could be a headache.

TonyT..

:


I have te following code which gives me the stock total for
individual
items................................

Function Onhand(itemCode 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 strProduct 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(itemCode) Then
'Initialize: Validate and convert parameters.
Set db = CurrentDb()

strProduct = itemCode
Debug.Print strSQL
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 ((ItemCode = """ & strProduct & """)" &
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.ItemCode = """ & strProduct
&
""")"
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)
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

NO SPAM PLEASE - Stock Value Help 2
Stock Value Reposted NO SPAM REPLIES PLEASE!! 1
Stock Value 1
Access and VB 26
Please HELP!! 4
Stock Value Calculation 0
VBA You entered an expression which has no value 9
Need Help on Code- Attention Allen Browne Please 10

Top