Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2009
    Posts
    7

    Question Unanswered: Update Record selected by Combobox

    Hi,

    I have a form with 1 ComboBox, 2 Text Boxs, and a cmd Button.

    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 ?

    Thanks in advance!!!!

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    VBA Recordsets would be my first choice
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Aug 2009
    Posts
    7
    I am pretty new at this.. most of my experience is in Excel VBA.

    Maybe my field names are what are causing my problems..

    Category
    Name
    Price
    Original Stock
    Current Stock

    does Original Stock need to be one word or use a _ to connect the words or does it not matter..

    I am searching online about Recordsets..

    Any additional help?

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Because you used a space in a field name, you MUST enclose it in [square brackets].
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Aug 2009
    Posts
    7
    Well I have it writing to the record for the most part using this code..

    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.Edit
                        rstInventory("CurrentStock").Value = RSamount
                        rstInventory.Update
                    End If
                End If
            Next
            ' Move to the next record and continue the same approach
            rstInventory.MoveNext
        Wend
    
    End Sub
    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..

    Category Names Price OriginalStock CurrentStock
    Candy C1 .50 10 10
    Candy C2 .50 20 20
    Candy C3 .50 30 30
    Candy C4 .50 40 40
    Drinks D1 .50 10 10
    Drinks D2 .50 20 20
    Drinks D3 .50 30 30

    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)

    Category Names Price OriginalStock CurrentStock
    Candy C1 .50 10 10
    Candy C2 .50 20 50
    Candy C3 .50 30 30
    Candy C4 .50 40 40
    Drinks C2 .50 10 10
    Drinks D2 .50 20 20
    Drinks D3 .50 30 30

    Any ideas based on my code.. Why this happens?

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Bear with me.... I am playing "catchup" after a week off. It might be a while before I can commit time to this as a result :/
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Aug 2009
    Posts
    7
    Thanks.. I will keep plugging away at it too.. thanks in advance for the help

  8. #8
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by joederr
    Hi,
    Thanks in advance!!!!
    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.

    Have a nice one,
    BUD
    Attached Thumbnails Attached Thumbnails joederrStocking1.bmp   joederrStockingUpdate1.bmp  
    Attached Files Attached Files

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    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:

    Code:
    Dim rs as Recordset
    Set rs = CurrentDB.OpenRecordset ("SELECT ProductID, CurrentStockLevel FROM tblPRODUCT WHERE ProductID = " & Me.RestockItm)
    If rs.RecordCount > 0 Then
      rs.MoveFirst
      rs.Edit
      rs!CurrentStockLevel = Me.CurrentStock + Me.RestockAmt
      rs.Update
    Else
      MsgBox "Could not find product specified by the combo box."
    End If
    Of course, you will have to change the field names to match what your real names are, such as tblPRODUCT, ProductID and CurrentStockLevel.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •