Concatenate all records from a subform

[Paul Pennington]

Can someone tell me how to create one concatenated
string from a set of records in a subform?

I am creating a research database for my doctoral
studies. Each item entered in the resource table (book,
article, etc.) can have multiple authors. So I have a
separate authors table, then a link table that allows
multiple authorIDs to connect to one resourceID.

On the Resource entry form I have a subform that shows
what authors are linked to that resource (or if none, I
can add them).

My last field takes all of the bibliographic data from
the different fields and concatenates them into a Citation
string for quick reference and export purposes. How do I
get the multiple author names and concatenate them into
the beginning of that Citation string. I have the build
string in the property's "default value" set as:
=[subfAuthors_in_a_Resource].[Form]![Lastname] & ", " &
[subfAuthors_in_a_Resource].[Form]![Initials] & ". " & "("
& [Date] & "). " & [Article] & ". " & [Title] & ", " &
[Edition] & ". " & [Publisher subform].[Form]!
[PublisherLocation] & ": " & [Publisher subform].[Form]!
[PublisherName] & ". " & [Accessed] & "."

Currently I have to save the record, then return to the
record and put my cursor in the Citation field. When I do
<ctl><alt><space> the field updates and includes the first
author name in the subform only.

I would actually prefer that after leaving the previous
field, the Citation string field automatically self-
creates. If you have any suggestions for that, it would
be appreciated too.
Thanks,
Paul Pennington
University of Cincinnati

 

[Ken Snell]

The subform contains the author info for a single resource record on the
main form? And the textbox that should trigger this code to run is the last
control on the main form?

Assuming this to be true, the way you do this is to loop through the
subform's recordset and build the string one record at a time. However, it's
not clear to me just which fields go where, and where those fields are (main
form or subform). If you can give more detailed info, a more specific code
example might be given.

To get you started, here is a generic code example for looping through a
subform's recordset and concatenating values (note that you need to use the
name of the field to which the subform's controls are bound, not the control
names, in this code, as shown by FieldName):

Dim strNames As String
strNames = ""
With Me.SubformControlName.Form.RecordsetClone
.MoveFirst
Do While .EOF = False
strNames = strNames & !FieldName & ";"
.MoveNext
Loop
End With

 

[Paul Pennington]

Thanks for the quick reply Ken. Here are the further
details.

You are correct that the subform has authors for a single
resource on the main form. Each author record in the
subform has four fields (Last, First, Initials,
Affiliation). I would like to cycle through the recordset
and build a string with Last & Initials for for all
related authors. It would look like this: Last1,
Initial1., Last2, Initial2, etc.

The total author string would then be concatenated to the
rest of the Citation string in the last field
(Citation$). The concatenate code in my original posting
shows how the other fields from the main form are joined
together. I just need to add the author info from the
subform. The rest of the concatenation works fine.

Thanks
Paul Pennington
UC

-----Original Message-----
The subform contains the author info for a single resource record on the
main form? And the textbox that should trigger this code to run is the last
control on the main form?

Assuming this to be true, the way you do this is to loop through the
subform's recordset and build the string one record at a time. However, it's
not clear to me just which fields go where, and where those fields are (main
form or subform). If you can give more detailed info, a more specific code
example might be given.

To get you started, here is a generic code example for looping through a
subform's recordset and concatenating values (note that you need to use the
name of the field to which the subform's controls are bound, not the control
names, in this code, as shown by FieldName):

Dim strNames As String
strNames = ""
With Me.SubformControlName.Form.RecordsetClone
.MoveFirst
Do While .EOF = False
strNames = strNames & !FieldName & ";"
.MoveNext
Loop
End With


--
Ken Snell
<MS ACCESS MVP>

Can someone tell me how to create one concatenated
string from a set of records in a subform?

I am creating a research database for my doctoral
studies. Each item entered in the resource table (book,
article, etc.) can have multiple authors. So I have a
separate authors table, then a link table that allows
multiple authorIDs to connect to one resourceID.

