Need to write all values or append to list

[Sammy145]

What i am doing or need to do is get a list of names from a table and add it to a notes email
i only want the list in one email so i tried to loop around the table and get the values BUT it only sends one name (IN THE VARIABLE COUNTERS)

Can someone SHOW me how to do this ??

Dim Rs
Dim COUNTERS
''Connection object is here
Set Rs = CreateObject("ADODB.Recordset&quot
sSQL2 = "Select USER_GROUP_NM from USER_GROUP_DELETED"
Set Rs = ObjConn.Execute(sSQL2)

While Not Rs.EOF Or Rs.bof
COUNTERS = Rs.fields("USER_GROUP_NM&quot
Rs.movenext
Wend

strMessage = "There is an error in the DTS package " & Counters


Thanks in advance

 

[bridamol]

change your code to the following

While Not Rs.EOF Or Rs.bof
COUNTERS = Counters & Rs.fields("USER_GROUP_NM&quot
Rs.movenext
Wend

 

[Sammy145]

hi there im a bit confused
the result i want to see in strMessage is

strMessage = "There is an error in the DTS package check " A,B
A comma betweeen each Counters or space

so when i get 1 email
it will say in the message
"There is an error in the DTS package check" A,B

Thanks in advance

 

[Sammy145]

anyone??

 

[CajunCenturion]

your statement:

COUNTERS = Rs.fields("USER_GROUP_NM&quot

is only assigning the current value from the recordset into the variable counters. In order to append the names to the list, you'll need to append them together, keeping the existing value of COUNTERS.

Inside the loop, the assignment statement should read as follows:

COUNTERS = COUNTERS & Trim(Rs.fields("USER_GROUP_NM&quot) & ","

Once you exit the loop, you need to remove the comma at the end

COUNTERS = Left(COUNTERS, Len(COUNTERS) - 1)

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein