Page 2 of 2 FirstFirst 12
Results 16 to 23 of 23
  1. #16
    PrincessApril is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    42
    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:

    Code:
    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.

  2. #17
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,815
    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.



    Last edited by June7; 02-09-2020 at 09:44 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #18
    PrincessApril is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    42
    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

  4. #19
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,815
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #20
    PrincessApril is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    42
    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.

  6. #21
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,815
    What tabs - a Tab control or a Navigation Form? Tab control is irrelevant in referencing a form's fields and controls.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #22
    PrincessApril is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    42
    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).

  8. #23
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,815
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to export access data to word
    By Wolphe in forum Programming
    Replies: 19
    Last Post: 02-02-2018, 10:58 AM
  2. Export Data from Access to Word
    By Byrkster in forum Access
    Replies: 4
    Last Post: 07-22-2016, 08:00 AM
  3. Export to Word Table
    By gstylianou in forum Access
    Replies: 39
    Last Post: 11-03-2014, 11:22 AM
  4. Replies: 0
    Last Post: 02-12-2013, 11:39 AM
  5. Replies: 7
    Last Post: 02-01-2013, 02:58 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums