Different response from table or Recordset

  • Thread starter Thread starter Sheldon Slade
  • Start date Start date
S

Sheldon Slade

I need a snippet of code to convert a list of names (from
a table or SELECT statement) to a single comma-separated
string (for display in a text box). Here's what I have:


Public Function StringFromList(TableName, FieldName As
String) As String
Dim OngoingString As String, GetNames As Object
Set GetNames = CurrentDb.OpenRecordset(TableName)
GetNames.MoveFirst
OngoingString = GetNames(FieldName)
For xMarker = 1 To GetNames.RecordCount - 1
GetNames.MoveNext
If GetNames.EOF = True Then GoTo GotEmAll
OngoingString = OngoingString & ", " & GetNames
(FieldName)
Next
GotEmAll:
GetNames.Close
StringFromList = OngoingString
End Function


When I use a table name as the argument, it works as it
should. But if I use a query or SQL statement, it
invariably returns only one record's information.
Queries in the rest of the DB seem to be fine, but
whenever I create a recordset from one in VB I get a
RecordCount of 1. Anyone see my mistake?

TIA
Sheldon
 
Sorry, know it's bad form to reply to one's own post, but
I thought I'd save someone the trouble of tracking this
down.

Seems the problem was in the RecordCount; I hadn't
realized a RecordCount will only show the number of
records accessed in the current recordset. So, I could
have fixed the problem by using a MoveLast before finding
the number of records, or replacing the For...Next loop
with a

Do Until GetNames.EOF = True

loop. Thanks anyway, and I hope nobody wasted time
trying to find the solution for me.

Sheldon
 
Back
Top