Inventory and recorder help please

Mike · Jan 11, 2004

Hi,

I have created an Inventory and Transaction database but
need to be able to deduct a transaction on an specific
item from the UnitsInStock in the Inventory database.

On my frmTransaction which is bounded to my
tblTransaction I have done this deduction based on
querires and it shows me how many units of that specific
item we have after a Transaction has been made, but
tblInventory still has the old value (Value before a
Transaction) in it. Is ther any way to refelect these
changes dynamically in to the tblInventory to have up to
date and correct values in the table Inventory?

Any help, suggestion on how to approach this problem and
possibly hints on doing that is greatlly appreciated.

Thank you in advance for your help.

Regards,

Mike

Allen Browne · Jan 12, 2004

Mike, how are you doing this?

Are you running an Update query in the AfterUpdate event procedure of the
form (frmTransaction)? If so, will your code deduct the amount twice if the
user changes something (since Form_AfterUpdate runs again)?

Will it make the change correctly if the user goes back and alters the
quantity in an existing record?

Will it restore the quantities if the user deletes rows from frmTransaction?
Even if they select multiple rows for deletion at once?

For an introduction to the issues, see:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html

Mike · Jan 13, 2004

Allen,

What I have done, is a query based on my tblProduct
(which is my Inventory table and has a feild UnitsIn) and
the tblTransaction (which is used for sending materials
out of the shop and has a feild quantity)). Based on that
query which deduct Quantity from UnitsIn and give me a
number which is the new unitsIn stock, I have created a
from and that form has become a subform on my
frmTransaction. This gives me the new number after x
quantity is sold. SO no codes have been involved so far,
but As you can see, there is a problem with this as my
original UnitsIn (Units in Stock) in the Inventory table
doesn't change.

here what have:

tblOrders (receiveable, has UnitsIn, UnitPrice,
ProductID, ProductName, etc.)

tblProduct (My Inventory, when a ProductID is typed in
the ProductID feild of frmProduct, it gats populated
based on the info from frmReceivable)

tblTransaction (has all the feild for frmOrders,
fromProduct plus the quantity and customer information).

tblInvoice (has InvoiceNUmber, CustomerName, InvoiceDate)
and frmInvoice,which is continous, On this from is a
Transaction form in the form of a subform which has all
the feilds of the frmInventory plus the qantity sold).

Above is wrapping up my Inventory database but there is a
problem as I really don't know how to handel my new units
in stock.

Also, How one can use the code you posted in your website?

Regards,

Mike

Mike · Jan 13, 2004

Allen,

How can the DSum() function you had explained can help me
in my case?

Regards,

Mike

Allen Browne · Jan 13, 2004

Hi Mike

Subtracting the quantity from tblProduct.UnitsIn every time there is a
transaction won't work.

You could Execute an Update query statement in the AfterUpdate event of the
form where transactions are entered. But picture this:
I enter a row to order 100 of a product. When I save the row, the code runs
and subtracts 100 items. Then I realise I should have typed 10, so I go back
and change 100 to 10. When the record is saved, the code runs again, and
subtracts 10 items. The field tblProduct.UnitsIn is now 100 less than what
it should be. And have you figured out how to restore those values when rows
are deleted?

To use the code in the link:
1. In the Database window, click the Module tab, and then New.
Access opens a code window.

2. From the Tools menu, choose References. Check the box beside:
Microsoft DAO 3.6 Library (or 3.51 if you use Access 97).

3. Copy the code from the web page, starting with "Function ..." and ending
with "End Function".

4. Paste into your module.

5. Choose Compile from the Debug menu to check Access understands it okay.

6. Save the module. A name like "Module1" will do.

7. Use the function just like any other in Access. For example, if you have
a form that has a ProductID text box, you can add another text box with a
Control Source of:
=OnHand([ProductID])

Mike · Jan 14, 2004

Allen,

