Combo Box - Multiple Selection 1

[achiola]

I am trying to create a combo box that allows the user to select specific items. The source of these items are from a table. Once these items are selected and the form is submitted I would liked them to be stored in a different table. Is this possible? How do I create a multiple selection combo box?

Nino

 

[LarryDeLaruelle]

Nino:

I may be wrong, but I don't think there is a multi-select property for Combo boxes. You will need to use a list box.

To make a list box multi-select, go to the Other tab on the property sheet and set the Multi-Select property to either Simple or Extended (simple allows the user to click on each item desired; extended allows the use of the shift and control keys to make multiple selections).

You will probably need to write some code if you intend to create distinct records in another table based on the user's selections.

Hope this gets you started.
Larry De Laruelle
larry1de@yahoo.com

 

[achiola]

Larry,

That is definetly the start I need, however, more importantly I need help writing that code that would send data to a table based on the user's selection. Can anyone help?

Nino

 

[LarryDeLaruelle]

Nino:

The approach you take depends on how you have the table that is to receive the new records set up.

For purposes of this example, I created a simple table with two fields: RecID (autonumber) and NewItem (Text).

I then created an unbound form with two controls:
lstItems
(list box where I typed in three values;
you can base yours on a table or query) with the
multi-select property set to Simple
cmdAddRecords
(command button).

In the On_Click event of the cmdAddRecords, I put this code:

Private Sub cmdAddRecords_Click()

Dim intCount As Integer 'count of items selected
Dim intLoop As Integer 'counter for loop
Dim dbTemp As Database
Dim rsTemp As Recordset

Set dbTemp = CurrentDb
Set rsTemp = dbTemp.OpenRecordset("tblListAdd&quot

intCount = lstItems.ItemsSelected.Count
'capture the number of selected items

For intLoop = 0 To intCount - 1
'uses 0 to start since ItemData is zero based
With rsTemp
.AddNew
.Fields(1) = lstItems.ItemData(intLoop)
.Update
End With
'Adds a new record and assigns the value to the
'second field (field counts are zero based)
'and updates the recordset
Next intLoop
'Processes the next choice if more than one selected.
rsTemp.Close
'closes the temporary recordset
End Sub

Give this a try.

You will need to modify where necessary for table and field names and, in the AddNew section, include all fields that need to be updated as well as their appropriate values.

I would suggest testing it on a test table/form before doing it for 'real'.

Good luck.

Larry De Laruelle
larry1de@yahoo.com