Thanks June. This is all so helpful.
I'm now able to send the concatenated string to my Word bookmark (so cool):
Code:
m_objDoc.Bookmarks("HIVRiskFactors").Select
m_objWord.Selection.Text = ConcatRelated("[HIV_Risk_Factors]", "[tbl_CAPRAP_Intro_Demographics_ID]")
Regarding the criteria to group the function on, I'd like to run it only on the current record, most likely run by clicking a command button at the end of the form. I will have a form with about 10 tabs (and probably hundreds of those m_obj bookmark exports in the underlying code).
I'm thinking of a few options for restricting this to the current record. I have seen the "Me!" property sometimes--I'm wondering if I should not bother at all with the recordset (so remove those opening bits of code that establish and navigate through the recordset) and replace all of the instances of rs! later in the code with Me!
Another idea I have is to have a unique ID, something like the first few letters of the client's first and last names, plus the assessment date or whatever, run a query to group the shared record for all of the 10 subforms, and then run the code just on that queried data.
Does either of those make sense? Below is my current code, which is still based in the recordset idea:
Code:
Option Compare DatabaseOption Explicit
Sub ExportToWord()
Dim rs As Object, m_objWord As Object, m_objDoc As Object, wname As String, SQL As String, nameaddress As String, newname As String
Dim fromfile As String, tofile As String, formattedDate As String, count As Long
'Set record set"
Set rs = CurrentDb.OpenRecordset("tbl_CAPRAP_Intro_Demographics_ID", dbOpenDynaset)
If Not rs.EOF Then
rs.MoveLast
rs.MoveFirst
End If
'Format the Assessment Date data so that it can be appended to new file name"
formattedDate = format(rs![Assessment_Date], "mm-dd-yyyy")
'Call source Word template file to insert the exported Access data"
wname = "C:\...\CAP-RAP1_.dotx"
For count = 1 To rs.RecordCount
Set m_objWord = New Word.Application
Set m_objDoc = m_objWord.Documents.Add(wname)
'Grab data from Access and export to bookmarked locations in the Word template. The first line in each pair of code lines sets the focus on the bookmark location in Word, and the second line of code specifies the field from where the data is drawn in Access to paste into the set location."
m_objDoc.Bookmarks("MCMfirstName").Select
m_objWord.Selection.Text = rs!MCM_First_Name
m_objDoc.Bookmarks("MCMlastName").Select
m_objWord.Selection.Text = rs!MCM_Last_Name
m_objDoc.Bookmarks("FirstName").Select
m_objWord.Selection.Text = rs!First_Name
m_objDoc.Bookmarks("LastName").Select
m_objWord.Selection.Text = rs!Last_Name
m_objDoc.Bookmarks("DOB").Select
m_objWord.Selection.Text = rs!DOB
m_objDoc.Bookmarks("SSN").Select
m_objWord.Selection.Text = rs!SSN
m_objDoc.Bookmarks("Pronouns").Select
m_objWord.Selection.Text = rs!Pronouns
m_objDoc.Bookmarks("AssessmentDate").Select
m_objWord.Selection.Text = rs!Assessment_Date
m_objDoc.Bookmarks("PreviousAssessmentDate").Select
m_objWord.Selection.Text = rs!Previous_Assessment_Date
m_objDoc.Bookmarks("MCMEnrollmentDate").Select
m_objWord.Selection.Text = rs!MCM_Enrollment_Date
m_objDoc.Bookmarks("HIVRiskFactors").Select
m_objWord.Selection.Text = ConcatRelated("[HIV_Risk_Factors]", "[tbl_CAPRAP_Intro_Demographics_ID]")
'Save completed Word file with exported data as new file, appending the client's full name and date of assessment to the file name"
newname = "C:\...\CAP-RAP1_" & rs![Last_Name] & "_" & rs![First_Name] & "_" & formattedDate & ".docx"
'Save Word doc
m_objDoc.SaveAs FileName:=newname
m_objDoc.Save
m_objDoc.Close
m_objWord.Quit
' clean up
Set m_objWord = New Word.Application ' not actually needed, it opens the new doc to ensure it is OK
m_objWord.Visible = True 'the same
Set m_objDoc = m_objWord.Documents.Add(newname) ' The same
MsgBox "this New Document has been saved as - " & newname ' The same
m_objDoc.Close ' The same
m_objWord.Quit ' The same
rs.MoveNext
Next count
'MsgBox "this New Document has been saved"
rs.Close
Set rs = Nothing
Set m_objWord = Nothing
Exit_Command1_Click:
Exit Sub
Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click
End Sub