Export Multiselected Table Data to Word Bookmark

[PrincessApril]

Okay so the Allen Browne ConcatenateRelated() function looks great. I want to try to use it.



I was thinking that I can run the ConcatRelated() public sub and specify the arguments so that it generates a string value for a multiselect item. So, let's take HIV risk factors as an example (can be intravenous drug use, sexual contact, blood transfusion, etc.).

So in my noob brain, I'm thinking that I call the function and pass the resulting value to my Word doc, just like I'm doing with the code in the OP. I've gotten this far:

Call ConcatRelated.ConcatRelated("[HIV_Risk_Factors]", "[tbl_CAPRAP_Intro_Demographics_ID]")
        m_objDoc.Bookmarks("HIVRiskFactors").Select
        m_objWord.Selection.Text = strOut

But I get an error message saying that strOut is not defined (however it appears to be defined in the public sub ConcatRelated(), so I thought the output value would be there somehow (I'm still having trouble trying to visualize where output values of functions exist after the functions are run--I'm kind of picturing them floating in space hehe). I tried adding Dim strOut as String and the code didn't show an error, but it passed no data to my Word bookmark. How do I grab that floating-in-neverland output value to pass in my third line of code?

Or is it much more complicated than I'm trying to do.

[June7]

Module and function cannot have same name. No need to prefix with module name. This function must be in a general module.

Usually this function is called from a query or textbox. You have not identified any criteria for the function to group on, such as a PatientID. Without it, you will get all records.

In VBA, function calls must be on one side of an expression (usually the right side) and the keyword Call is not used.

m_objWord.Selection.Text = ConcatRelated("fieldname", "tablename", "somefield=" & Me!someparameter)

The strOut variable only lives within the ConcatRelated process. It dies when the function completes. The function sets its value in the line: ConcatRelated = Left(strOut, lngLen)
and that is what is returned to calling procedure.

[PrincessApril]

Thanks June. This is all so helpful.

I'm now able to send the concatenated string to my Word bookmark (so cool):

           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:

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

[June7]

You can pull criteria from current record on form or from a recordset that is looped. The latter would be if you want to automate output of multiple documents. All depends on your needs.

A unique identifier would certainly be needed for code to pull appropriate records. This can be an autonumber generated ID. Since autonumber ID should have no meaning to users (they don't ever need to see it), use some other meaningful info for users to identify client - name, SSN, email, address, account number, etc. However, code can use the associated autonumber key.

[PrincessApril]

That makes sense, but I wasn't sure if the autonumber only applies to the first tab of the form or to all tabs? That's why I was wondering if I had to create a custom unique ID in addition to the autonumber and make sure it appears on each tab.

[June7]

What tabs - a Tab control or a Navigation Form? Tab control is irrelevant in referencing a form's fields and controls.

[PrincessApril]

Oh sorry, I mean how you can add multiple pages to a form, but I hadn't actually gotten there yet. I was thinking that each page sheet would come from a different control source, but I guess that's incorrect and all the page sheets are attached to the same control source (so in that case the autonumber id makes sense--I guess I just query all of my data tables and set the query as the control source for the multipage form).

[June7]

Controls on each tab page would be associated with same RecordSource unless you put a subform on tab page.

A form is normally associated with only one table source for data entry/edit. Then subforms are used for related dependent tables. You need identifier from the 'parent' or 'master' table. This does not require a RecordSource that joins all related tables.