Thanks for getting back to me. I made changes in the code
to reflect the feild s that I have inmy tables very
carefully and I think I understand/or can follow what you
are trying to do here. The compile went fine but the text
box I created and used the onHand as you explaned below
as the control source, I get the #Name.

Any thoughts on where I must have gone wrong?

Regards,

Mike

Mike said:
-----Original Message-----
Hi Mike

Subtracting the quantity from tblProduct.UnitsIn every time there is a
transaction won't work.

You could Execute an Update query statement in the AfterUpdate event of the
form where transactions are entered. But picture this:
I enter a row to order 100 of a product. When I save the row, the code runs
and subtracts 100 items. Then I realise I should have typed 10, so I go back
and change 100 to 10. When the record is saved, the code runs again, and
subtracts 10 items. The field tblProduct.UnitsIn is now 100 less than what
it should be. And have you figured out how to restore those values when rows
are deleted?

To use the code in the link:
1. In the Database window, click the Module tab, and then New.
Access opens a code window.

2. From the Tools menu, choose References. Check the box beside:
Microsoft DAO 3.6 Library (or 3.51 if you use Access 97).

3. Copy the code from the web page, starting with "Function ..." and ending
with "End Function".

4. Paste into your module.

5. Choose Compile from the Debug menu to check Access understands it okay.

6. Save the module. A name like "Module1" will do.

7. Use the function just like any other in Access. For example, if you have
a form that has a ProductID text box, you can add another text box with a
Control Source of:
=OnHand([ProductID])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Mike said:

What I have done, is a query based on my tblProduct
(which is my Inventory table and has a feild UnitsIn) and
the tblTransaction (which is used for sending materials
out of the shop and has a feild quantity)). Based on that
query which deduct Quantity from UnitsIn and give me a
number which is the new unitsIn stock, I have created a
from and that form has become a subform on my
frmTransaction. This gives me the new number after x
quantity is sold. SO no codes have been involved so far,
but As you can see, there is a problem with this as my
original UnitsIn (Units in Stock) in the Inventory table
doesn't change.

here what have:

tblOrders (receiveable, has UnitsIn, UnitPrice,
ProductID, ProductName, etc.)

tblProduct (My Inventory, when a ProductID is typed in
the ProductID feild of frmProduct, it gats populated
based on the info from frmReceivable)

tblTransaction (has all the feild for frmOrders,
fromProduct plus the quantity and customer information).

tblInvoice (has InvoiceNUmber, CustomerName, InvoiceDate)
and frmInvoice,which is continous, On this from is a
Transaction form in the form of a subform which has all
the feilds of the frmInventory plus the qantity sold).

Above is wrapping up my Inventory database but there is a
problem as I really don't know how to handel my new units
in stock.

Also, How one can use the code you posted in your website?

Regards,

Mike event
procedure of the the
amount twice if the back
and alters the rows
from frmTransaction? up
to

Click to expand...

.

Allen Browne · Jan 14, 2004

Open the Immediate window (Ctrl+G)

Assuming you have a product 2, enter:
? OnHand(2)
If this works, the problem is with the text box: did you include the equal
sign?

If it does not work, the problem is with the function.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Mike said:
Allen,

Thanks for getting back to me. I made changes in the code
to reflect the feild s that I have inmy tables very
carefully and I think I understand/or can follow what you
are trying to do here. The compile went fine but the text
box I created and used the onHand as you explaned below
as the control source, I get the #Name.

Any thoughts on where I must have gone wrong?

Regards,

Mike

-----Original Message-----
Hi Mike

Subtracting the quantity from tblProduct.UnitsIn every time there is a
transaction won't work.

You could Execute an Update query statement in the AfterUpdate event of the
form where transactions are entered. But picture this:
I enter a row to order 100 of a product. When I save the row, the code runs
and subtracts 100 items. Then I realise I should have typed 10, so I go back
and change 100 to 10. When the record is saved, the code runs again, and
subtracts 10 items. The field tblProduct.UnitsIn is now 100 less than what
it should be. And have you figured out how to restore those values when rows
are deleted?

