Inventory transactions???????? 1

[DaveMac]

OK. I have a tblitems and a field in the tbl that is beginning inventory. I want to have a check box on my open orders form that when checked it subtracts the inventory. You would be amazed at how hard this is when you know squat about code.

BTW the check box is shipped and I am going to use the same logic to but inventory in by having another box for produced. I guess that is backwards but that is what Access has driven me to so oh well.

Thanks,
David
[sig][/sig]

 

[Robertd]

hi David

Ok the your stock is controled depends on many things, how complicated you want to get, how much control you want, and what your model requires, you gotta start somewhare!

as you are using a check box to indicate that the order was shipped you could use an update query to process the inventory item quantities, be aware what would heppen if the users were to uncheck then recheck the this control? will it debit debit the qty's twice??, an issue you need to consider. here is an outline of a typical sql to update the inventory quantities.

<SQL>

mySQL = &quot;UPDATE Inventory INNER JOIN&quot;
mySQL = mySQL & &quot; CustOrder ON&quot;
mySQL = mySQL & &quot; Inventory.ItemID = CustOrderDet.ItemID&quot;
mySQL = mySQL & &quot; SET Inventory.QtyOH =&quot;
mySQL = mySQL & &quot; [QtyOH]-[CustOrderDetails].[QTY]&quot;
mySQL = mySQL & &quot; WHERE&quot;
mySQL = mySQL & &quot; (((CustOrder.TransID)=&quot;
mySQL = mySQL & ThisTransID & &quot);&quot;

this could be used in the afterupdate of the check box, then add some field as a flag to allow this to only run once for each order maybe a &quot;dateShiped&quot; field.
and check that this isnull before running

if isnull(me.dateShiped) then
dim mySQL as string
mySQL = &quot;yourSQLstatement&quot;
DoCmd.SetWarnings (False)
docmd.runsql
DoCmd.SetWarnings (True)
me.dateShiped = date
end if

some ideas
HTH

[sig]<p>Robert Dwyer<br><a href=mailto:rdwyer@orion-online.com.au>rdwyer@orion-online.com.au</a><br>[/sig]

 

[WP]

Dave,

Can I make a suggestion? Rather than use check boxes for each status why not use a field on your order table to hold the order status ( Status ).

You could have a table of valid statuses :

tblStatus
Type Status
Order Received
Order Shipped
Order Cancelled

This introduces much more flexibility. I've included status type too which will allow tblStatus to hold other statuses too.

By making the default value of the Order table Status field &quot;Received&quot; on your add form the status would be set to &quot;Received&quot;.

On you open orders list you could have the status field as a combo box with row Source : SELECT Status FROM tblStatus WHERE Type=&quot;Order&quot;;
Another technique I like (but only if you have the space on your row) is to have command buttons Received,Shipped and Cancelled ( or just R S and C) which you can put code behind.

When changing from &quot;Received&quot; to &quot;Shipped&quot; on the After Update you would run the SQL Robert provides.

If cboStatus=&quot;Shipped&quot; AND Isnull(me.ShipDate) then
mySQL = &quot;UPDATE Inventory INNER JOIN&quot;
mySQL = mySQL & &quot; CustOrder ON&quot;
mySQL = mySQL & &quot; Inventory.ItemID = CustOrderDet.ItemID&quot;
mySQL = mySQL & &quot; SET Inventory.QtyOH =&quot;
mySQL = mySQL & &quot; [QtyOH]-[CustOrderDetails].[QTY]&quot;
mySQL = mySQL & &quot; WHERE&quot;
mySQL = mySQL & &quot; (((CustOrder.TransID)=&quot;
mySQL = mySQL & ThisTransID & &quot);&quot;
Docmd.RunSQL mySQL

'Also update the Shipped Date :

mySQL=&quot;UPDATE CustOrderDet SET ShipDate=Now();&quot;
Docmd.RunSQL mySQL

Else
'And converersely if the a shipped order is put back to Received or Cancelled :

mySQL = &quot;UPDATE Inventory INNER JOIN&quot;
mySQL = mySQL & &quot; CustOrder ON&quot;
mySQL = mySQL & &quot; Inventory.ItemID = CustOrderDet.ItemID&quot;
mySQL = mySQL & &quot; SET Inventory.QtyOH =&quot;
mySQL = mySQL & &quot; [QtyOH]+[CustOrderDetails].[QTY]&quot;
mySQL = mySQL & &quot; WHERE&quot;
mySQL = mySQL & &quot; (((CustOrder.TransID)=&quot;
mySQL = mySQL & ThisTransID & &quot);&quot;
Docmd.RunSQL mySQL

'Also reset the Shipped Date :

mySQL=&quot;UPDATE CustOrderDet SET ShipDate=Null;&quot;
Docmd.RunSQL mySQL
Endif


Hope this helps,


[sig]<p>Bill Paton<br><a href=mailto:william.paton@ubsw.com>william.paton@ubsw.com</a><br><a href=

http://www.neptune400.co.uk/>http://www.neptune400.co.uk/</a><br>

Check out my website ![/sig]

 

[jendendav]

Forgive me for borrowing this thread for talking to Bill. I have been reading your posting for a couple of weeks and have some questions regarding inventory programs.

I built a rather simplistic idea to start with. All of my transactions (adding or subtracting) went into the same table with subtractions indicated by a negative number. Then I used a query to sum up my on hand totals. As you may imagine, this has caused a few problems related to data entry errors.

I have been wondering about making two tables, one for adding items to inventory and one for subtraction items from inventory. Of course there is still the possibility they will select the wrong form for input but you have to start somewhere. I'm not using orders to reduce inventory because this was just a simple record keeping of internal usage. We aren't selling anything.

Then I wonder if my on hand qty should be in my products table? I was under the impression that holding a changing figure in a static table might not be wise. Also, I dont recall the ability to make a calculated field in a table. I know I can do it in a query.

On a related note I left a new thread two or three days ago because a form I was trying to build wasn't allowing entry of new records. The form was built on a query built from one of these tables and the query with the calculated control. I got one answer, a very logical one that said set my allow additions to yes. Good answer but that was not the problem. I really suspect it has to do with the construction of the query. Do you know?

Thanks for allowing me to hijack this thread.

Jen [sig][/sig]

 

[WP]

Jen,

Not able to Add new records to form
The reason your form does not allow additions is that it is based on an recordset ( the table joined to the query ) that is not updatable. Invariably recordsets across several tables are not updatable because the selection of fields comes from the different tables ( why else would you have such a recordset ) and the SELECT sql string can't be converted to an UPDATE - you can only update one table with a single UPDATE query.

I hate coding bound forms as this is what happens. I prefer to use unbound forms. They are more work but in the long run give you less headaches.

Now on to inventory systems

There are no rules to what has to be a static table. If the data does not change then it is a static table.

I always put my on-hand qty field in the items / product table.

In its simplest form the inventory model is:

tblProduct
ProdID Unique Product ID
ProdDesc Product Description
SOH Stock on hand
MinStock Reorder Level

tblTransactions
TransType Type of transaction 'A'=Addition, 'I'=Issue
SubType Sub type - 'N'=Normal and S''= Stock Correction
TransNo Transaction Number
Date Date of transaction
ProdDesc Product Description
SOH Stock on hand
MinStock Reorder Level
Comment Comments

By having a subtype for stock corrections you never have to update or delete mistakes - you just add a Stoock correction addition or Issue.

You then have at least the following forms:

Add Product
Update Product
Stock additions
Stock Issues
List Stock Items
List Stock Transactions
View stock transaction

hope this helps,



[sig]<p>Bill Paton<br><a href=mailto:william.paton@ubsw.com>william.paton@ubsw.com</a><br><a href=

http://www.neptune400.co.uk/>http://www.neptune400.co.uk/</a><br>

Check out my website ![/sig]

 

[jendendav]

I'm printing a copy of this so I mull it over. Thank you for confirming my problem with my form.

Clarity issues. You use the term unbound form. I went to my books to look up unbound forms. Did you mean unbound controls in forms?
On hand qty is data that changes. That was why I questioned whether it should go in a table.
Stock additions seems obvious (items added to inventory). What is stock issues? Subtractions from inventory? That would make sense based on the transaction table info.
I suspect this will help enormously. Thank you.

Jen [sig][/sig]

 

[WP]

Jen,

An unbound form is where the RecordSource property of the form is blank and all data controls (text boxes, combo boxes, etc.) have a blank control source. All fields are populated using code when the form opens and all table updates are actioned using code too.

They make use of the DAO model to retrieve and update data.

As I said they are much more work ... but are worth learning.


[sig]<p>Bill Paton<br><a href=mailto:william.paton@ubsw.com>william.paton@ubsw.com</a><br><a href=

http://www.neptune400.co.uk/>http://www.neptune400.co.uk/</a><br>

Check out my website ![/sig]

 

[WP]

I have given you bum info ! Sorry.

The SOH should only be on the Product table. The transaction table holds the transaction quantity.

Table layouts corrected

tblProduct
ProdID Unique Product ID
ProdDesc Product Description
SOH Stock on hand
MinStock Reorder Level

tblTransactions
TransType Type of transaction 'A'=Addition, 'I'=Issue
SubType Sub type - 'N'=Normal and S''= Stock Correction
TransNo Transaction Number
Date Date of transaction
Qty Transaction quantity
Comment Comments





Bill Paton
william.paton@ubsw.com

http://www.neptune400.co.uk/

Check out my website !

 

[jendendav]

Thanks Bill. This makes more sense to me and should make my forms much simpler.