By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,779 Members | 1,291 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,779 IT Pros & Developers. It's quick & easy.

Moving data from a list box to a table

P: 3
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
Mar 12 '08 #1
Share this Question
Share on Google+
4 Replies


JustJim
Expert 100+
P: 407
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
Mar 20 '08 #2

JustJim
Expert 100+
P: 407
Me again,

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

Jim
Mar 20 '08 #3

ADezii
Expert 5K+
P: 8,636
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:
Expand|Select|Wrap|Line Numbers
  1. Dim frm As Form
  2. Dim ctl As Control
  3. Dim intCounter As Counter
  4. Dim dbSOWGen As DAO.Database
  5. Dim rstNewSOW As DAO.Recordset
  6.  
  7. Set frm = Forms!("<your Form Name here>")
  8. Set ctl = frm![List6]
  9.  
  10. Set dbSOWGen = CurrentDb
  11. Set rstNewSOW = dbSOWGen.OpenRecordset("TABLE SOW")
  12.  
  13. If ctl.ItemsSelected.Count > 0 Then
  14.   For intCounter = 0 To ctl.ItemsSelected.Count - 1
  15.     rstNewSOW.AddNew
  16.       rstNewSOW("Description") = ctl.Column(0, intCounter)
  17.       rstNewSOW("PPU") = ctl.Column(1, intCounter)
  18.       rstNewSOW("Hours") = ctl.Column(2, intCounter)
  19.       rstNewSOW("Type") = "SOW"
  20.       rstNewSOW("Task Number") = Me![Task Number]
  21.     rstNewSOW.Update
  22.   Next intCounter
  23. End If
  24.  
  25. rstNewSOW.Close
  26. Set rstNewSOW = Nothing
  27.  
  28. Forms("FORM_PriceBooks").[TABLE SOW subform1].Requery
  29.  
  30. DoCmd.Close
Mar 20 '08 #4

P: 3
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
Apr 7 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.