Moving data from a list box to a table

[rsmolik]

Hi,

I have a list box that allows you to select multiple values and then transfers the selected values to a table.

I have almost cracked this but I am having a small problem;

My code is as follows;

Private Sub Command18_Click()
Dim item
Dim dbSOWGen As DAO.Database
Dim rstNewSOW As DAO.Recordset

Set dbSOWGen = CurrentDb
Set rstNewSOW = dbSOWGen.OpenRecordset("TABLE SOW")

For Each item In List6.ItemsSelected
rstNewSOW.AddNew
rstNewSOW("Description").Value = List6.ItemData(item)
rstNewSOW("PPU").Value = List6.Column(1, List6.ListIndex)
rstNewSOW("Hours").Value = List6.Column(2, List6.ListIndex)
rstNewSOW("Type").Value = "SOW"
rstNewSOW("Task Number").Value = [Task Number]
rstNewSOW.Update
Next item

Forms("FORM_PriceBooks").[TABLE SOW subform1].Requery

DoCmd.Close
End Sub

It is successfully transfering the Descriptions selected values but it is only transferring the last selected values for PPU and Hours. Can someone please help me modify the above code so it transfers the correct values for PPU and Hours as it runs through the FOR loop???

Any help will be really appreciated

RichO

[JustJim]

Hi,

I have a list box that allows you to select multiple values and then transfers the selected values to a table.

I have almost cracked this but I am having a small problem;

My code is as follows;

Private Sub Command18_Click()
Dim item
Dim dbSOWGen As DAO.Database
Dim rstNewSOW As DAO.Recordset

Set dbSOWGen = CurrentDb
Set rstNewSOW = dbSOWGen.OpenRecordset("TABLE SOW")

For Each item In List6.ItemsSelected
rstNewSOW.AddNew
rstNewSOW("Description").Value = List6.ItemData(item)
rstNewSOW("PPU").Value = List6.Column(1, List6.ListIndex)
rstNewSOW("Hours").Value = List6.Column(2, List6.ListIndex)
rstNewSOW("Type").Value = "SOW"
rstNewSOW("Task Number").Value = [Task Number]
rstNewSOW.Update
Next item

Forms("FORM_PriceBooks").[TABLE SOW subform1].Requery

DoCmd.Close
End Sub

It is successfully transfering the Descriptions selected values but it is only transferring the last selected values for PPU and Hours. Can someone please help me modify the above code so it transfers the correct values for PPU and Hours as it runs through the FOR loop???

Any help will be really appreciated

RichO

Hi,
Completely off the top of my head, I think it's in the DIM of Item. I think it's got to be a special data type, not just Variant. I'll look into it a bit later and get back to you.

You are very close though.

Jim

[JustJim]

Me again,

No, it seems that Variant is fine. While I work this through, have a look at this

Jim

[ADezii]

Hi,

I have a list box that allows you to select multiple values and then transfers the selected values to a table.

I have almost cracked this but I am having a small problem;

My code is as follows;

Private Sub Command18_Click()
Dim item
Dim dbSOWGen As DAO.Database
Dim rstNewSOW As DAO.Recordset

Set dbSOWGen = CurrentDb
Set rstNewSOW = dbSOWGen.OpenRecordset("TABLE SOW")

For Each item In List6.ItemsSelected
rstNewSOW.AddNew
rstNewSOW("Description").Value = List6.ItemData(item)
rstNewSOW("PPU").Value = List6.Column(1, List6.ListIndex)
rstNewSOW("Hours").Value = List6.Column(2, List6.ListIndex)
rstNewSOW("Type").Value = "SOW"
rstNewSOW("Task Number").Value = [Task Number]
rstNewSOW.Update
Next item

Forms("FORM_PriceBooks").[TABLE SOW subform1].Requery

DoCmd.Close
End Sub

It is successfully transfering the Descriptions selected values but it is only transferring the last selected values for PPU and Hours. Can someone please help me modify the above code so it transfers the correct values for PPU and Hours as it runs through the FOR loop???

Any help will be really appreciated

RichO

The following code segment should work well for you, just be sure to substitute your Form's Name for the place marker in Line# 7:

Dim frm As Form
Dim ctl As Control
Dim intCounter As Counter
Dim dbSOWGen As DAO.Database
Dim rstNewSOW As DAO.Recordset
 
Set frm = Forms!("<your Form Name here>")
Set ctl = frm![List6]
 
Set dbSOWGen = CurrentDb
Set rstNewSOW = dbSOWGen.OpenRecordset("TABLE SOW")
 
If ctl.ItemsSelected.Count > 0 Then
  For intCounter = 0 To ctl.ItemsSelected.Count - 1
    rstNewSOW.AddNew
      rstNewSOW("Description") = ctl.Column(0, intCounter)
      rstNewSOW("PPU") = ctl.Column(1, intCounter)
      rstNewSOW("Hours") = ctl.Column(2, intCounter)
      rstNewSOW("Type") = "SOW"
      rstNewSOW("Task Number") = Me![Task Number]
    rstNewSOW.Update
  Next intCounter
End If
 
rstNewSOW.Close
Set rstNewSOW = Nothing
 
Forms("FORM_PriceBooks").[TABLE SOW subform1].Requery
 
DoCmd.Close

[rsmolik]

Thank you both for your help. The last post is exactly what I was looking for. Once again thanks tonnes I really appreciate your assistance.

Regards,

RichO