A97: Concatenate text fields into one long string

[RonMcIntire]

All:

I wish to print the Chapter name field from a number of records in the following way::

Chapter1;Chapter2;Chapter3;Chapter4;Chapter5. . . . .etc.

How do I do it programmatically?

Thanks,

Ron Mc

 

[scriverb]

Try this. Create a query that selects the records that you want to cancatenate together. Save it and update the following red code with the query name. Put this code in the reports On Open event Procedure.
Dim db as DAO.database
Dim rs as DAO.Recordset
Dim vChapterString as String
Set db = CurrentDB
Set rs = db.OpenRecordset("qryYourQuery", dbOpenDynaset)
vChapterString = ""
rs.MoveFirst
Do
vChapterString = vChapterString & rs("ChapterField&quot & ";"
rs.MoveNext
Loop Until rs.eof
vChapterString = Left(vChapterString, Len(ChapterString)-1)
rs.close
db.close
me.ChapterString = vChapterString

I assigned the string to a report control for display.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[RonMcIntire]

Bob:

Thanks for your reply. This is exactly what I needed but, I have a bit of a problem.

I have followed the logic, it makes sense and it's what I thought I needed to do but didn't quite know how. However, the routine doesn't want to compile and bombs on the last statement - "me.ChapterString = ." If I understand correctly the variable me.ChapterString should be the name of the Textbox control on the form but when I assign it to the control, I get the error "You can't assign a value to this object." What am I doing wrong?

QUESTION: It seems to me the line following the Loop...Until statement, the "...., Len(ChapterString statement, should be 'vChapterString'." Am I correct?

Thanks,

Ron

 

[scriverb]

You are correct about this statement as this was a typo:
vChapterString = Left(vChapterString, Len(vChapterString)-1)

Just add the red v. Sorry about that.

As far as the assignment back to the text box I think that problem only exists because of the variable naming problem above. Try running it with just the above stated fix.

Let me know.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[RonMcIntire]

Bob:

Well, I'm still having a few problems. Here's the actual code I'm using. The chapter field is "Chapter," the name of the textbox control is "ChapterString" and the punctuation, "; ", has an extra space - thus len = 2. It seems to me that A97 is just being obstinent. What do you think?

I'm getting a #2448 Run-Time error now, but still saying in the last statement that I "...can't assign a value to this object."

Private Sub Report_Open(Cancel As Integer)
Dim db As DAO.DATABASE
Dim rs As DAO.Recordset
Dim vChapterString As String

Set db = CurrentDb
Set rs = db.OpenRecordset("qryChapters", dbOpenDynaset)
vChapterString = ""
rs.MoveFirst
Do
vChapterString = vChapterString & rs("Chapter&quot & "; "
rs.MoveNext
Loop Until rs.EOF

vChapterString = Left(vChapterString, Len(vChapterString) - 2)
rs.Close
db.Close
Me.ChapterString = vChapterString

End Sub

Thanks,

Ron

 

[scriverb]

What I would do is put in a STOP command as below and when you code stops here analyze what is in vChapterString. Use the Immediate or Debug window. Also, try just to assign any value to me.ChapterString.

Look at the length of the string. Look for anything that may keep it from making the assignment.

Try changing the assignment to:
me.ChapterString = "xxxxx"

just to see if you can assign any value to it. Experiement. This will be executing live and you can by process of elimination figure out what the problem is.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[RonMcIntire]

Bob:

I found the problem: I set the event procedure to the On Format event in the Detail section and it works great.

Thanks again for your help.

Ron

 

[scriverb]

Great!!! I am glad to be of assistance.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???