Listbox load question

[kfinpgh]

I use Access 2003 with VBA and managed to create a dual list box feature that allows me to pick from left and copy to right. I set it up to copy the items instead of moving them and I can do multiple selections. It works great for lists up to several hundred, but is sluggish at 1100 or so names.

I feed the results to a table so I can use it in a query to limit the matching records in the selection. That was easier than creating a humongous SQL statement.

It works pretty well until I have a list with embedded commas in the text. The additem function splits the entry at the comma. Last name, first name turns into last name on one row and first name on the next.

Does any one have a way to tell additem to ignore embedded commas?

I would also be happy to share this routine should anyone want a copy.

Thanks.

[pnish]

The additem function splits the entry at the comma.

It shouldn't, and in fact I just tried it and it didn't. Perhaps there is some other (non visible) data embedded in the string which is causing your problem, eg a linefeed or carriage return etc

[kfinpgh]

I am using VBA, which may behave differently than VB6. If there were other characters in the field, Access would split the contents of the field in other operations.

[Hack]

Access VBA question moved to Office Development

[kfinpgh]

I used findandreplace() to change the comma to " and ". I reverse the process when the listbox contents are used in a multi-item selection table. That turns the " and " back into the comma.

Works OK for a small list, but 11 names can take a while.