How to pull data from a table to make a string

I have a table, TDr that contains a few names under string fields of FName and LName.  I would like to run through the table to get all the names to make a signature on a letter.  Ex. Dr. Joe Smith, Dr. Mary Jones etc)

I thought about using VBA to make the string and then set the value of a text box on the report open but I am not sure how to code this.

Any suggestions/solutions would be most appreciated.

Thank you.
thandelAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IrogSintaCommented:
No need to use VBA.  If this table is part of your recordsource of the report that you will be using for your letter, you can either place a textbox in your report with the following expression:
=[FName] & " " & [LName]

Or you can have that expression inside a calculated field in the query that you use in the report, for example:
FullName: [FName] & " " & [LName]

Then your report can just use the field called FullName.

Ron
0
thandelAuthor Commented:
Should have clarified, it is not part of my record source.  

I started with this but not sure how to append sDr to the text box text (control source)

    Dim rs As DAO.Recordset
    Dim sqlStr As String
    Dim sDr As String

    sqlStr = "SELECT * FROM tDr as sDr"

    Set db = CurrentDb
    Set rs = db.OpenRecordset(sqlStr)

    rs.MoveFirst

    Do While Not rs.EOF
      sDr = sDr & "Dr. " & rs!FName & " " & rs!LName & ", "
      rs.MoveNext
    Loop
   
    rs.Close 'Close the recordset
    Set rs = Nothing 'Clean up
0
thandelAuthor Commented:
The only way I found but seems silly is to set a label value and then have the text box append that label's value to the end of it control source.... bad idea/coding right?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mbizupCommented:
Assuming that sDR gives you the correct results, you can place that code in a public function, and call that function from your textbox's control source:

Function getSignatures() As String
    Dim rs As DAO.Recordset
    Dim sqlStr As String
    Dim sDr As String

    sqlStr = "SELECT * FROM tDr as sDr"

    Set db = CurrentDb
    Set rs = db.OpenRecordset(sqlStr)

    rs.MoveFirst

    Do While Not rs.EOF
      sDr = sDr & "Dr. " & rs!FName & " " & rs!LName & ", "
      rs.MoveNext
    Loop
    
    rs.Close 'Close the recordset
    Set rs = Nothing 'Clean up

    getSignatures = sDr
End Function

Open in new window


Then set your textbox control source as follows (include the = sign):

= getSignatures

Open in new window


Or something like this if you are appending it to some other value:

= [SomeField] & " " & getSignatures

Open in new window

0
IrogSintaCommented:
I would modify the loop a bit to get rid of the comma at the end of your signature string.
    Do While Not rs.EOF
        sDr = sDr & ", " & "Dr. " & rs!FName & " " & rs!LName 
        rs.MoveNext
    Loop
    sDr = Mid(sDr, 3)    'removes comma at beginning

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
Is there only one record in tDr because there is nothing the code that identifies which record you want.

What is the PK of tDr?  Is it a FK in the table you are using for the report?  If so, change to using a query that joins the two tables so you can select the name parts from tDr.  if tDr is sparse, then use a Left Join.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.