To use the code in the link:
1. In the Database window, click the Module tab, and then New.
Access opens a code window.

2. From the Tools menu, choose References. Check the box beside:
Microsoft DAO 3.6 Library (or 3.51 if you use Access 97).

3. Copy the code from the web page, starting with "Function ..." and ending
with "End Function".

4. Paste into your module.

5. Choose Compile from the Debug menu to check Access understands it okay.

6. Save the module. A name like "Module1" will do.

7. Use the function just like any other in Access. For example, if you have
a form that has a ProductID text box, you can add another text box with a
Control Source of:
=OnHand([ProductID])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Mike said:

What I have done, is a query based on my tblProduct
(which is my Inventory table and has a feild UnitsIn) and
the tblTransaction (which is used for sending materials
out of the shop and has a feild quantity)). Based on that
query which deduct Quantity from UnitsIn and give me a
number which is the new unitsIn stock, I have created a
from and that form has become a subform on my
frmTransaction. This gives me the new number after x
quantity is sold. SO no codes have been involved so far,
but As you can see, there is a problem with this as my
original UnitsIn (Units in Stock) in the Inventory table
doesn't change.

here what have:

tblOrders (receiveable, has UnitsIn, UnitPrice,
ProductID, ProductName, etc.)

tblProduct (My Inventory, when a ProductID is typed in
the ProductID feild of frmProduct, it gats populated
based on the info from frmReceivable)

tblTransaction (has all the feild for frmOrders,
fromProduct plus the quantity and customer information).

tblInvoice (has InvoiceNUmber, CustomerName, InvoiceDate)
and frmInvoice,which is continous, On this from is a
Transaction form in the form of a subform which has all
the feilds of the frmInventory plus the qantity sold).

Above is wrapping up my Inventory database but there is a
problem as I really don't know how to handel my new units
in stock.

Also, How one can use the code you posted in your website?

Regards,

Mike
-----Original Message-----
Mike, how are you doing this?

Are you running an Update query in the AfterUpdate event
procedure of the
form (frmTransaction)? If so, will your code deduct the
amount twice if the
user changes something (since Form_AfterUpdate runs
again)?

Will it make the change correctly if the user goes back
and alters the
quantity in an existing record?

Will it restore the quantities if the user deletes rows
from frmTransaction?
Even if they select multiple rows for deletion at once?

For an introduction to the issues, see:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html

I have created an Inventory and Transaction database
but
need to be able to deduct a transaction on an specific
item from the UnitsInStock in the Inventory database.

On my frmTransaction which is bounded to my
tblTransaction I have done this deduction based on
querires and it shows me how many units of that
specific
item we have after a Transaction has been made, but
tblInventory still has the old value (Value before a
Transaction) in it. Is ther any way to refelect these
changes dynamically in to the tblInventory to have up
to
date and correct values in the table Inventory?

Any help, suggestion on how to approach this problem
and
possibly hints on doing that is greatlly appreciated.

Thank you in advance for your help.

Regards,

Mike

Click to expand...

.

Click to expand...

Mike · Jan 14, 2004

Allen,

Sorry to bother you again, But here is the situation, I
get a type mismatch error on the following line:

lngProduct=vProductID

gets highlighted
and it has the productID value that is in my table in it.
I have three tables:

