Access message box help

[wiley2111]

Hello, I am trying to get the code for when I open up a form that it will display all of the people in the database at the time in a message box that appears on the form... The code will not display in a nice way and im not sure how to fix it to make it appear nicely. The code works but it keeps repeating the txt and looks really bad. I don't know if an array would work but is there any way to clean up how it looks? Below is my code: Thanks for the help.


Private Sub Form_Open(Cancel As Integer)
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i, j As Long
Set cn = CurrentProject.Connection

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

Me.TxtMsg.Value = "The following people have DB open: " & Me.TxtMsg.Value & rs.Fields(0)
rs.MoveNext

Wend


End Sub





This is the result I get if there is more than one person in the Database...

The following people have DB open: The following people have DB open: "NAME1" "NAME2"



I want it to look something like this:

The following people have DB open:
"NAME1"
"NAME2"

[SamT]

Me.TxtMsg.Value = "The following people have DB open: " & vbCrLf _
& Me.TxtMsg.Value & rs.Fields(0) & vbCrLf
nb: " _", (Space+Underscore,) is the Line Continuation code.

[wiley2111]

Thank you! I appreciate the help but the line: "The following people have DB open:" still appears twice when a second person enters the DB... this is how it looks:

The following people have DB open:
The following people have DB open:
"Name1"
"Name2"

Is there any way that it doesn't repeat the first line twice and only appear once no matter how many people are in the DB?

[SamT]

Move the opening line out of the loop.

Dim Nms As String
While Not rs.EOF
Nms = vbCrLf & Me.TxtMsg.Value & rs.Fields(0)
rs.MoveNext
Wend

If Nms = "" Then Nms = "None"
Me.TxtMsg.Value = "The following people have DB open: " & Nms