Need help on code to String records

[Sunflower]

I need to have all [APPROVERNAME] is rows in my subform
to be strung together with a ";" between them

Here is what I have done (not working) so far...

---------------------------------

MAIN FORM - frmWOEDIT
TEXTBOX - txtAPPROVERS_STRING
CONTROL SOURCE - =subfrmAPPROVALS.Form!txtAPPROVERSTRING

--------------------------------

SUBFORM - subfrmAPPROVALS
TEXTBOX (IN FOOTER) - txtAPPROVERSTRING



CODE -

Private Sub txtAPPROVERSTRING_Enter()

Dim RS As Recordset, strResult As String


Set RS = CurrentDb.OpenRecordset([tblAPPROVALS])
While Not RS.EOF
strResult = strResult & RS!APPROVERNAME & "; "
RS.MoveNext
Wend

End Sub

 

[BruceM]

Here are two generic function to concatenate records:

http://allenbrowne.com/func-concat.html
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16

Do you only want to see the concatenated records when you enter the text
box? If you want to see them at other times, such as when you navigate to
the record, you could use one of the functions in the form's Record Source
query, or in VBA in the form's Current event.

 

[KARL DEWEY]

Maybe Duane Hookom's generic concatenation
http://www.rogersaccesslibrary.com/...neric Function To Concatenate Child Records'e

Here are two generic function to concatenate records:

http://allenbrowne.com/func-concat.html
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16

Do you only want to see the concatenated records when you enter the text
box? If you want to see them at other times, such as when you navigate to
the record, you could use one of the functions in the form's Record Source
query, or in VBA in the form's Current event.

I need to have all [APPROVERNAME] is rows in my subform
to be strung together with a ";" between them

Here is what I have done (not working) so far...

---------------------------------

MAIN FORM - frmWOEDIT
TEXTBOX - txtAPPROVERS_STRING
CONTROL SOURCE - =subfrmAPPROVALS.Form!txtAPPROVERSTRING

--------------------------------

SUBFORM - subfrmAPPROVALS
TEXTBOX (IN FOOTER) - txtAPPROVERSTRING



CODE -

Private Sub txtAPPROVERSTRING_Enter()

Dim RS As Recordset, strResult As String


Set RS = CurrentDb.OpenRecordset([tblAPPROVALS])
While Not RS.EOF
strResult = strResult & RS!APPROVERNAME & "; "
RS.MoveNext
Wend

End Sub

 

[BruceM]

You may have an old link. It doesn't work in my browser. However, Duane's
example is where my second link is pointing.

Maybe Duane Hookom's generic concatenation
http://www.rogersaccesslibrary.com/...neric Function To Concatenate Child Records'e

Here are two generic function to concatenate records:

http://allenbrowne.com/func-concat.html
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16

Do you only want to see the concatenated records when you enter the text
box? If you want to see them at other times, such as when you navigate
to
the record, you could use one of the functions in the form's Record
Source
query, or in VBA in the form's Current event.

I need to have all [APPROVERNAME] is rows in my subform
to be strung together with a ";" between them

Here is what I have done (not working) so far...

---------------------------------

MAIN FORM - frmWOEDIT
TEXTBOX - txtAPPROVERS_STRING
CONTROL SOURCE - =subfrmAPPROVALS.Form!txtAPPROVERSTRING

--------------------------------

SUBFORM - subfrmAPPROVALS
TEXTBOX (IN FOOTER) - txtAPPROVERSTRING



CODE -

Private Sub txtAPPROVERSTRING_Enter()

Dim RS As Recordset, strResult As String


Set RS = CurrentDb.OpenRecordset([tblAPPROVALS])
While Not RS.EOF
strResult = strResult & RS!APPROVERNAME & "; "
RS.MoveNext
Wend

End Sub

 

[Sunflower]

Here are two generic function to concatenate records:

http://allenbrowne.com/func-concat....ccesslibrary.com/forum/forum_posts.asp?TID=16

Do you only want to see the concatenated records when you enter the text
box?  If you want to see them at other times, such as when you navigateto
the record, you could use one of the functions in the form's Record Source
query, or in VBA in the form's Current event.

I need to have all [APPROVERNAME] is rows in my subform
to be strung together with a ";" between them

Here is what I have done (not working) so far...

MAIN FORM - frmWOEDIT
TEXTBOX - txtAPPROVERS_STRING
CONTROL SOURCE - =subfrmAPPROVALS.Form!txtAPPROVERSTRING

SUBFORM - subfrmAPPROVALS
TEXTBOX (IN FOOTER) - txtAPPROVERSTRING

CODE -

Private Sub txtAPPROVERSTRING_Enter()

Dim RS As Recordset, strResult As String

Set RS = CurrentDb.OpenRecordset([tblAPPROVALS])
   While Not RS.EOF
       strResult = strResult & RS!APPROVERNAME & "; "
       RS.MoveNext
   Wend

End Sub

Any and all help greatly appreciated- Hide quoted text -

- Show quoted text -

I used the Duane Hookum suggestion...

Sorry to be a pest...but we are almost there...

I placed the module, and put the following code in the text box on my
main form:

=Concatenate("SELECT APPROVERNAME FROM tblAPPROVALS WHERE WOID =" &
[WOID])


What get is a string of the ID numbers and not the names,

So instead of returning ...

Mike; Cleave; Carol; Kelly

I get ...

196; 275; 24; 348



Thanks for your patience

 

[Sunflower]

Here are two generic function to concatenate records:

Do you only want to see the concatenated records when you enter the text
box?  If you want to see them at other times, such as when you navigate to
the record, you could use one of the functions in the form's Record Source
query, or in VBA in the form's Current event.

news:dc28ca77-c5f1-4a6e-be56-48e31343c293@k17g2000prn.googlegroups.com....

I need to have all [APPROVERNAME] is rows in my subform
to be strung together with a ";" between them
Here is what I have done (not working) so far...
---------------------------------
MAIN FORM - frmWOEDIT
TEXTBOX - txtAPPROVERS_STRING
CONTROL SOURCE - =subfrmAPPROVALS.Form!txtAPPROVERSTRING
--------------------------------
SUBFORM - subfrmAPPROVALS
TEXTBOX (IN FOOTER) - txtAPPROVERSTRING
CODE -
Private Sub txtAPPROVERSTRING_Enter()
Dim RS As Recordset, strResult As String
Set RS = CurrentDb.OpenRecordset([tblAPPROVALS])
   While Not RS.EOF
       strResult = strResult & RS!APPROVERNAME & "; "
       RS.MoveNext
   Wend
End Sub

- Show quoted text -

I used the Duane Hookum suggestion...

Sorry to be a pest...but we are almost there...

I placed the module, and put the following code in the text box on my
main form:

        =Concatenate("SELECT APPROVERNAME FROM tblAPPROVALS WHERE WOID =" &
[WOID])

What get is a string of the ID numbers and not the names,

So instead of returning ...

Mike; Cleave; Carol; Kelly

I get ...

196; 275; 24; 348

Thanks for your patience- Hide quoted text -

- Show quoted text -

I understand where the ID number is coming from...
My [APPROVERNAME] is a combobox linked to [tblCONTACTS]

ROW SOURCE:
SELECT tblCONTACTS.ContactID, tblCONTACTS.Name FROM tblCONTACTS ORDER
BY [Name];



I just dont know how to code it, to pull the [Name]



Thanks for your help