Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old October 9th, 2008, 10:48 PM
Posts: n/a
Default Populate a list box from comma separated values

I'm trying to populate a list box from a single field in a record and also update entries in the single field from the list box.

This is simular to Outlooks Contacts Category function where if you select a category or categories from the list box to add to a contact it save all the category name in a single field separated by a comma. But the list box shows each category name as a separate line item.

Adding a category appends the field by adding the category name with a comma.

Removing a category removes the category name from the single field.

I hope this makes sense. I thought by using Outlook as example would give everyone a working model of what I am trying to do.

I'm new to VBA and Access but I have come a long way in the last six months.

The reason I need this to work like above is because my application is managing Outlook contacts via Access and if I can get the category names in a single field in a record separated by a comma, then I'm golden.

Thanks in advance.

Old October 9th, 2008, 11:58 PM
Friend of Wrox
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts

Bad DB design. You should *NEVER* use a delimited list as the value for a field in a relational database. You should instead create another table and create a many-to-one relationship from that new table to your existing one.

If you stored the values as separate records in this related table, then you could fairly easily create the comma delimited list (e.g., in VBA) for use when communicating with Outlook.

If you think you *must* use the delimited list, then creating an array therefrom is pretty easy:
     catArray = Split( categoriesWithCommas, "," )
and if you have an array, you can go the other way via
     categoriesWithCommas = Join( catArray, "," )
but as to how you manipulate that array into a list box and back out again, can't help you.
Old October 18th, 2008, 09:54 PM
Posts: n/a

I agree, I would never do this in a normal database application.

I do have a joined table for the categories to manage the listbox. However, I need to take these values from the joined categories table and when the table is updated or when the listbox senses a modification I need to take all the entries in the joined table and make a string of all the values separated by commas and then dump this string into a single field to be read by Outlook when the update engine cycles the database for changes.

Would I use an array to read the values from the joined table and from the array create a comma delimited string and then dump the string into a field?

Can you point me in the direct for the command(s) needed to dump the string into a field?


Old October 20th, 2008, 08:37 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

Did you get what you need? Basically you would build the List box using the Split() function that the old dude suggests. Do you know how to build the list from the array?

Then to take the values back into the field, you would check the field for any existing values. If yes, then add a comma and load in the one or many selections from the list box, and if no, just load in the one or many selections without the leading comma.

Let me know if you need the code. If so, I am assuming a bound form, post the column names for the incoming and outgoing data.


Look it up at: http://wrox.books24x7.com

Similar Threads
Thread Thread Starter Forum Replies Last Post
Comma Separated values aldwinenriquez SQL Server 2005 3 June 1st, 2007 08:52 PM
comma separated list MunishBhatia SQL Server 2000 11 March 21st, 2007 03:56 PM
selecting elements from comma separated list rjonk XSLT 4 September 29th, 2006 08:46 AM
comma separated list of attributes rjonk XSLT 3 September 27th, 2006 11:52 AM
Select from Comma Separated Values sasidhar79 SQL Server 2000 2 March 28th, 2005 06:58 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.