On the Resource entry form I have a subform that shows
what authors are linked to that resource (or if none, I
can add them).

My last field takes all of the bibliographic data from
the different fields and concatenates them into a Citation
string for quick reference and export purposes. How do I
get the multiple author names and concatenate them into
the beginning of that Citation string. I have the build
string in the property's "default value" set as:
=[subfAuthors_in_a_Resource].[Form]![Lastname] & ", " &
[subfAuthors_in_a_Resource].[Form]![Initials] & ". " & "("
& [Date] & "). " & [Article] & ". " & [Title] & ", " &
[Edition] & ". " & [Publisher subform].[Form]!
[PublisherLocation] & ": " & [Publisher subform].[Form]!
[PublisherName] & ". " & [Accessed] & "."

Currently I have to save the record, then return to the
record and put my cursor in the Citation field. When I do
<ctl><alt><space> the field updates and includes the first
author name in the subform only.

I would actually prefer that after leaving the previous
field, the Citation string field automatically self-
creates. If you have any suggestions for that, it would
be appreciated too.
Thanks,
Paul Pennington
University of Cincinnati

.

 

[Ken Snell]

You can modify the generic code I provided this way (not tested!):

Dim strNames As String
strNames = ""
With Me.SubformControlName.Form.RecordsetClone
.MoveFirst
Do While .EOF = False
strNames = strNames & !LastName & ", " & _
!Initials & "., "
.MoveNext
Loop
End With
If Right(strNames, 2) = ", " Then strNames = _
Left(strNames, Len(strNames) - 2)
Me.Citation.Value = Me.Citation.Value & " " & strNames
--
Ken Snell
<MS ACCESS MVP>

Thanks for the quick reply Ken. Here are the further
details.

You are correct that the subform has authors for a single
resource on the main form. Each author record in the
subform has four fields (Last, First, Initials,
Affiliation). I would like to cycle through the recordset
and build a string with Last & Initials for for all
related authors. It would look like this: Last1,
Initial1., Last2, Initial2, etc.

The total author string would then be concatenated to the
rest of the Citation string in the last field
(Citation$). The concatenate code in my original posting
shows how the other fields from the main form are joined
together. I just need to add the author info from the
subform. The rest of the concatenation works fine.

Thanks
Paul Pennington
UC

-----Original Message-----
The subform contains the author info for a single resource record on the
main form? And the textbox that should trigger this code to run is the last
control on the main form?

Assuming this to be true, the way you do this is to loop through the
subform's recordset and build the string one record at a time. However, it's
not clear to me just which fields go where, and where those fields are (main
form or subform). If you can give more detailed info, a more specific code
example might be given.

To get you started, here is a generic code example for looping through a
subform's recordset and concatenating values (note that you need to use the
name of the field to which the subform's controls are bound, not the control
names, in this code, as shown by FieldName):

Dim strNames As String
strNames = ""
With Me.SubformControlName.Form.RecordsetClone
.MoveFirst
Do While .EOF = False
strNames = strNames & !FieldName & ";"
.MoveNext
Loop
End With


--
Ken Snell
<MS ACCESS MVP>

Can someone tell me how to create one concatenated
string from a set of records in a subform?

I am creating a research database for my doctoral
studies. Each item entered in the resource table (book,
article, etc.) can have multiple authors. So I have a
separate authors table, then a link table that allows
multiple authorIDs to connect to one resourceID.

On the Resource entry form I have a subform that shows
what authors are linked to that resource (or if none, I
can add them).

My last field takes all of the bibliographic data from
the different fields and concatenates them into a Citation
string for quick reference and export purposes. How do I
get the multiple author names and concatenate them into
the beginning of that Citation string. I have the build
string in the property's "default value" set as:
=[subfAuthors_in_a_Resource].[Form]![Lastname] & ", " &
[subfAuthors_in_a_Resource].[Form]![Initials] & ". " & "("
& [Date] & "). " & [Article] & ". " & [Title] & ", " &
[Edition] & ". " & [Publisher subform].[Form]!
[PublisherLocation] & ": " & [Publisher subform].[Form]!
[PublisherName] & ". " & [Accessed] & "."

