Using commas in "Value List" list boxes

[Guest]

I am trying to add a column that contains a comma to a 'value list' list box.
I have a patient name (Last, first MI) column and a patient ID column (among
others). I have my Row Source Type set to Value List and the number of
columns correct, column widths etc. all correct, but when I try and do this:

lstbox.AddItem "TreatmentId;LastName, FirstName MI;PatientId"

The help documentation shows that the delimeter should be the ; not comma's,
however instead of getting the name all in one column, it is being placed in
two separate columns (because of the comma... it is being seen by Access as a
delimeter, separating columns). I have tried to escape the comma... to get it
to be seen as a literal. Ive tried using chr(44) in place of it... all to no
avail...

is there anyway to use a comma in a 'value list' list box without Access
splitting the string on the comma?

btw: Im using 2007 if it makes any dif...

 

[Peter Hibbs]

Try enclosing the LastName, FirstName part in single quotes.

However, are you trying to do this in Access 2007 as I don't believe
the standard List Box control has an AddItem method (perhaps you are
using Visual Basic 2007).

HTH

Peter Hibbs.

 

[Guest]

I am 'using' 07 in the sense that I am using it to develop my db, but saving
it in 2000's .mdb format. Which brings up a good point: I want to eventually
move my db to 07's .accdb format, so will my lstbox.AddItem method break when
I convert to that format?

Thank you Peter, now I know how to include comma's in value list lstboxes,
it wasn't documented well with the online help from MS.

Best,
Mark

 

[Peter Hibbs]

Mark,

I have done a bit of research and it seems that Access 2007 now allows
the AddItem method on combo and list boxes, see -
http://msdn2.microsoft.com/en-us/library/bb224614.aspx

As I don't have A2007 I don't know if this procedure will work in the
..accdb format. If you are intending to use the database under Access
2000 then I think I would check whether this is going to work first
before going much further with the design, I would think it unlikely
that it would. You can enter a string in the RowSource property which
would then be compatible with earlier versions (although bear in mind
that in A2000 you are limited to a maximum of 2048 characters).

Perhaps someone who has A2007 and A2000 could pick up this thread and
give a bit more information.

Regards.

Peter.