Subreports to print Horizontal instead of Vertical 2

[natesin]

I have a query that that lists some items. I understand how to make a subreport in a form to print the items in a column.
Is there a way to make the items print in a row, horizontal seperated by commas?
This would help me free up some space in the attempt to make one page report.
Any help greatly appreciated!!

TIA,
Nathan

 

[ghubbell]

Hi Nathan,
In order to print a subreport as desired you'll have to run your data through a function first.

Here's a function I just made in the Northwinds sample database:

Function BlendTheData() As String
On error goto Err1
Dim SQL As String, Rs As Recordset, Db As Database
Set Db = CurrentDb()
SQL = "SELECT Customers.CompanyName FROM Customers"
Set Rs = Db.OpenRecordset(SQL, dbOpenSnapshot)
BlendTheData = Rs!CompanyName
Rs.MoveNext
Do Until Rs.EOF
BlendTheData = BlendTheData & ", " & Rs!CompanyName
Rs.MoveNext
Loop

Exit1:
Exit Function

Err1:
MsgBox Err.Number & " " & Err.Description,vbInformation,"An error has occured..."
Resume Exit1
End Function

You can place it in a new module and in this case I'm using the "CompanyName" from the table "Customers". You'll have to change these entries to match your table and field names.

Now on a subreport add a textbox. It's "Can Grow" and "Can Shrink" properties must be yes.

In it's "Control Source" property type in:

=blendthedata()

and run the report. This should list every "CustomerName" from the table "Customers" seperated by a comma and one space.

Make the adjustments you require to obtain the right info from the right table and you'll be on your way! Gord
ghubbell@total.net

 

[sera]

Ghubbel,
Do you think I can get a copy of that altered sample database! I am trying to do this also and I have something wrong with what I am doing. I would just like to look at what you did.
Thank you
Sera

 

[ghubbell]

Hi Sera, Still (Ugggh) at work, but when I get home in a couple of hours it will be on its way to you. Gord
ghubbell@total.net

 

[ghubbell]

Oh, sorry... forgot to ask: 2000 or 97 If it's 2000 we might be able to fix it right here! Gord
ghubbell@total.net

 

[sera]

You know I think I spoke to fast. I have it working sort-of. It took me a minute to figure out which module you were talking about...it is of course the module behind the subreport. I am working with Access 97. I have the report printing horizontally, but it is giving me all of the values in the table and is not sorting them by the primary key. Does this make sense?
Sera

 

[ghubbell]

Well Sera you can place it in any module you'd like and it should work. I think when I made it I had it sitting in the "Utility Functions" module....

Sure it makes sense. The example above is very basic. If you would like to, make a new query based on the customers table. Sort the query by whatever you choose. Take a look at it in Datasheet view and if you are happy, switch to "SQL" view. Copy the query, then paste it in to your code. You will probably have to straighten it out in to one nice long "sentence" and when you do be careful of the spacing. But it will work!! Gord
ghubbell@total.net

 

[sera]

I did that, I'll play with it more! I created a module from the module tab on the database window and then I tried to call the function from the Control Source property, and when I ran the report it kept asking for blendthedata, like it was looking for criteria. I typed in =blendthedata() just like you said above. I am very interested in using the modules like you described above, it seems like I would have to tell the report what module to look in to find the function. Is this the way it works?
Sera

 

[ghubbell]

Hi Sera, you're typing in to the control source property of the text box on the report correct? =blendthedata()
I did this on a blank new report so maybe there's something I have forgotten. Perhaps you would try that and see if it goes: a new blank report with a text box in the detail section, with a control source: =blendthedata()
I have to travel in about 10 minutes otherwise I'll pick up back here in an hour or so.
Gord
ghubbell@total.net

 

[sera]

Yep I did what you said, created a blank report and then added an unbound textbox. Then I defined its control source to be: =blendthedata(). The function blendthedata() is defined in a module called blendthedata. I am assuming the name of the module has nothing to do with the function call. I will be leaving for the day also, so I'll check back tomorrow. If you think I am hopeless, then will you e-mail me the database and I will figure out what I am doing wrong? Thank you for your help.
Sera

 

[ghubbell]

No one is hopeless Sera...a little sleepy perhaps, but never hopeless!
A copy is on its way to you now. Enjoy! Gord
ghubbell@total.net

 

[sera]

Okay, I have this working, but now I want to make it a general function, so I don't have to create multiple copies of it. I am trying to send it two parameters, the SQL statement and the field to reference in the recordset. I don't know what I am doing wrong but I do know where. Here is the code

Function blendthedata(SelectString As String, FieldName As String) As String

On Error GoTo Err1
Dim SQL As String, Rs As Recordset, Db As DATABASE
Set Db = CurrentDb()
SQL = SelectString

MsgBox SQL
MsgBox FieldName
Set Rs = Db.OpenRecordset(SQL, dbOpenSnapshot)
blendthedata = Rs! FieldName
Rs.MoveNext
Do Until Rs.EOF
blendthedata = blendthedata & ", " & Rs! FieldName
Rs.MoveNext
Loop
Exit1:
Exit Function
Err1:
MsgBox Err.Number & " " & Err.Description, vbInformation, "An error has..."
Resume Exit1
End Function

I am pretty sure that I am not using the right type for FieldName, because if I put the name of the field in the place where Fieldname is it works beautifully, but not as beautifully as I would like it to. Do you know what I can do to make this work?
Sera

 

[ghubbell]

Hi Sera, Well...you don't really have a recordset here. The point of this function initially was to loop through a bunch of records and return them in a line. It looks here that you would like to loop through a bunch of field names and return them in a line? If you please, explain what you would like as a result, from where, and to where... Gord
ghubbell@total.net

 

[sera]

Maybe Fieldname is a bad name for the variable. I do want to loop through a bunch of records and return them in a line. An example SelectString for this function is:
"SELECT temp_for_impact.impact_agents FROM temp_for_impact"
The corresponding "FieldName" would be :
"impact_agents"
the records for impact_agents come from a table that only has one field(impact_agents). I would like the function to display all of the records in a comma delimited line. If I replace "FieldName" with impact_agents the code works. We declared Rs as a recordset and the code falls apart when I replace
Rs!impact_agents
with
Rs! FieldName
This is why I assumed that I had the wrong type for FieldName. I hope this makes sense.
Sera

 

[ghubbell]

Hi Sera,
Sorry for the delay. I see little point (or success!) in adding the field name as a seperate variable when you have defined in in the SQL line. Perhaps someone with another idea will come along:

Function BlendtheData2(strSQL As String) As String
On Error GoTo Err1
Dim Rs As Recordset, Db As Database, fld As Field
Set Db = CurrentDb()
Set Rs = Db.OpenRecordset(strSQL, dbOpenSnapshot)
For Each fld In Rs.Fields
BlendtheData2 = fld.Value
Next
Rs.MoveNext
Do Until Rs.EOF
For Each fld In Rs.Fields
BlendtheData2 = BlendtheData2 & ", " & fld.Value
Next
Rs.MoveNext
Loop
Exit1:
Exit Function

Err1:
MsgBox Err.Number & " " & Err.Description, vbInformation, "An error has occured..."
Resume Exit1
End Function

If you input "SELECT temp_for_impact.impact_agents FROM temp_for_impact"

BlendTheData2 will sort it out for you. Gord
ghubbell@total.net