Consulting

Results 1 to 9 of 9

Thread: Loop through Recordsetclone set focus to each record

  1. #1
    VBAX Newbie
    Joined
    Jan 2022
    Posts
    5
    Location

    Loop through Recordsetclone set focus to each record

    I am new to this and requiring some help, I have setup an inventory management system and have a purchase order form with a continuous subform inside a tab for order lines which is working for the most part, when I receive stock I have a field InOutQty that I enter the amount received and then a combobox where I can select Received. I have VBA code which will run on event AfterUpdate of Received Status and add the stock to my Quantity on hand in the Inventory Table which works fine, one record at a time. My goal is to be able to set all received records to received and then press a command button which runs through all the records and updates inventory, then I want to disable that button so it can't be entered twice, I haven't tackled that bit yet until I get the loop working right. The code I have been playing with loops though a recordsetclone and runs my update to inventory I have checked that it loops but it just updates the same line which was last selected as many times as the recordcount, I think I need to maybe setfocus on each record as it loops through, but open to any suggestions, possibly throw out my code and start again with a different method. My attempt at code attached. I have a few message boxes to tell me what was going on and I can see the records count through but the InOutQty just keeps returning the value of last selected record.

    Private Sub StockUpdate_Click()
    
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rst As DAO.Recordset2
    Set db = CurrentDb
    Set rs = Me.RecordsetClone
    
    
    
    
        rs.MoveLast
        rs.MoveFirst
    
    
    Dim idx As Integer
    For idx = 1 To rs.RecordCount
    Me.Item.SetFocus
    
    
    MsgBox "recordcount"
    
    
    Set rst = db.OpenRecordset("select  Quantity_On_Hand from Inventory where Item= '" & Me.Item & "';")
    
    
    MsgBox "setrst" & idx
    
    
    MsgBox "InOutQty" & Me.InOutQty
    
    
    
    
    '" & val & "';"
    
    
    
    
    
    
     With rst
     .Edit
     If Me.LineStatus.Value = "Received" Then
     
     !Quantity_On_Hand = !Quantity_On_Hand + Nz(Me.InOutQty, 0)
     
     End If
     
     If Me.LineStatus.Value = "Returned" Then
     
     !Quantity_On_Hand = !Quantity_On_Hand - Nz(Me.InOutQty, 0)
     
     
     End If
     
      
     .Update
     End With
    
    
    Next idx
    
    
    
    
    End Sub

  2. #2
    you can make the process simple by just creating a Total Query and does
    not require you to maintain any Inventory table.

    select Item, Sum(Switch([LineStatus]="Received", [InOutQty], [LineStatus]="Returned", -[InOutQty], True, [InOutQty])) As Quantity_On_Hand
    From yourTable
    Group By Item

  3. #3
    VBAX Newbie
    Joined
    Jan 2022
    Posts
    5
    Location
    Thanks arnelgp for your input, I had thought about a query but wasn't quite sure how to go about it. I am a little confused though, when you say it doesn't require maintaining inventory table, but then at end of query I am assuming this goes to Quantity_on_Hand in table, can you explain that a little better, sorry for my ignorance I am on a crash course here but learning more each day.

  4. #4
    VBAX Newbie
    Joined
    Jan 2022
    Posts
    5
    Location
    I will play around with the query method as another option but with some trial and error I have managed to get VBA Code working, now for my next project I need to disable button once submitted. Code so far

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rst As DAO.Recordset2
    Set db = CurrentDb
    Set rs = Me.RecordsetClone
    'On Error GoTo edit_error
    
    
    
    
    
    
        rs.MoveLast
        rs.MoveFirst
    
    
    Dim idx As Integer
    For idx = 1 To rs.RecordCount
    
    
    Forms!frmReceiveInventory![frmReceiveInventoryDetails].SetFocus
    DoCmd.GoToRecord , , acGoTo, idx
    
    
    Set rst = db.OpenRecordset("select  Quantity_On_Hand from Inventory where Item= '" & Me.Item & "';")
    
    
    [Item].SetFocus
    
    
    MsgBox "setrs" & idx
    MsgBox "InOutQty" & Me.InOutQty
    
    
     With rst
     .Edit
     If Me.LineStatus.Value = "Received" Then
     
     !Quantity_On_Hand = !Quantity_On_Hand + Nz(Me.InOutQty, 0)
     
     End If
     
     If Me.LineStatus.Value = "Returned" Then
     
     !Quantity_On_Hand = !Quantity_On_Hand - Nz(Me.InOutQty, 0)
     
     
     End If
     
      
     .Update
     End With

  5. #5
    VBAX Newbie
    Joined
    Jan 2022
    Posts
    5
    Location
    Next idx was cut off the bottom of that,

  6. #6
    Quote Originally Posted by Dangas70 View Post
    Thanks arnelgp for your input, I had thought about a query but wasn't quite sure how to go about it. I am a little confused though, when you say it doesn't require maintaining inventory table, but then at end of query I am assuming this goes to Quantity_on_Hand in table, can you explain that a little better, sorry for my ignorance I am on a crash course here but learning more each day.
    it doesn't go (update the any table) to Inventory table.
    Quantity_On_Hand on the query is a Calculated Column.

    see attached. see Table1, then open Query1.
    view Query1 in design view.
    Attached Files Attached Files

  7. #7
    VBAX Newbie
    Joined
    Jan 2022
    Posts
    5
    Location
    Thanks, I thought that may be the case as I tried a query where it would send it to the table but found it wanted a calculated column, I didn't know if there was a workaround for that. Problem is I need to store the value in the inventory table for stock reports, calculating low stock etc.

  8. #8
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Do you still need help with this?

  9. #9
    One rule of thumb is never store a calculated value as it data changes in a database. What you you require is a reporting technique to generate a report of what stock is currently on hand. I can help if you want that.

Posting Permissions

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