Inventory DataBase; Stock on hand

You need some more fields in your table as well as a separate table for vendors:

Partstable                                  Vendors table

PartsID (autonumber PK)             VendorID(autonumber PK)
PartsDescription (txt)                   VendorName(Text)
PartsNumber (txt)                        VendorStreet(text)
Quantity (Number)                       VendorCity(Text)
MinimumQty(number                    VendorState(Text)
VendorID(Long)                            VendorZip(Text)
                                                   VendorPhone(Text)

This utililizes the relational aspect of access/sql by creating a relationship between the tables using VendorID to relate the tables.
Now we need more info on whether you are using a Jet DB or SQL DB from Access2k3.  I can provide you code in either DAO or ADO to accomplish what you ask but is a basic update query to update your quantity.

Add Quantity:
"UPDATE Partstable SET Partstable.Quantity = Partstable.Quantity +" & [yourquantitytextbox]

Remove from stock:
"UPDATE Partstable SET Partstable.Quantity = Partstable.Quantity -" & [yourquantitytextbox]

How these queries are executed depends on whether you are using DAO or ADO/type of database.
Just a few more details please...

Sorry:
"but HERE is a basic update query to update your quantity"

I am not sure what I am using (DAO or ADO?). I am in the early stages of creating this database I used some tips from a Access 2003 book. I have some knowledge of access but your question confused me a little bit.  I like your suggestion and would like to know more how to trigger the process

Mel  

Melsink,

From your original question I gather you need a simple solution that fits with what you already have.

You will need a least some minimal knowledge of VBA to do this.  But essentially, it can be done in a few simple steps:

#1  Create a new button the form in question.

#2  Use either the button wizard or the button's properties to set the OnClick event to [Event Procedure].  This will create a VBA procedure that you can add code to.  If you used the button wizard, you may even want to delete most of the code it created for that button.

#3  You will need to know the name of the button.  (Look in Properties)

#4  Create a statement in the OnClick event procedure in the form of:  Me.ButtonName = Me.ButtonName + 1

Example:

I created a new button, changed the Name property to AddOneToStock and created an OnClick event.

The OnClick event looks like this:

Me.AddOneToStock = Me.AddOneToStock + 1


That may be all you need, and I hope it helps.

Enjoy,
-- Miqro

Sorry, I meant to refer to the Name of the text box you are updating.  If the text box Name is CurrentStock then the example should be:

Me.CurrentStock = Me.CurrentStock + 1

You might want to create two command buttons, one called Add Product and one called Remove Product.  Inventory control can be quite complex but I'll give you a few pointers.  Typically products are added to stock by a "Receive Product" form.  Items are removed from stock via an Invoice or PO.  When you add a product to an invoice, you would remove that quantity from stock.  When you delete an item from an invoice you should put the item back into stock.  I created something called a 'master stock record' that is modified when stock is added or removed.

DAO(Data Access Objects) and ADO(Active Data Objects) are the code interface you use to execute querys and manipulate data within tables, reports and queries.  I'm assuming you aren't talking to a SQL server.  The following is an example using DAO:

In your form create a button and put this code in it's On Click event:

Private Sub YourAddButton_Click()
Dim strSQL , MyDatabase as Database

Set MyDataBase = CurrentDb()
strSQL = "UPDATE Partstable SET Partstable.Quantity = Partstable.Quantity + " & [yourquantitytextbox]

CurrentDb.execute (strSQL )

MyDatabase.close
set MyDatabase = nothing

end sub

To add to the complexity, to make the above code work you'd need to select a stock item first and change the stock item's quantity with the query.  The following assumes you created a combobox named YourProductComboBox and the rowsource for the combobox would be:

[YourProductComboBox].Rowsource = "Select fldProductID, fldProductionDescription FROM PartsTable ORDER BY fldProductionDescription"
Set the combobox's column count property to 2
Set the Column Widths property to "0 in; 2 in"

Then execute the query like above using the following statement:

strSQL = "UPDATE Partstable SET Partstable.Quantity = Partstable.Quantity + " & [yourquantitytextbox] _
              & " WHERE Partstable.fldProduct_ID=" & [YourProductComboBox]

Argi,

Thanks I will give it try I don't have time to work on it right now but 1st thing in the moring I will give it shot

Mel  

I try it bu I must be doing something wrong; I created a form, placed two command btns on it used the code your suggested and created cbo to select the product I wanted to change it's qty. may be if you can just breakdown you suggestion in a few more steps

Mel

Let's try this:
Set up your cbo the same way only this time we're going to see what happens when you select a product from it:

[YourProductComboBox].Rowsource = "Select fldProductID, fldProductionDescription FROM PartsTable ORDER BY fldProductionDescription"
Set the combobox's column count property to 2
Set the Column Widths property to "0 in; 2 in"

In the afterupdate event of your 'Add' button place this code:

Private Sub YourAddButton_Click()
Dim strSQL , MyDatabase as Database

msgbox [YourProductComboBox]

end sub

Don't forget to change the name of 'YourAddButton' to whatever you named your Add Button and the name of your cbo to whatever you named your cbo.  Then let me know what MsgBox displays.

Better yet, send me your code....

[Combo33].rowsource  SELECT tblparts.PartID, tblparts.PartNumber FROM tblparts ORDER BY [PartNumber];

Private Sub SubstractQty_Click()
Dim strSQL, MyDataBase As Database

Set MyDataBase = CurrentDb()
strSQL = "update tblparts Set tblparts.quantity=tblparts.quantity - " & [quantity]

CurrentDb.Execute (strSQL)
Set MyDataBase = Nothing
End Sub


Private Sub AddQty_Click()
Dim strSQL, MyDataBase As Database

Set MyDataBase = CurrentDb()
strSQL = "update tblparts Set tblparts.quantity=tblparts.quantity + " & [quantity]

CurrentDb.Execute (strSQL)
Set MyDataBase = Nothing
End Sub


Thanks

Mel

Yeah you are right but it is not working

Mel

hi there
 i am faced with the same problem did you manage to get a solution , if you did could you post the solution please

lambarts

There is a multitude of solutions to this question.  It really depends on your individual needs.  What exactly are you trying to do?  What have  you already done?  This will help me  understand your needs so I can give you a hand.  I currently have an inventory control app running but it involves quite a bit.  There are several parts that come into play.

1. A Master stock record that contains the info about each item and it's current in-stock quantity.  Note that I also have a field that defines a minimum stock requirement so you can notify a user when it's time to re-order with a stock report/query

2. A Receive items form that adds a quantity of an item to the quantity field of the master stock record

3. A PO or Invoice form that removes items from stock when a PO/Invoice item is added AND adds an item to stock when a PO item is deleted.  I use combos for the user to select a stock item and also a quantity field. Then an "Add" button that adds the item(s) to the PO and simultaneously removes that quantity from the master stock record.  The "Delete Item" buttom removes the selected item from the PO and in turn adds it back to the master stock record quantity