Currently I have to save the record, then return to the
record and put my cursor in the Citation field. When I do
<ctl><alt><space> the field updates and includes the first
author name in the subform only.

I would actually prefer that after leaving the previous
field, the Citation string field automatically self-
creates. If you have any suggestions for that, it would
be appreciated too.
Thanks,
Paul Pennington
University of Cincinnati

.

 

[Paul Pennington]

Ken,
Thanks for code. That gives me what I need, I think.
Paul Pennington
UC

-----Original Message-----
You can modify the generic code I provided this way (not tested!):

Dim strNames As String
strNames = ""
With Me.SubformControlName.Form.RecordsetClone
.MoveFirst
Do While .EOF = False
strNames = strNames & !LastName & ", " & _
!Initials & "., "
.MoveNext
Loop
End With
If Right(strNames, 2) = ", " Then strNames = _
Left(strNames, Len(strNames) - 2)
Me.Citation.Value = Me.Citation.Value & " " & strNames
--
Ken Snell
<MS ACCESS MVP>

Thanks for the quick reply Ken. Here are the further
details.

You are correct that the subform has authors for a single
resource on the main form. Each author record in the
subform has four fields (Last, First, Initials,
Affiliation). I would like to cycle through the recordset
and build a string with Last & Initials for for all
related authors. It would look like this: Last1,
Initial1., Last2, Initial2, etc.

The total author string would then be concatenated to the
rest of the Citation string in the last field
(Citation$). The concatenate code in my original posting
shows how the other fields from the main form are joined
together. I just need to add the author info from the
subform. The rest of the concatenation works fine.

Thanks
Paul Pennington
UC

-----Original Message-----
The subform contains the author info for a single resource record on the
main form? And the textbox that should trigger this

code
to run is the last

control on the main form?

Assuming this to be true, the way you do this is to

loop
through the

subform's recordset and build the string one record at

a
time. However, it's

not clear to me just which fields go where, and where those fields are (main
form or subform). If you can give more detailed info, a more specific code
example might be given.

To get you started, here is a generic code example for looping through a
subform's recordset and concatenating values (note that you need to use the
name of the field to which the subform's controls are bound, not the control
names, in this code, as shown by FieldName):

Dim strNames As String
strNames = ""
With Me.SubformControlName.Form.RecordsetClone
.MoveFirst
Do While .EOF = False
strNames = strNames & !FieldName & ";"
.MoveNext
Loop
End With


--
Ken Snell
<MS ACCESS MVP>

Can someone tell me how to create one concatenated
string from a set of records in a subform?

I am creating a research database for my doctoral
studies. Each item entered in the resource table (book,
article, etc.) can have multiple authors. So I have a
separate authors table, then a link table that allows
multiple authorIDs to connect to one resourceID.

On the Resource entry form I have a subform that shows
what authors are linked to that resource (or if none, I
can add them).

My last field takes all of the bibliographic data from
the different fields and concatenates them into a Citation
string for quick reference and export purposes. How

do
I

get the multiple author names and concatenate them into
the beginning of that Citation string. I have the build
string in the property's "default value" set as:
=[subfAuthors_in_a_Resource].[Form]![Lastname] & ", " &
[subfAuthors_in_a_Resource].[Form]![Initials] & ". " & "("
& [Date] & "). " & [Article] & ". " & [Title] & ", " &
[Edition] & ". " & [Publisher subform].[Form]!
[PublisherLocation] & ": " & [Publisher subform]. [Form]!
[PublisherName] & ". " & [Accessed] & "."

Currently I have to save the record, then return to the
record and put my cursor in the Citation field.

When I
do

<ctl><alt><space> the field updates and includes the first
author name in the subform only.

I would actually prefer that after leaving the previous
field, the Citation string field automatically self-
creates. If you have any suggestions for that, it would
be appreciated too.
Thanks,
Paul Pennington
University of Cincinnati


.

.