Multiple Select List box

[chilipep417]

Hello, Ok, here's the scenario. I want to have a multiple select list box in my form. And the results of these will be included in a table. Let's call this Table A.

Now I found out that when you create a multiple select listbox, the value of the bound field in Table A will always be null.

So what I did was wrote a code that would loop through all the selected items using the .ItemSelected

Question is, now that I was able to extract the values that I selected in the list box, how do I dynamically add these results to a temporary table which I will include in a query together with Table A. This query then will be the data source of my underlying form.

Please advise. Thanks.

Any source code you may share will be very much appreciated.

 

[TrojanRabbit]

Probably TableDef and QueryDef objects. Create the two objects, and use a field object to add to them. (fld = tbl.CreateField(...).) Access Help will explain how to use these. Not sure I can give a good explanation.

 

[Guest_imported]

Hi,
You could create the temp table before hand and then use th following code in the click event of a button to enter the data in the temp table.


Private Sub enterDataIntoTemp_Click()
dim db as DAO.Database
dim rs as DAO.Recordset
dim listSelection as Variant
Set db=currentdb
Set rs=db.openrecordset("Temptable&quot
for each listSelection in Me.List1.ItemsSelected
rs.AddNew
rs.Fields("field1&quot=Me.List1.Column(0,listSelection)
rs.Fields("field2&quot=Me.List2.Column(1,listSelection)
' Code for the rest of the fields in the temp table
rs.Update
Next listSelection
rs.close
db.close
End Sub

The above code will fill your temp table with data from the list box. Once you are done with whatever you are doing, be sure to delete all the entries in the temp table before the next user selects items in the list box.

Let me know what happens.

 

[chilipep417]

Hello..thanks so much to everyone who had responded to my question. Thanks especially to tlvChennai for your code. Your code worked perfectly..

Another question though..
Now that I was able to "dump" all the selected items in a table.. how can I retrieve it in such a way that if I go back to my form that contains the list box, I would see all the selected items? Apparently, once I leave the form (the databas is composed of "interconnected" forms similar to a wizard with Next and Back buttons), the items I highlighted in the list box doesnt register.

Thanks so much again to everyone. Youve all been such a great help.

 

[Guest_imported]

Hi,
You could try the idea given below:

In the click event of the button that takes the user to the previous form ( with the list box ) add the following code:

Private Sub showPrevious_Click()
[Forms]![Previous Form].List1.RowSourceType="Table\Query"
[Forms]![Previous Form].RowSource="Select * from TempTable"
docmd.openform "Previous Form",acNormal

End Sub

In the Load event of the form ( if you close the form after the user has moved to this form ) or Activate event (if you minimize the form after the user has moved ), add the following:

Priavte Sub Form_Load()
Me.List1.Requery
End Sub

But make sure that once the user clicks on the button to move to the next form in the wizard or clicks on the finish button the RowSource property is set to the table or query that will be used when you open the wizard for the next user.