Building a String From A Recordset

[maccten2000]

Hello Chaps,

I have a piece of code that loops through a recordset and builds a string. The recordset contains a list of errors on a production line for a particular manufacturer. My code is below.

     Do Until RS1.EOF
                ProdStrMsg = "Item: " & RS1!Code & " - " & RS1.Fields("Item").Value & " Dups: " & RS1.Fields("CountOfItem").Value
                If Len(ProdStrMsg) > 0 Then ProdStrMsg = ProdStrMsg & vbCrLf
            Loop

So it should loop through it and when it finishes ProdStrMsg will have been built up and formatted correctly as below to be passed back as a variable to a different function

Item: 00005 - 001 Dups: 2
Item: 00006 - 001 Dups: 4
Item: 00007 - 001 Dups: 5
Item: 00008 - 001 Dups: 7


Unfortunatly i cant seem to get it to work
Can anyone help me out with my code

Cheers

 

[SkipVought]

I'd do something like

  dim fld as adodb.field

  Do Until RS1.EOF
    
       ProdStrMsg = ProdStrMsg  & "Item: " & RS1!Code & " - "
       ProdStrMsg = ProdStrMsg  & RS1.Fields("Item").Value & vblf    
            
  Loop 
  ProdStrMsg = Left(ProdStrMsg, Len(ProdStrMsg)-1)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jrbarnett]

As a first start, put

RS1.MoveNext

immediately above the "Loop" line to force it to not go into an infinite loop.

Is this the only thing that is wrong? are you getting any other error messages?

John

 

[sarduspater]

Try with:

Do while not(RS1.EOF)
....
Loop

 

[TheAceMan1]

How are ya maccten2000 . . .

Just a guess since you don't reveal what the actual output looks like:

[blue]   Dim Pack As String, txt As String
     
   Do Until Rs1.EOF
      txt = "Item: " & Format(Rs1!Code, "00000") & " - " & _
                       Format(Rs1.Fields("Item"), "000") & " " & _
            "Dups: " & Format(Rs1.Fields("CountOfItem"), "00")
      
      If Pack <> "" Then [green]'all other loops[/green]
         Pack = Pack & vbNewLine & txt
      Else
         Pack = txt [green]'1st loop[/green]
      End If
      
      Rs1.MoveNext
   Loop
   
   Debug.Print Pack [green]'testview![/green][/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Remou]

You might like to consider an ADO recordset and GetString.

'Ref: Microsoft ActiveX Data Objects 2.8 Library

Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection

Set cn = CurrentProject.Connection
rs.Open "SELECT * FROM Table1", cn

srs = rs.GetString

More info:

http://www.w3schools.com/ado/ado_getstring.asp

http://lessthandot.com