add an item in a Listbox (VBA)

[CP]

Hi: I want to add an item in a Listbox but when I try to execute my message
in the Additem, VBA considers "," and ";" like a enter.
And, here the VBA Code of the combobox after update

List12.AddItem "SELECT " + oRst.Fields("CDRUBR").Value + ", COUNT (*)
FROM " + Kvalenv + "1DBO." + oRst.Fields("CDSTDO").Value + "" +
oRst.Fields("CDINFO").Value + " WHERE " + oRst.Fields("CDRUBR").Value + " =
'old code' GROUP BY " + oRst.Fields("CDRUBR").Value + " ;"

We don't see "," and ";" in the listbox.

 

[missinglinq via AccessMonster.com]

The above problems with "," and ";" aside, AddItem cannot be used in VBA the
same way as it is in, say, Visual Basic 6.

From Access Help:

"AddItem Method

Adds a list item to the specified *commandbar* combo box control. This
method will fail if it's applied to an edit box or a built-in (non-commandbar)
combo box control."

In other words AddItem in VBA only applies to combo boxes that are part of
the commandbar, not to free standing combo boxes. In VBA, for free standing
combo boxes (and list boxes) you have to use RecordSource. To add a new item
(ItemToBeAdded) you'd use something like:

YourListBox.RecordSource = YourListBox.RecordSource & ItToBeAdded

 

[John Nurick]

Hi CP,

Remember that AddItem only works when the RowSourceType is set to "Value
List", and that all it does is take the string you pass it and append it
to or insert it into the existing RowSource property. You seem to be
expecting it to execute a SQL select statement and somehow extract
values from it: that won't happen.

As far as I can tell, there's no documentation on what happens when you
pass AddItem a string containing semicolons or commas.

The actual behaviour seems to be to truncate the string immediately
before the first semicolon, but to ignore commas. Once inserted into the
RowSource, the commas seem to be treated much the same as semicolons (at
least in Access 2003 SP2) but I wouldn't rely on this. Best to add one
item at a time, perhaps.

But if you're trying to use a SELECT query to add an item to the value
list, why not set the RowSourceType to Table/Query and use a query that
returns the values you need for the listbox? That is usually much
simpler than trying to manipulate a value list.

 

[John Nurick]

Recent versions of Access (2002 and later, perhaps?) do have
ListBox.AddItem and Combobox.AddItem, though I've never needed to use
them.