CODEWITHSUNDEEP
vbams-access
icalderon
icalderon

Reputation: 63

How to store record information from a ListBox to a Table?

I am using the following code that is linked to a button cmdAdd. Using the 'On Click' event, the ListBox selection information is copied to a table KitBuild.

Private Sub cmdAdd_Click()

  Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  Dim ctl           As Control
  Dim varItem       As Variant

  On Error GoTo ErrorHandler

  Set db = CurrentDb()
  Set rs = db.OpenRecordset("KitBuild", dbOpenDynaset, dbAppendOnly)

  'make sure a selection has been made
  If Me.lstResults.ItemsSelected.Count = 0 Then
    MsgBox "At least 1 part must be selected."
    Exit Sub
  End If

  'add selected value(s) to table
  Set ctl = Me.lstResults
  For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!ItemNo = ctl.ItemData(varItem)
    rs.Update
  Next varItem
    
ExitHandler:
  Set rs = Nothing
  Set db = Nothing
  Exit Sub

ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select

End Sub

Shown below is the table KitBuild where selections are saved. Currently, it only transfers over 1 piece of information from the selection which is No from qryParts into the column called ItemNo (ID #8). This works fine but I also need to transfer over the Description and Unit Cost of records from qryParts to KitBuild.

enter image description here

I tried changing the following to rs!Description and rs!UnitCost but the No value just shifts over to those columns (ID #9, 10) rather than providing the Description and Cost.

  'add selected value(s) to table
  Set ctl = Me.lstResults
  For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs!ItemNo = ctl.ItemData(varItem)
    rs.Update
  Next varItem

This is an example of the information I would like copied from qryParts to KitBuild. enter image description here

How can I modify the code to include No, Description and Cost?

Upvotes: 0

Views: 45

Answers (1)

Vlado
Vlado

Reputation: 888

I assume that your ListBox has data in the order as on your last picture. Use this:

      'add selected value(s) to table
      Set ctl = Me.lstResults
      For Each varItem In ctl.ItemsSelected
        rs.AddNew
        rs!ItemNo = ctl.Column(0)
        rs!Description = ctl.Column(1)
        rs!UnitCost = ctl.Column(2)
        rs.Update
      Next varItem

Upvotes: 1

Related Questions