Concatenate and write a recordset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two tables in a one-to-many relationship. The records in the germane
field on the many side are text. For each record in the one side of the
relationship, I would like to concatenate (witha hard return between each)
and write the many related records into an unbound textbox control on a form.


Can I do this? Any help with how?
 
Well, yes, you could do this. It would look something like the following
untested air-code. But why go to all this trouble, when you could so easily
display the related records in a list box or subform, simply by making the
rowsource or recordsource a query that refers to the PK of the record on the
one side in it's criteria? (e.g. "SELECT FieldName FROM ManyTable WHERE FK =
" & Me!PK).

That said, here's the aforementioned air-code ...

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strWork As String

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT FieldName FROM ManyTable WHERE FK = '" &
Me!PK)
Do Until rst.EOF
strWork = strWork & rst.Fields("FieldName")
rst.MoveNext
If Not rst.EOF Then
strWork = strWork & vbCrLf
End If
Loop
rst.Close
Me!TextBoxName = strWork

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Back
Top