Listbox not populating properly

[duluter]

I am trying to populate a listbox with an ADO recordset that contains data with internal commas. Normally, I just double-double quote these and everything is fine:

Do Until rs.EOF
Me.MyList.AddItem ("""" & rs!MyField & """")
rs.MoveNext
Loop

But this time, the listbox is not getting populated with the entire recordset. It just seems to be arbitrarily cutting off part way through the dataset. I have checked that the ADO query itself is returning the correct number of records, and I have inspected the data to see if there is anything unusual going on at the point where the failure occurs, but I see nothing special about the records that aren't being added properly.

If I try to eliminate the internal commas using the Replace function prior to adding the item to the listbox, I get a few more records successfully added, but still not all of them. I also tried wrapping the data in Chr(34), to no avail.


Some sample data in the field I'm trying to get into my listbox:
ABCD, NW1/4
DWER E 1/4, N 1/4
TREWE
WERT, NW1/4 & NE1/4
etc.

It will get through 573 records that look just like these and then stop, even though the next 110 records have the same characters and general pattern.


Duluter

 

[Galaxiom]

MyField is a string variable so I would have expected it to work with no quotes at all. Concatenating quad double quotes should result in the List Item including double quotes.

Presumably the comma is somehow being taken as the argument separator even though it is inside a string variable.
http://msdn.microsoft.com/en-us/library/aa141572(office.10).aspx

This should throw argument type errors. The listbox population should not stop without an error. Could the error be quietly getting swallowed with an On Error Resume Next.

 

[Galaxiom]

I got curious and tested it. You are quite right about the quad double quotes.
Othewise the comma is taken as a column separator.

I expect you have hit the limit of 2048 characters in the RowSource.

 

[LPurvis]

Are we talking about a version of Access prior to 2002 here?
If not then assigning the recordset directly to the list control would be the most efficient and productive option (you don't have a 32K character limit then), just the 64K list limit - which is crazy long anyway.

Me.MyList.RowSourceType = "Table/Query"
Set Me.MyList.Recordset = rs

You'll need to size your columns to match the recordset returned columns of course.

If you're not using 2002 or newer then you can always fall upon a CallBack function to fill the list. (To circumvent the length limit and delimiter issues).
And the list limit is indeed 2K characters in such versions.

FWIW 573 records is probably past the level most developers would choose to offer in a list control. It's not excessive - but if you are using Access 2000 or older then that would sound right for you list length limit.
Allen Browne has the standard "Limit As You Type" example. There's a different take on it (based on a recordset) on my examples page (linked to in my sig).

Cheers.

 

[duluter]

Leigh, I think you hit the nail on the head. I changed the row source type to Table/Query and set the listbox's recordset to the ADO recordset and the listbox populated just fine. What a relief!

I did not know about the 32K character limit. Pretty sneaky--there was no indication from Access that anything was amiss.

Thank you.

Duluter

 

[duluter]

Two more related questions.

1. If I populate the listbox by setting the listbox's recordset property to the ADO recordset, does that create a persistent connection to the data as long as the form is open? I'd rather not have a persistent data connection.

2. I am populating a multi-column listbox. My ADO recordset has five fields in this order: A, B, C, D, E. When I set the listbox's recordset to the ADO recordset, the listbox does not order the fields the same as they are ordered in the ADO recordset. Instead, they are ordered: C, A, D, E, B. Why is the field order not the same?


Duluter

 

[LPurvis]

1. Is your form not bound then? A persistent data connection is common in Jet database development. Are you operating in an, otherwise, unbound methodology?

2. This is actually potentially related to 1, but I'll await your response on that.
It's an unusual result for certain - but my first question would be:
Is your ADO recordset using a Client side cursor location? (That's not an absolute requirement for list control binding - but IMO you're asking for trouble without it).

Cheers.

 

[duluter]

Leigh:

1. My form is not bound. Not sure if this is relevant, but I am working in a front end/back end paradigm--both .mdb's.

2. I get the same disordering if I use a keyset or static cursor.


Duluter

 

[LPurvis]

So your form is entirely unbound?
You're using the listbox purely for entry into an unbound control?

To better answer both 1 and 2, can you provide your full code?

Cheers.

 

[duluter]

Yes, it's entirely unbound, and the listbox control is also unbound. I have two comboboxes that the user can make some selections in, and then some data gets displayed for them in the listbox. Here's the routine that populates the 5-column listbox. The routine runs when the comboboxes' change events fire.

Private Sub LoadSnapshot()

Dim rs As ADODB.RecordSet

Set rs = New ADODB.RecordSet

'Grab the custom view work status info.
rs.Open "SELECT WorkYear, ClientID, ClientName, LocationDescription, nz(Max(CompletedDate),'NONE') AS MaxOfCompletedDate " & _
            "FROM [A BUNCH OF JOINED TABLES] " & _
            "GROUP BY [some grouping rule] " & _
            "HAVING WorkYear=" & Me.cboSnapshotWorkYear.Value & " And ServiceID=" & Me.cboCustomService.Value & " " & _
            "ORDER BY Max(CompletedDate), ClientName, LocationDescription", CurrentProject.Connection, adOpenStatic, adLockReadOnly

Set Me.lstCustom.RecordSet = rs

rs.Close
Set rs = Nothing

End Sub

The recordset should be returning the fields in the order: WorkYear, ClientID, ClientName, LocationDescription, MaxOfCompletedDate.

But when I look at the populated listbox, it's being populated in the order: ClientName, WorkYear, LocationDescription, MaxOfCompletedDate, ClientID.


Duluter

 

[LPurvis]

OK then.
On both counts - try:

Private Sub LoadSnapshot()

Dim rs As ADODB.RecordSet

Set rs = New ADODB.RecordSet
rs.CursorLocation = adUseClient

'Grab the custom view work status info.
rs.Open "SELECT WorkYear, ClientID, ClientName, LocationDescription, nz(Max(CompletedDate),'NONE') AS MaxOfCompletedDate " & _
            "FROM [A BUNCH OF JOINED TABLES] " & _
            "GROUP BY [some grouping rule] " & _
            "HAVING WorkYear=" & Me.cboSnapshotWorkYear.Value & " And ServiceID=" & Me.cboCustomService.Value & " " & _
            "ORDER BY Max(CompletedDate), ClientName, LocationDescription", CurrentProject.Connection, adOpenStatic, adLockReadOnly

Set rs.ActiveConnection = Nothing
Set Me.lstCustom.RecordSet = rs

rs.Close
Set rs = Nothing

End Sub

Cheers.

 

[LPurvis]

Also, I presume that
"GROUP BY [some grouping rule] "
is actually
"GROUP BY WorkYear, ClientID, ClientName, LocationDescription "

Actually, FWIW I'd have

rs.Open "SELECT WorkYear, ClientID, ClientName, LocationDescription, nz(Max(CompletedDate),'NONE') AS MaxOfCompletedDate " & _
            "FROM [A BUNCH OF JOINED TABLES] " & _
            "WHERE WorkYear=" & Me.cboSnapshotWorkYear.Value & " And ServiceID=" & Me.cboCustomService.Value & " " & _
            "GROUP BY WorkYear, ClientID, ClientName, LocationDescription " & _
            "ORDER BY 5, 3, 4", CurrentProject.Connection, adOpenStatic, adLockReadOnly

Cheers.

 

[duluter]

Leigh:

That did it, though I don't understand why explicitly setting the cursor location would reorder the recordset fields. If you feel like explaining that, I'm all ears. Otherwise, thanks very much for the help.

Duluter

 

[LPurvis]

Well, it would be probably that not setting the location re-ordered the fields.
I don't know what order they're stored in your actual database table - but forcing the recordset to a client side cursor means you're not fetching any data from the server after the initial hit. Because ADO is responsible for providing the cursor service then all it has to work from is what you have loaded - in the order you've loaded it. Even if you wanted optimisation on any static columns - you'd supply those to ADO explicitly, the server (and any underlying ordinal positions) aren't requested.

If your table columns aren't ordered as either (even beneath the seams) then I don't know what would be the cause. (But then it would be hard to truly know the internal ordering anyway).

Cheers.