Retrieve values from a multiselect combobox

[b82726272]

Hi,



With the code below I can retrieve the values that are selected from a multiselect listbox. But this code doesn't work for a combobox. The selected values aren't saved in the variable strOffice. What do I need to change? Thank you for the reaction.

Kind regards,

For Each varItem In Me.List35.ItemsSelected
        strOffice = strOffice & ",'" & Me.List35.ItemData(varItem) & "'"
    Next varItem

[Ruegen]

combo box is column

so me.ComboBoxControl.column(0) 'This one is the first column

If you want it to pull the second column first add the column in the properties of the control so that you have more than one, then choose starting form 0

i.e. if it is the first column (generally the ID field) in the query or row source it will be column 0, second 1, third will be 2

It's a tad confusing but you need to get used to it in programming as things like arrays start at 0 not 1

[b82726272]

Hi Ruegen,

Thank you for your response. The combobox exists of one column with values like option 1, option 2 and option 3. I have made this combobox with a valuelist. The selected values (for example option 2 and option 3) I want to use in the where part of a query. This works fine for a listbox (with the code mentioned above). But it doesn't work for a combobox. It seems that .ItemsSelected doensn't work for comboboxes. Is that possible?

Kind regards,

[Missinglinq]

...But it doesn't work for a combobox. It seems that .ItemsSelected doensn't work for comboboxes...

That is because Comboboxes do not have a Multi-Select Property, only ListBoxes.

I have a custom hack I wrote for simulating this in Comboboxes; let me see if I can find it.

Linq ;0)>

[b82726272]

Hi Linq,

I just read that on another forum It would be great if you can post the hack. Thank you very much.

Kind regards,

[Missinglinq]

I had a note to myself that I could never get this to work assigning the selections directly to a Variable, but rather had to assign it to a Textbox (txtHolding, in the code below) and then, when all selections are made, assigned the Value of txtHolding to the Variable. Set the Visible Property of txtHolding to No, so that it doesn't show to the users and confuse them.

The Variable is declared directly under the Option Statements, at the top of the code module, so that it will stay in Scope (retain the selections) as long as you remain in the Form. You can then use strOffice for whatever you need.

Option Compare Database
Option Explicit

Dim strOffice As String

Then

Private Sub YourComboBox_AfterUpdate()

 If IsNull(Me.txtHolding) Then
   Me.txtHolding = Me.YourComboBox
 Else
   Me.txtHolding = Me.txtHolding & ", " & Me.YourComboBox
 End If
  
 strOffice = Me.txtHolding

 End Sub

Note that the line

Me.txtHolding = Me.txtHolding & ", " & Me.YourComboBox

Places a comma between each selection; you could use another delimiter if you preferred.

Depending on how you're using this, you may have to reset txtHolding to Null, in an appropriate event. You'd have to do this, for instance, if you wanted to make entirely different selections, as in running a second search without first closing the Form.

Linq ;0)>

[b82726272]

Hi Linq,

Thank you for the code. I get the following error when I use a multiselect comobox: "The value you entered isn't valid for this field". The code works fine with a single select combobox.

Kind regards,

[Ruegen]

Hi Linq,

Thank you for the code. I get the following error when I use a multiselect comobox: "The value you entered isn't valid for this field". The code works fine with a single select combobox.

Kind regards,

What are you putting the data into?

If there is a field, it is related to a record, so you can only put one for one in if it is an integer

so if you have an ID field (which is an integer) you are placing one value into one record using the combo box. (then you move to the next record on the form and repeat the process).

update - I see that you are putting values into a string

is the strOffice a string?

[b82726272]

Hi Ruegen,

Yes, I have declared it as a string.

Kind regards,

[Ruegen]

Hi Ruegen,

Yes, I have declared it as a string.

Kind regards,

are you putting strOffice into me.txtHolding (assuming it is a field called [holding]) and is that field in your query a text field?

[b82726272]

Hi Ruegen,

I put the value of Me.txtHolding in the variable strOffice. I use this variable in the query. I think that is has something to do with the multiselect combobox. If I enter one value I get the same error.

Kind regards,

[Missinglinq]

There is no such thing in Access as a 'multi-select' Combobox! Are you, perhaps, talking about a Combobox based on a Multi-Value Field? Where each item in the Combobox has a Checkbox beside it?

Linq ;0)>

[b82726272]

Hi Linq,

Yes, that is what I mean.

Kind regards,

[Missinglinq]

That's a whole different ball of wax, one that most of us stay away from! But here's a link on the subject, including, I believe, how to use MVFs in a Query:

http://office.microsoft.com/en-us/ac...001233722.aspx

Linq ;0)>