'ms access 2013 vba join or concat values from multiple values list box

I have one form FORM1 with one field named FIELD1 which is listbox with many values and it is configured as Multi-select = extended. The data from this form I want to be inserted via ODBC to MS SQL server database and table.

If the table is in MS ACCESS I how to configure it to be with multi-values field.

But in MS sql server I don't know.

I have an idea; When the user select values from this list box, and press button for save record, the values to be joined or concatenate with separator; for example or - (doesn't matter) and joined text to be written in the SQL server field.

Example:

FIELD1 values:
             Val1
             Val2
             Val3
             Val4

The user selecting Val1, Val3, Val4 onClick event in VBA something like this

SQLSTRING = [forms]![FORM1].[FIELD1].value.join(';')

and as result in the MS SQL TABLE to be written Val1;Val3;Val4

after that

insert into MSSQLTABLE set MSSQLFIELD = SQLSTRING

How to configure the form to save SQLSTRING vs FIELD1?

sql-servervbams-accessunity3d


Solution 1:[1]

Three separate fields might even be better than all in one.

However if you really must: code must loop through the listbox and concatenate.
Review: Allen Browne - listbox

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 June7

Related Questions