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.

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.

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?

Thanks

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.

mmcdonal

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