Controlling Do Loop in recordset

[Ben McCall]

I have a database in access 97.

I have one table – “report” which contains two fields – “formatted answer” and “answer” in each record. I want to have a report that concatenates these fields sequentially on a line:

[formatted answer] & “ “ & [answer] for each record with a space between each concatenated record.

I am using the following code in the “On Format” event of the detail section of the report:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim db As Database
Dim rs As Recordset
Dim str As String
Set db = CurrentDb
Set rs = db.OpenRecordset("report", dbOpenTable)

rs.MoveFirst
Do Until rs.eof

str = str & rs![formatted answer] & " " & rs![answer] & ". "
rs.MoveNext

[Text2] = Trim(str) 'Text2 is unbound text box in detail.

Loop

rs.Close
Set rs = Nothing

End Sub

This gives me exactly what I want except that it goes through the loop for the number of records I have in the table. If I have four records it give me four concatenated lines on the report.

Can anyone tell me how to have this loop only one time no many how many record the table contains?

Thanks!

Ben McCall



[This message has been edited by Ben McCall (edited 07-07-2001).]

 

[raskew]

Find it difficult to envision what you're trying to accomplish. Could you show us an example of what you hope to see on your report.

Your looping problem is particularly puzzling. You've created a looping mechanism designing to loop through the recordset, but you don't want it to?!

A little more explanation would really be helpful.

 

[Ben McCall]

I want th report to show this:

Record1[formatted answer] & Record1[answer] & “. “ & Record2[formatted answer] & Record2[answer] & “. “ & Record3[formatted answer] & Record3[answer] & “. “& Record4[formatted answer] & Record4[answer] & “. “

I get this but I get four copies of it. I would like to have only one. I am not an expert in VBA. Maybe what I am getting is what I am supposed to be getting. If so is there any way to get just the one copy?

Ben McCall

 

[Fornatian]

Would it not be easier just to include the fields in the reports recordsource,and put the formula for the string into an unbound text box's control source?

 

[joeyreyma]

try to remove the do...loop in your code because this cycles you to every record in your recordset. since you only want one record, it is safe to remove these lines.

and by the way, try to put this code before rs.movefirst:

if rs.recordcount <> 0 then
rs.movefirst


however, on your last reply, you have a slight different output than what i understand from your explanation.

if you want your report to display
Record1[formatted answer] & Record1[answer] & “. “ & Record2[formatted answer] & Record2[answer] & “. “ & Record3[formatted answer] & Record3[answer] & “. “& Record4[formatted answer] & Record4[answer] & “. “

try this one:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim db As Database
Dim rs As Recordset
Dim str As String
Set db = CurrentDb
Set rs = db.OpenRecordset("report", dbOpenTable)

If rs.RecordCount <> 0 Then
rs.MoveFirst
Do Until rs.eof

str = str & rs![formatted answer] & " " & rs![answer] & ". "

rs.MoveNext

Loop
End If

[Text2] = Trim(str) 'Text2 is unbound text box in detail.

rs.Close
Set rs = Nothing

End Sub

'remove the Me("Text2") box from the do loop

[This message has been edited by joeyreyma (edited 07-07-2001).]

 

[Ben McCall]

The last code above still gives me four strings instead of the one that I would like to have.

Ben McCall

 

[joeyreyma]

try to insert this code before rs.movefirst:

str=vbNullString

 

[Ben McCall]

That still gives me multiple stings - as many as there are records.

Ben McCall

 

[Fornatian]

try using this function:

Function MyStringIs(intRecNo As Integer) As String

Dim dbs As Database
Dim rst As Recordset
Set dbs = CurrentDb
'temp string holder
Dim strHolder As String
'open recordset
Set rst = dbs.OpenRecordset("Table1")
'populate
rst.MoveFirst
'keep looping until end of file
Do While Not rst.EOF
'conditionally add info to temp string
If rst.Fields("PrimaryRecordIdentified") = intRecNo Then
strHolder = strHolder & rst.Fields("Data")& etc.. fields & ", "
End If
'next rec
rst.MoveNext
Loop
'assign string and knock last comma and space off
MyStringIs = Left(strHolder, Len(strHolder) - 2)
End Function

I use this function in the column header(ColumnTitle: MyStringIs(PrimaryKeyID)for queries and then base reports on the queries output. If you intend to use it make sure the query doesn't output duplicate records using the unique values property.

I know this doesn;t give a full answer but I've tried this method and it does cycle records building a string as it goes.
My apologies if I've broken the thread.

Ian

 

[joeyreyma]

as i understand it, the string format you want is already done but only you get four copies of it or the same with the number of records. why don't you remove the recordsource of your report because in every record in the source will cycle you in the detail section of the report.

 

[Ben McCall]

Thanks! That solved my problem.

Ben McCall