tblProduct (ProductID, UnitsIn, LastStckRcvd, UnitPrice,
etc.)
tblInvoice (InvoiceNumber, InvoiceDate, CustomerName,
JobNumber)
tblTransaction (ProductID, InvoiveNumber, InvoiceDate,
Quantity (i.e.Sold or used)

And here is how I have modified the code and if it is
possible please take a look at it - And Thank you

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 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 (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

Regards,

Mike

Mike said:
-----Original Message-----
Open the Immediate window (Ctrl+G)

Assuming you have a product 2, enter:
? OnHand(2)
If this works, the problem is with the text box: did you include the equal
sign?

If it does not work, the problem is with the function.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Mike said:

Allen,

Thanks for getting back to me. I made changes in the code
to reflect the feild s that I have inmy tables very
carefully and I think I understand/or can follow what you
are trying to do here. The compile went fine but the text
box I created and used the onHand as you explaned below
as the control source, I get the #Name.

Any thoughts on where I must have gone wrong?

Regards,

Mike

-----Original Message-----
Hi Mike

Subtracting the quantity from tblProduct.UnitsIn every time there is a
transaction won't work.

You could Execute an Update query statement in the AfterUpdate event of the
form where transactions are entered. But picture this:
I enter a row to order 100 of a product. When I save

Click to expand...

the
row, the code runs

and subtracts 100 items. Then I realise I should have typed 10, so I go back
and change 100 to 10. When the record is saved, the

Click to expand...

code
runs again, and

subtracts 10 items. The field tblProduct.UnitsIn is

Click to expand...

now
100 less than what

it should be. And have you figured out how to restore those values when rows
are deleted?

To use the code in the link:
1. In the Database window, click the Module tab, and then New.
Access opens a code window.

2. From the Tools menu, choose References. Check the

Click to expand...

box
beside:

Microsoft DAO 3.6 Library (or 3.51 if you use Access 97).

3. Copy the code from the web page, starting with "Function ..." and ending
with "End Function".

4. Paste into your module.

5. Choose Compile from the Debug menu to check Access understands it okay.

6. Save the module. A name like "Module1" will do.

7. Use the function just like any other in Access. For example, if you have
a form that has a ProductID text box, you can add another text box with a
Control Source of:
=OnHand([ProductID])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

What I have done, is a query based on my tblProduct
(which is my Inventory table and has a feild

Click to expand...

UnitsIn)
and

the tblTransaction (which is used for sending materials
out of the shop and has a feild quantity)). Based on that
query which deduct Quantity from UnitsIn and give me a
number which is the new unitsIn stock, I have created a
from and that form has become a subform on my
frmTransaction. This gives me the new number after x
quantity is sold. SO no codes have been involved so far,
but As you can see, there is a problem with this as my
original UnitsIn (Units in Stock) in the Inventory table
doesn't change.

here what have:

tblOrders (receiveable, has UnitsIn, UnitPrice,
ProductID, ProductName, etc.)

tblProduct (My Inventory, when a ProductID is typed in
the ProductID feild of frmProduct, it gats populated
based on the info from frmReceivable)

tblTransaction (has all the feild for frmOrders,
fromProduct plus the quantity and customer information).

tblInvoice (has InvoiceNUmber, CustomerName, InvoiceDate)
and frmInvoice,which is continous, On this from is a
Transaction form in the form of a subform which has all
the feilds of the frmInventory plus the qantity sold).

Above is wrapping up my Inventory database but there is a
problem as I really don't know how to handel my new units
in stock.

Also, How one can use the code you posted in your website?

Regards,

Mike
-----Original Message-----
Mike, how are you doing this?

Are you running an Update query in the AfterUpdate event
procedure of the
form (frmTransaction)? If so, will your code deduct the
amount twice if the
user changes something (since Form_AfterUpdate runs
again)?

Will it make the change correctly if the user goes back
and alters the
quantity in an existing record?

Will it restore the quantities if the user deletes rows
from frmTransaction?
Even if they select multiple rows for deletion at once?

For an introduction to the issues, see:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html

I have created an Inventory and Transaction database
but
need to be able to deduct a transaction on an specific
item from the UnitsInStock in the Inventory database.

On my frmTransaction which is bounded to my
tblTransaction I have done this deduction based on
querires and it shows me how many units of that
specific
item we have after a Transaction has been made, but
tblInventory still has the old value (Value before a
Transaction) in it. Is ther any way to refelect these
changes dynamically in to the tblInventory to

Click to expand...

have
up

to
date and correct values in the table Inventory?

