Well I have it writing to the record for the most part using this code..
Private Sub Command11_Click()
Dim dbInventoryv3 As Object
Dim rstInventory As Object
Dim fldEnumerator As Object
Dim fldColumns As Object
Dim Item As String
Dim RSamount As String
Set dbInventoryv3 = CurrentDb
Set rstInventory = dbInventoryv3.OpenRecordset("Inventory")
Set fldColumns = rstInventory.Fields
' Scan the records from beginning to each
While Not rstInventory.EOF
' Check the current column
For Each fldEnumerator In rstInventory.Fields
' If the column is named Name
If fldEnumerator.Name = "Name" Then
' If the name of the current record is the same as what is selected in the Combobox
Item = Me.RestockItm.Value
RSamount = Me.RestockAmt.Value
If fldEnumerator.Value = Item Then
' then change its value
rstInventory("CurrentStock").Value = RSamount
' Move to the next record and continue the same approach
This does update the Table called Inventory, however it also renames the name of the item a couple rows down in the next category to the name of the item being updated..
For instance (I changed the names of the items just to figure out what is going on.. they are just candy bars and soda, like cokes and pepsi)
If I update C1, C2, C3 or C4 it will change D1's name to which ever one I updated, but just the name.. the data stays the same in "D1" The data will correctly change in the one I selected, which is good.
If I update D2, D3 it will also change D1's name to which ever one I updated. Again it will just change the name of D1 to the one I selected, the correct one will update.
If I update D1, the odd record, when I update it, everything seems right, but when I back out of the Restock form, it gives me a write conflict, saying that someone else has updateded the record, do I want to Save, Copy to Clip Board or Cancel.
Something in that might be the answer?
So If I chose to update C2 with 50 (it over writes the data right now.. adding to it will come later) the table will look like this (notice D1 changed to C2)
Here is what I have in an old Inventory database to pull stock OUT of inventory. Based on 2 seperate queries and then used actual Code to do the work. Code pulled the Queries both into a Sub and then a FunctionCall was set. See attached. As far as your names go...I simply name things as just one continual stream but use UpperCase for each word such as: OriginalStock or as was suggested us the Underscore to tie it together. UpperCase is just my preference. See if this helps you any. Oh, instead of using the (-), you surely would use (+) to add the inventory into stock.
I can't follow that code easily enough, so back to basics.
Combobox named - RestockItm
1 textbox named - CurrentStock
1 textbox named - RestockAmt
User will select an item using the combobox, then in the textbox named CurrentStock it will retrieve the Current Stock from the Inventory Table by being bound using =[RestockItm].column(1)
That part works.. now.. how do I increase that amount by what is put in the second textbox RestockAmt ?
Ok, that sounds easy.
You have everything in front of you, the ID of the product whose level you want to change (in the combo box), the current stock level and the amount to adjust the current level by. So:
Dim rs as Recordset
Set rs = CurrentDB.OpenRecordset ("SELECT ProductID, CurrentStockLevel FROM tblPRODUCT WHERE ProductID = " & Me.RestockItm)
If rs.RecordCount > 0 Then
rs!CurrentStockLevel = Me.CurrentStock + Me.RestockAmt
MsgBox "Could not find product specified by the combo box."
Of course, you will have to change the field names to match what your real names are, such as tblPRODUCT, ProductID and CurrentStockLevel.