[RESOLVED] MS Access VBA addItem List view

[FishGuy]

I am getting an error on this line of code, hope someone can help.
As you can see all I want to do is loop through the dataset and add column 1 to a list view for each row.

Me.lstEmpStates.RowSourceType = ValueList
While Not rs.BOF And Not rs.EOF
[U]Me.lstEmpStates.Additem rs.DataMember(rs.RecordCount, 1)[/U]
rs.MoveNext

[FishGuy]

Me.lstEmpStates.Additem
This is the offending piece of code I think. Error Method or data member not found. I have set the source type to value list so cant see the problem?
Anyone?

[salvelinus]

I think to use the .AddItem on a listbox it has to be a CommandBar listbox, which I've never used. The listbox control in the toolbox doesn't have an .Additem method that I know of. You can set the contents to a data source, like a table or query, or enter a value list.

[FishGuy]

OK yes add item is on 2003 and im on 2000.
I have a workaround by using a value list
i.e Build a comma delimited string then
Me.lstEmpStates.RowSource = strStates

My trouble now is building the string

While Not rs.BOF And Not rs.EOF

strStates = strStates & ";" & rs.DataMember(0, 1)
rs.MoveNext

Wend

As you can see I am just wanting to get column 1 into the string as I loop through the record set but the syntax is wrong.

hlp pls

[Ecniv]

what is rs?
what is datamember?

if rs is a recordset then use rs("<fieldname>") - will work except (possibly) for null values.

Also there is a character limit on the list box for values, but if you are not going near that limit you should be ok.

[FishGuy]

Thanks Ecniv
http://msdn.microsoft.com/library/de...html/ino2k.asp

Also keep in mind that there's a limit on just how long a control's RowSource property can be, and that limit is 2,048 characters

I think that should be enough

[salvelinus]

Another option is to write the values you want to a temp table and use that as a rowsource. That way you could sort, select, etc.