Any help, suggestion on how to approach this problem
and
possibly hints on doing that is greatlly appreciated.

Thank you in advance for your help.

Regards,

Mike

.

Click to expand...

Click to expand...

.

Allen Browne · Jan 14, 2004

The code assumes the ProductID field is a Number.
It appears that yours is Text?

If so, you will need to modify the code so it uses a string variable instead
of a long, and it includes the extra quote marks as delimiters in the WHERE
clause.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Mike said:
Allen,

Sorry to bother you again, But here is the situation, I
get a type mismatch error on the following line:

lngProduct=vProductID

gets highlighted
and it has the productID value that is in my table in it.
I have three tables:

tblProduct (ProductID, UnitsIn, LastStckRcvd, UnitPrice,
etc.)
tblInvoice (InvoiceNumber, InvoiceDate, CustomerName,
JobNumber)
tblTransaction (ProductID, InvoiveNumber, InvoiceDate,
Quantity (i.e.Sold or used)

And here is how I have modified the code and if it is
possible please take a look at it - And Thank you

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 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 (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

Regards,

Mike

-----Original Message-----
Open the Immediate window (Ctrl+G)

Assuming you have a product 2, enter:
? OnHand(2)
If this works, the problem is with the text box: did you include the equal
sign?

If it does not work, the problem is with the function.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Mike said:

Allen,

Thanks for getting back to me. I made changes in the code
to reflect the feild s that I have inmy tables very
carefully and I think I understand/or can follow what you
are trying to do here. The compile went fine but the text
box I created and used the onHand as you explaned below
as the control source, I get the #Name.

Any thoughts on where I must have gone wrong?

Regards,

Mike
-----Original Message-----
Hi Mike

Subtracting the quantity from tblProduct.UnitsIn every
time there is a
transaction won't work.

You could Execute an Update query statement in the
AfterUpdate event of the
form where transactions are entered. But picture this:
I enter a row to order 100 of a product. When I save the
row, the code runs
and subtracts 100 items. Then I realise I should have
typed 10, so I go back
and change 100 to 10. When the record is saved, the code
runs again, and
subtracts 10 items. The field tblProduct.UnitsIn is now
100 less than what
it should be. And have you figured out how to restore
those values when rows
are deleted?

To use the code in the link:
1. In the Database window, click the Module tab, and
then New.
Access opens a code window.

2. From the Tools menu, choose References. Check the box
beside:
Microsoft DAO 3.6 Library (or 3.51 if you
use Access 97).

3. Copy the code from the web page, starting
with "Function ..." and ending
with "End Function".

4. Paste into your module.

5. Choose Compile from the Debug menu to check Access
understands it okay.

6. Save the module. A name like "Module1" will do.

7. Use the function just like any other in Access. For
example, if you have
a form that has a ProductID text box, you can add
another text box with a
Control Source of:
=OnHand([ProductID])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

What I have done, is a query based on my tblProduct
(which is my Inventory table and has a feild UnitsIn)
and
the tblTransaction (which is used for sending materials
out of the shop and has a feild quantity)). Based on
that
query which deduct Quantity from UnitsIn and give me a
number which is the new unitsIn stock, I have created a
from and that form has become a subform on my
frmTransaction. This gives me the new number after x
quantity is sold. SO no codes have been involved so
far,
but As you can see, there is a problem with this as my
original UnitsIn (Units in Stock) in the Inventory
table
doesn't change.

here what have:

tblOrders (receiveable, has UnitsIn, UnitPrice,
ProductID, ProductName, etc.)

tblProduct (My Inventory, when a ProductID is typed in
the ProductID feild of frmProduct, it gats populated
based on the info from frmReceivable)

tblTransaction (has all the feild for frmOrders,
fromProduct plus the quantity and customer
information).

tblInvoice (has InvoiceNUmber, CustomerName,
InvoiceDate)
and frmInvoice,which is continous, On this from is a
Transaction form in the form of a subform which has all
the feilds of the frmInventory plus the qantity sold).

