Combo Box Multi Select Saving (1 Viewer)

dynamictiger

Registered User.
Local time
Today, 21:26
Joined
Feb 3, 2002
Messages
270
Hi All

Its been a while...however I dont often get stuck and usually can solve things myself. However this is a mystery to me.

The machine shop has decided they want an Access database and have given me a base form etc they want to use.

On one of the forms to achieve input checking etc I have unbound the form. However this form includes a multi select combo box and I dont see how to save this data in VBA, maybe I am getting a brain freeze around the multi-select and combo box mix. I need to solve this issue to be able to complete this form.

Anyone got any experience with these pesky things?
 
I thought, to use a multi-select combobox, the form or control has to be bound. Is that what you're asking? How to use an unbound form with a multi-select combobox?
 
I am asking how to save the content of a multi select combo box in an unbound form.

Form is unbound.

Combo box is multi select.

One completing a record including this combo box with its multiple selections. I walk the form and save the records to the table.

I would think something like:

.Fields("MultiSelect") = Me.cboMultiSelect

Should work, however it doesn't, throwing an error. I have tested the various columns and cant work out what to save?
 
I am asking how to save the content of a multi select combo box in an unbound form.

I agree with a dBGuy, you can't have a multi select combo-box without a table. In other words you can't have it unbound. If you've managed to create a multi select combobox and it's unbound, then I'd like to know the steps you took, because as far as I'm aware you can only make it a multi select in the table.
 
Somehow I am not making this clear. The multiselect combo is attached to a table. Its saving the data selected that is proving illusive.

Code:
Private Sub cboReference_BeforeUpdate(Cancel As Integer)
Dim i As Integer
Dim value As Variant

MsgBox "BeforeUpdate " & Me.cboReference.ItemsSelected.Count

    For i = 0 To Me.cboReference.ListCount - 1
    If Me.cboReference.Selected(i) = True Then
        value = Me.cboReference.ItemData(i)
                
        MsgBox i & " " & value
        
        MsgBox Me.cboReference.Selected(i)
      
        
    End If
    
    Next i
    
End Sub

Me.cboReference.ItemsSelected.Count returns the correct count.

Value returns ?

Selected returns -1 which I assume is the correct column 0 selected.

I cant save what I cant see.
 
There's an excellent video by "Takeshi K" - "Access2010 MultiValued ComboBox"

Access2010 MultiValued ComboBox

on multi value Fields (combo boxes)

Also at the bottom of my "Nifty Access" web-page on multi value Fields, there are some useful links:- Multi Value Fields Links
 
VBA is not required if combobox is bound to multi-value field. Can't have a multi-select combobox on an unbound form. However, can have multi-select listbox and this would require VBA to save selected items.
 
Somehow I am not making this clear. The multiselect combo is attached to a table. Its saving the data selected that is proving illusive.

Code:
Private Sub cboReference_BeforeUpdate(Cancel As Integer)
Dim i As Integer
Dim value As Variant

MsgBox "BeforeUpdate " & Me.cboReference.ItemsSelected.Count

    For i = 0 To Me.cboReference.ListCount - 1
    If Me.cboReference.Selected(i) = True Then
        value = Me.cboReference.ItemData(i)
               
        MsgBox i & " " & value
       
        MsgBox Me.cboReference.Selected(i)
     
       
    End If
   
    Next i
   
End Sub

Me.cboReference.ItemsSelected.Count returns the correct count.

Value returns ?

Selected returns -1 which I assume is the correct column 0 selected.

I cant save what I cant see.
Hi. Since we cannot see what you're looking at, maybe you can help us understand you better by posting a demo copy of your db.
 
I am asking how to save the content of a multi select combo box in an unbound form.

Form is unbound.

Combo box is multi select.
Hi. I just gave this a try and couldn't duplicate your setup. As soon as I make the form unbound, then the MVF combobox stops working.

If it might help, take a look at this demo to see how you could work with MVF combobox.

 
that is not a "mystery".
make the form bound.
nobody, only you, understand an unbound form with Multiselect combo?
 
Any chance you are using confusing terminology.
There is a multiselect listbox that can be used on an unbound form.
There is a multi value combobox that has to be bound to a MVF
There is really no such thing as a multi select combobox.
By chance you mean multiselect listbox?

However it would be really cool if access made the MVF controls available for standard use.
 
There is really no such thing as a multi select combobox.
Not sure I agree. You can select more than one value in a MV combo if it's bound to a MVF? Or am I not remembering that correctly as I don't use MV fields?
This would be a web sourced image since I wouldn't have any. Shows first 2 values are chosen.
aa1.gif
 
Not sure I agree. You can select more than one value in a MV combo if it's bound to a MVF? Or am I not remembering that correctly as I don't use MV fields?
I think we are saying the same thing. There is no multi select combo in the Access toolbox that you can select for a unbound form or even a bound form. You can only get a Multi Value Combobox with a multi value field. So there is no way to have a multi select combo on an unbound form.
 
I tried to roll a generic "simulated" multivalue listbox. It works OK, but it is hard to make it completely generic. This could work on a unbound form, and with a little control positioning would be a good fake. It still requires some coding by the user to update the tables.
 

Attachments

Hi! Sorry for asking a question on this topic after so much time. MajP's database is perfect for my needs, but if I have a table with many records, it takes a very long time to scroll through the form and then to close it. Do you have any ideas on how to fix this? Thanks!
 
Is it only slow when scrolling and searching? Or does it take a long time to load and unload the control?
 
If it is slow loading and unloading you may be better off with building a temp table instead of using the in memory ado recordset. The version I demoed has to read the data and loop it to build the recordset. If those selections are already in a table bound to a form then loading will be faster. There is an example here

Also there is a way to use an MVF control but requires a "helper" table in order to get the MVF control. See discussion here.

If the ADO example works but only scrolling is tedious, you could customize that and add a filter. Sharepoint has a control like that. You have a multi select listbox and a textbox at the top. Then you can filter the list of choices. However if it is slow loading this would only add to that each time you filter.
 
it takes a long time to unload the control
Can you explain more where you get your choices from and how large that is. I do not really understand what you mean by unloading. The demo creates an ado in memory recordset, reads and loads the data into the recordset, and then binds that to the pop up form. I could envision that it might be slow to load if the data is large. However, I do not know what is slow on "unloading". Are you saying the pop up takes a long time to close? Or a long time to write the selections to the table?
Any chance you could provide a strip down version. There may be a way to stick with the ADO recordset and fix this delay, or you might be better off with one of the other approaches demonstrated in thread 17.
 

Users who are viewing this thread

Back
Top Bottom