Adding records from multiselect listbox

NYIntensity
Lounger
Posts: 47
Joined: 26 Jan 2010, 01:05

Adding records from multiselect listbox

Post by NYIntensity »

Good morning :)

I have a form (frmLinks) that has a combo box (cboBuild) and a multiselect list box (lstTwo). The list box is actually populated from another list box on the form, that users can move items between. I want to be able to select an item from cboBuild, and click a button, and have a record created for every item in lstTwo (not just items selected, but everything in it) in tblLinks. If there are ten items in lstTwo, then I want ten records created in tblLinks; the records would have two fields, the ID obtained from cboBuild (which works properly), and the ID from the items in lstTwo (which doesn't work). I realize that the code I have below uses the ItemsSelected property; I'd like to get away from that, so the user doesn't have to transfer items from lstOne to lstTwo and then select everything in lstTwo if possible. Right now, when I click the button, I get records created in tblLinks, but there is no ID from the items in lstTwo. The bound column in both cboBuild and lstTwo is column 1, which is the ID.

Code: Select all

Dim DB As DAO.Database
Dim RST As DAO.Recordset

Set DB = CurrentDb
Set RST = DB.OpenRecordset("tblLinks")

For Each Item In lstTwo.ItemsSelected
RST.AddNew
RST!imageid = cboBuild
RST!updateid = lstTwo
RST.Update
Next

Set RST = Nothing
Set DB = Nothing

MsgBox "Updates linked successfully"

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Adding records from multiselect listbox

Post by HansV »

If you want to add ALL items from lstTwo, try

Code: Select all

Dim DB As DAO.Database
Dim RST As DAO.Recordset
Dim i As Long

Set DB = CurrentDb
Set RST = DB.OpenRecordset("tblLinks")

For i = 0 To lstTwo.ListCount - 1
  RST.AddNew
  RST!imageid = cboBuild
  RST!updateid = lstTwo.ItemData(i)
  RST.Update
Next i

RST.Close
Set RST = Nothing
Set DB = Nothing

MsgBox "Updates linked successfully"
Best wishes,
Hans

NYIntensity
Lounger
Posts: 47
Joined: 26 Jan 2010, 01:05

Re: Adding records from multiselect listbox

Post by NYIntensity »

Thanks for that Hans! It works; however, it's also adding a record with the word 'ID'. Screenshot attached w/ issue
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Adding records from multiselect listbox

Post by HansV »

Does your list box have Column Heads set to Yes? If so, change

For i = 0 To lstTwo.ListCount - 1

to

For i = 1 To lstTwo.ListCount - 1
Best wishes,
Hans

NYIntensity
Lounger
Posts: 47
Joined: 26 Jan 2010, 01:05

Re: Adding records from multiselect listbox

Post by NYIntensity »

That was it...thanks!