Above is wrapping up my Inventory database but there
is a
problem as I really don't know how to handel my new
units
in stock.

Also, How one can use the code you posted in your
website?

Regards,

Mike
-----Original Message-----
Mike, how are you doing this?

Are you running an Update query in the AfterUpdate
event
procedure of the
form (frmTransaction)? If so, will your code deduct
the
amount twice if the
user changes something (since Form_AfterUpdate runs
again)?

Will it make the change correctly if the user goes
back
and alters the
quantity in an existing record?

Will it restore the quantities if the user deletes
rows
from frmTransaction?
Even if they select multiple rows for deletion at
once?

For an introduction to the issues, see:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html

I have created an Inventory and Transaction database
but
need to be able to deduct a transaction on an
specific
item from the UnitsInStock in the Inventory
database.

On my frmTransaction which is bounded to my
tblTransaction I have done this deduction based on
querires and it shows me how many units of that
specific
item we have after a Transaction has been made, but
tblInventory still has the old value (Value before a
Transaction) in it. Is ther any way to refelect
these
changes dynamically in to the tblInventory to have
up
to
date and correct values in the table Inventory?

Any help, suggestion on how to approach this problem
and
possibly hints on doing that is greatlly
appreciated.

Thank you in advance for your help.

Regards,

Mike

Click to expand...

Click to expand...

Mike · Jan 15, 2004

Hi Allen,

Is this what you meant by placing quote in the where
clause: NOTICE "lngProduct" please.

strSQL = "SELECT TOP 1 DateReceived, UnitsIn FROM
tblProduct " & _
"WHERE ((ProductID = " & "lngProduct" & ")" &
strDateClause & _
") ORDER BY DateReceived DESC;"

I did change the type of the ProductID to String as you
were right, I was using Text type.

Now I'm getting too few parameters error.

Thx,

Mike

Mike said:
-----Original Message-----
The code assumes the ProductID field is a Number.
It appears that yours is Text?

If so, you will need to modify the code so it uses a string variable instead
of a long, and it includes the extra quote marks as delimiters in the WHERE
clause.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Mike said:

Allen,

Sorry to bother you again, But here is the situation, I
get a type mismatch error on the following line:

lngProduct=vProductID

gets highlighted
and it has the productID value that is in my table in it.
I have three tables:

