msg box is truncating (!?)

[markjkubicki]

i've got this simple code behind a command button, in debug.print I get complete data, but when I assign that data to a msgbox... it truncates to the first record only
Does anyone see something that I'm not seeing ?

(with thanks in advance,m.)

Sub ShowUserRosterMultipleUsers()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i, j As Long
strtext = ""
Set cn = CurrentProject.Connection

Dim dbs As Database, tbl As TableDef, fld
Set dbs = CurrentDb

Set tbl = dbs.CreateTableDef("Users")
Set fld = tbl.CreateField("User", dbText)

' The user roster is exposed as a provider-specific schema rowset
' in the Jet 4.0 OLE DB provider.  You have to use a GUID to
' reference the schema, as provider-specific schemas are not
' listed in ADO's type library for schema rowsets

Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
, "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

'Output the list of all users in the current database.

'Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, _
"", rs.Fields(2).Name, rs.Fields(3).Name

While Not rs.EOF
    Debug.Print rs.Fields(0), rs.Fields(1), _
    rs.Fields(2), rs.Fields(3)
    strtext = Trim(strtext) _
            & ", " & rs.Fields(0)
    rs.MoveNext
    
Wend
strtext = IIf(Len(Nz(strtext, "")) = 0, "no other users connected", strtext)
Response = MsgBox(strtext, vbOKOnly + vbInformation, "Current Connections to MDb")
End Sub

[June7]

Cannot replicate issue and don't see anything wrong with code. However, I have never used OpenSchema. It's not even showing in intellisense tip. I would use OpenRecordset.

Could simplify the MsgBox code and don't need the Response variable:

MsgBox strtext, vbInformation, "Current Connections to MDB"

[davegri]

Does anyone see something that I'm not seeing ?

Looks like strtext is concatenating rs.fields(0) only, over and over.

It also appears you are not using option explicit, as 'response' is not dimmed but is not causing an error.

[June7]

@davegri, code is concatentating Field(0) of multiple records because of rs.MoveNext. This works perfect for me. There is a comma at beginning of the displayed string but the code does work. I have to use OpenRecordset instead of OpenSchema.

Variable strtext is also not declared.

[davegri]

@davegri, code is concatentating Field(0) of multiple records because of rs.MoveNext.

Yes, I understood that. The OP's complaint was that the debug.print was different from the message box, showing only one record. The debug.print is showing 4 fields, the message box just one, which is quite different.
Perhaps OP can provide more complete description of the problem...

[June7]

Right, should "truncates to the first record only" have stated "truncates to the first field only"? Should the MsgBox string include all 4 fields or just Fields(0)?