How to change report presentation of recordset groups?

[dreew]

Hi All,<br>
<br>
New to this forum, but this is a problem that's been stumping me for some time, and wanted to see if I've been looking too hard at the wrong solution.<br>
<br>
Rather than a lengthy sub-report listing matches to it's parent, I'd like a single control on a report (or sub-report) to contain all matches to the parent control, probably separated by commas.<br>
<br>
ie: No good...<br>
New York<br>
Manhattan<br>
Queens<br>
Brooklyn<br>
<br>
Good:<br>
New York<br>
Manhattan, Queens, Brooklyn<br>
<br>
Any help much appreciated.<br>
<br>
Happy Holidays,<br>
Drew <br>

 

[rochelle]

Write a function that returns &quot;Manhattan, Queens, Brooklyn&quot; called, let's say, Strungout by opening the recordset based on the parent, looping through the recordset and stringing all the values together:<br>
<br>
Private Function StrungOut() as text<br>
dim rst as recordset, dbs as database, temp as string, strSQL as text<br>
<br>
' the following strSQL is only approximate but you get the idea, I'm sure<br>
<br>
strSQL = &quot;Select [YourFieldName] from [YourTableName] where [YourFieldName for Parent in the Table] = '&quot; & [YourControlName for the Parent in the Report] & &quot;'&quot;<br>
<br>
(I'm assuming a string value for Parent here)<br>
<br>
Set dbs = CurrentDb<br>
set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)<br>
rst.movefirst<br>
temp = &quot;&quot;<br>
while not rst.eof<br>
if len(temp)&gt;0 then<br>
temp = temp & &quot; ,&quot;<br>
endif<br>
temp = temp & rst![YourFieldName]<br>
rst.movenext<br>
wend<br>
StrungOut = temp<br>
set rst = nothing<br>
set dbs = nothing<br>
end function<br>
<br>
Then set the controlsource of the textbox in your detail section to be:<br>
=StrungOut()<br>
<br>
Note that strings have a length limit of 256 characters on them.<br>
<br>

 

[dreew]

ty for your reply, Rochelle.<br>
<br>
I tried for a couple hours today, but kept getting a user-defined type error on the &quot;...as text&quot; statements. Tried changing it to type string, but then i got an error on the 'set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)' line about different type comparison...dang programming ain't easy!<br>

 

[Zelandakh]

depending on the version of Access you have, you may be able to get away with variable as string * 255 instead of text.