tblProduct (ProductID, UnitsIn, LastStckRcvd, UnitPrice,
etc.)
tblInvoice (InvoiceNumber, InvoiceDate, CustomerName,
JobNumber)
tblTransaction (ProductID, InvoiveNumber, InvoiceDate,
Quantity (i.e.Sold or used)

And here is how I have modified the code and if it is
possible please take a look at it - And Thank you

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 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 (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

Regards,

Mike

-----Original Message-----
Open the Immediate window (Ctrl+G)

Assuming you have a product 2, enter:
? OnHand(2)
If this works, the problem is with the text box: did

Click to expand...

you
include the equal

sign?

If it does not work, the problem is with the function.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks for getting back to me. I made changes in the code
to reflect the feild s that I have inmy tables very
carefully and I think I understand/or can follow

Click to expand...

what
you

are trying to do here. The compile went fine but the text
box I created and used the onHand as you explaned below
as the control source, I get the #Name.

Any thoughts on where I must have gone wrong?

Regards,

Mike
-----Original Message-----
Hi Mike

Subtracting the quantity from tblProduct.UnitsIn every
time there is a
transaction won't work.

You could Execute an Update query statement in the
AfterUpdate event of the
form where transactions are entered. But picture this:
I enter a row to order 100 of a product. When I

Click to expand...

save
the

row, the code runs
and subtracts 100 items. Then I realise I should have
typed 10, so I go back
and change 100 to 10. When the record is saved, the code
runs again, and
subtracts 10 items. The field tblProduct.UnitsIn is now
100 less than what
it should be. And have you figured out how to restore
those values when rows
are deleted?

To use the code in the link:
1. In the Database window, click the Module tab, and
then New.
Access opens a code window.

2. From the Tools menu, choose References. Check

Click to expand...

the
box

beside:
Microsoft DAO 3.6 Library (or 3.51 if you
use Access 97).

3. Copy the code from the web page, starting
with "Function ..." and ending
with "End Function".

4. Paste into your module.

5. Choose Compile from the Debug menu to check Access
understands it okay.

6. Save the module. A name like "Module1" will do.

7. Use the function just like any other in Access. For
example, if you have
a form that has a ProductID text box, you can add
another text box with a
Control Source of:
=OnHand([ProductID])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

What I have done, is a query based on my tblProduct
(which is my Inventory table and has a feild UnitsIn)
and
the tblTransaction (which is used for sending materials
out of the shop and has a feild quantity)). Based on
that
query which deduct Quantity from UnitsIn and give me a
number which is the new unitsIn stock, I have created a
from and that form has become a subform on my
frmTransaction. This gives me the new number after x
quantity is sold. SO no codes have been involved so
far,
but As you can see, there is a problem with this

Click to expand...

as
my

original UnitsIn (Units in Stock) in the Inventory
table
doesn't change.

here what have:

tblOrders (receiveable, has UnitsIn, UnitPrice,
ProductID, ProductName, etc.)

tblProduct (My Inventory, when a ProductID is

Click to expand...

typed
in

the ProductID feild of frmProduct, it gats populated
based on the info from frmReceivable)

tblTransaction (has all the feild for frmOrders,
fromProduct plus the quantity and customer
information).

tblInvoice (has InvoiceNUmber, CustomerName,
InvoiceDate)
and frmInvoice,which is continous, On this from is a
Transaction form in the form of a subform which

Click to expand...

has
all

the feilds of the frmInventory plus the qantity sold).

Above is wrapping up my Inventory database but there
is a
problem as I really don't know how to handel my new
units
in stock.

Also, How one can use the code you posted in your
website?

Regards,

Mike
-----Original Message-----
Mike, how are you doing this?

Are you running an Update query in the AfterUpdate
event
procedure of the
form (frmTransaction)? If so, will your code deduct
the
amount twice if the
user changes something (since Form_AfterUpdate runs
again)?

Will it make the change correctly if the user goes
back
and alters the
quantity in an existing record?

Will it restore the quantities if the user deletes
rows
from frmTransaction?
Even if they select multiple rows for deletion at
once?

For an introduction to the issues, see:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html

I have created an Inventory and Transaction database
but
need to be able to deduct a transaction on an
specific
item from the UnitsInStock in the Inventory
database.

On my frmTransaction which is bounded to my
tblTransaction I have done this deduction

Click to expand...

based
on

querires and it shows me how many units of that
specific
item we have after a Transaction has been

Click to expand...

made,
but

tblInventory still has the old value (Value before a
Transaction) in it. Is ther any way to refelect
these
changes dynamically in to the tblInventory to have
up
to
date and correct values in the table Inventory?

Any help, suggestion on how to approach this problem
and
possibly hints on doing that is greatlly
appreciated.

Thank you in advance for your help.

Regards,

Mike

Click to expand...

Click to expand...

.

Allen Browne · Jan 15, 2004

If lngProduct is a string variable that contains "zzz", you need a string
that contains:
WHERE (ProductID = "zzz")

To get the quote marks inside a string, you have to double them up so Access
does not thing it's the end of the string, i.e. you need:
"WHERE (ProductID = ""zzz"")"

To concatenate the variable into the string and get the quote marks around
it, try:
"WHERE (ProductID = """ & lngProduct & """)"

Mike · Jan 16, 2004

Allen,

Thanks,

It did help me to get thought the 3/4 of the program but
still getting errors o the From Sayntex.

I will try to see what the problem is. Hopefully I will
fix this and this becomes a good learning experience.

Thank you very much.

Regards,

Mike