Change Listbox Separating Character

[JRodko]

I have a listbox in a form that is doing nearly exactly what I want it to do. When I click into the datasheet style field I can select whatever options I want from a linked lookup table and they are being stored properly in the forms record source. Is there any way to change the separating character from a "," to a ";"?



For example when my list box shows up I can select from the colours, I choose "Red", "Green", "Blue".

When I go to the table where form data is stored that field is displayed as "Red, Green, Blue" which is mostly correct. Is there any way to change this to "Red;Green;Blue"?

This is because I often export as CSV so obviously having a comma in there will ruin those exports.

Thanks!

[June7]

You are using a multi-value field? No, cannot change separator. A multi-value field really just stores pointers to records of a hidden dependent table. Access controls this, you cannot change how Access manages this link.

I NEVER use multi-value field.

Have you tried exporting this field type?

[JRodko]

Hi June7,

It exports fine to .xlsx, CSV exports will be a mess. I don't think the table field is a multi-value field? I can't find anything in the properties sheet that says that it is, just a regular old short text field.

[June7]

If it is multi-value the field will be formatted in table with a combobox (or I suppose a listbox but usually combobox) and AllowMultipleValues will be set to Yes.

If not, then there must be some code that is concatenating selected values into a CSV string and that code can use whatever separator you want.

I just tested a normal listbox on form with AllowMultipleSelect set to Yes but not associated with a multi-value field and it does not allow multiple selection in Datasheet view.

I think you have a multi-value field.

If you want to provide db for analysis, follow instructions at bottom of my post.

[JRodko]

It appears that you are correct. How do you suggest I re-create this without a multi value field but one that concatenates from a set of options?

[moke123]

Im assuming your using a multi-select listbox. I use the function below with multi-select list boxes. One of the arguments sets the seperator character in the string. You would call it with something like

X = getLBX(Me.MyListBoxName,1,";")

Public Function getLBX(lbx As ListBox, Optional intColumn As Variant = 0, Optional Seperator As String = ",", _
                       Optional Delim As Variant = Null) As String


'Iterates thru the multiselect listbox and constructs an array of the selected items
'Arguments:
'Lbx is Listbox Object ie.Me.MyListbox
'intColumn is the column # to be returned
'Seperator is the character seperating items in array returned
'Delim is optional delimiter to be return in array ie. #1/1/2001#,#12/25/2015#


    Dim strList As String
    Dim varSelected As Variant


    'On Error GoTo getLBX_Error


    If lbx.ItemsSelected.Count = 0 Then
        'MsgBox "Nothing selected"
    Else


        For Each varSelected In lbx.ItemsSelected


            If Nz(lbx.Column(intColumn, (varSelected)), "") <> "" Then


                strList = strList & Delim & lbx.Column(intColumn, (varSelected)) & Delim & Seperator


            Else


                strList = strList


            End If


        Next varSelected


        If Nz(strList, "") <> "" Then


            strList = Left$(strList, Len(strList) - 1)  'remove trailing comma


        End If
    End If


    getLBX = strList


    On Error GoTo 0
    Exit Function


getLBX_Error:


    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure getLBX of Module modLBX"


End Function

[JRodko]

Thank you moke123. I think that is a little bit beyond me. I will try to make this work.

The data needs to be stored in 'Header! Probe_Serial' and the listbox is from another table named 'LKT_Probes! strProbes'

I will have to try to implement this. I can create public functions etc. but the syntax of making this work eludes me.

Cheers.

[orange]

Suggest you post a copy of your database (zip format) so readers can "see" what you are dealing with.
We just need enough records to showcase the issue you are having.

[June7]

You won't be able to use a Datasheet form. Use Continuous form.

You will likely have to delete the multi-value field and create a new text field. Then use code suggested by moke to save CSV string into this field.

Is 'Header! Probe_Serial' name of this field?

Okay, driving me nuts - correct spelling is 'separator'

[moke123]

Okay, driving me nuts - correct spelling is 'separator'

No spell check in the vbe and it's not something option explicit catches.
At least now I know how to push your buttons

[JRodko]

I'm going to have to put more time into how I want to solve this. I generally use datasheet forms but for this situation I may need to have a separate continuous form or convert to continuous form.

Thanks for the input. I will respond with further questions or with my solution.

[JRodko]

I am marking this thread as solved for my purposes. I ended up coming to the conclusion that the juice is not worth the squeeze. My total possible number of selections is actually fairly few (less than 10 combinations) so I have manually created them in my lookup table, and I have changed my listbox to a single select combo box.

My solution is a little bit bush league but it also simplifies things and works well enough.

Thanks!