How to input multiple selections to table from form via List box?

[rlw63109]

I'm trying to create a user friendly form to input data into a table. I've hit a wall with one of my controls. I'd like to make multiple selections (simple or extended) and have them populated in the respective column of my table. Preferably separated by a comma or semicolon. Is this possible and if so, how?

Example:


Customer ABC purchased X,Y, and Z products. In my form, there is a List Box that contains all available products A-Z. I want my table to display all products purchased by Customer ABC in a single field with each item separated by a comma or semicolon. Is this even possible or is a text box my only option?

I do not have a history of programming so please excuse my ignorance and explain it was you would to a 5-year old.

[June7]

Saving multiple items to a single field is bad design. What you described is accomplished one of two ways:

1. multi-value field established in table design

2. saving comma separated values string to a normal text field

The latter requires VBA code.

[Micron]

Another way would be a main form/subform design. Main form has customer data, subform has products they ordered.

[June7]

What Micron suggests does not produce comma separated values in a single field. It produces a record for each product selected from a combobox or single-select listbox. This is better db design. This is a many-to-many relationship and requires 3 tables at a minimum:

tblOrders

tblProducts

tblOrderDetails

A tblCustomers could be useful as well, especially if you have repeat customers and want to collect info like name, address, phone.

[Micron]

Yeah, I guess I was ignoring the whole csv idea. Doing csv in one field, once you have "apple,orange,banana,grape" in a record, good luck trying to find out how many oranges a customer ordered (or anything at all about oranges). Not impossible but I cannot think of a reason to impose such difficulties.

[xps35]

Using one field, multivalue or comma separate, is a bad idea. You also need to be able to store the quantity and current price per product. A orderline table is the only right solution.

[rlw63109]

First, and this goes for all commenters, thanks for your time and feedback.

Second, in this particular case, "apple, orange, banana, grape" is precisely what I'm looking for. Qty and price are irrelevant with respect to my overall goal. My intent for this field is simply to provide a short description of what was included on a given project, but I want to limit and control the input values via list box. I'll appreciate any guidance that can be offered.

[pbaldy]

Post 7 was moderated, not sure why. I'm posting to trigger email notifications.

[June7]

Post 2 already advised on methods. Which do you prefer?

VBA for looping multi-select listbox is common topic. Here is one example https://stackoverflow.com/questions/...ccess-list-box

[Micron]

irrelevant with respect to my overall goal.

As long as you understand the ramifications of how difficult it will be to get specific data out, then IMO go for it.
My suggestion is to research how to ' ms access loop over listbox selected items and concatenate ' or ' ms access loop over selected items and add to comma separated string ' or similar. Not simple for a beginner, methinks. Here's one source

https://learn.microsoft.com/en-us/of...ction-list-box

When you see the code for that you might understand why it's not feasible to guide you as one would a 5 year old as you stated.

[moke123]

I use a lot of listboxes so I use a public function to iterate through the listboxes.

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

you can simply call it with

X = getLBX(me.myListBoxName)

You can use the optional arguments to change which column to return, what delimiters to use or what separator to use.