Results 1 to 11 of 11
  1. #1
    Perineum is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2012
    Posts
    28

    Using VBA copying multiple fields to one table cell in Word

    I'm using MS Access 2013. I'm using VBA code to create a MS Word doc. I need to copy date field(s) and text field(s) to ONE table cell in MS Word. It should look something like this within the cell:

    Date1



    • Update for PM#1 summary text
    • Update for PM#2 summary text
    • Update for PM#4 summary text


    Date2

    • Update for PM#2 summary text
    • Update for PM#3 summary text
    • Update for PM#5 summary text


    I tried using a recordset to get the date, update text and toggle value. The toggle value determines if it goes in Cell A or Cell B. I can set the cell equal to either the date or the update details but not both. I can add carriage returns. I tried putting a date and update details into an array and then setting the cell equal to that. Didn’t work. I then tried a string instead of an array. Didn't work. I tried adding a "text form field" to the Word doc. Instead of setting the values equal to a cell, I also tried the above methods with:

    .Bookmarks("txtactionitem").Range.Fields(1).Result .Text = array or string or recordset values
    .FormFields("txtactionitem ").Result = array or string or recordset values

    Didn't work. I played around with Range.InsertAfter but I don’t know how I would use it in this scenario. Here is some code. I have remmed out some failed attempts:

    Code:
    sAction = "SELECT [tbl_PM_Updates.Update Date], [tbl_PM_Updates.Update Details], tbl_PM_Updates.Toggle  FROM tbl_PM_Updates WHERE (((tbl_PM_Updates.[SMART])= '" & ticketno & "'));"
       
        Set rs5 = db.OpenRecordset(sAction, dbOpenSnapshot)
           
        With rs5
            If .RecordCount <> 0 Then
                .MoveLast   'Ensure proper count
                iRecCount = .RecordCount    'Number of records returned by the table/query
                .MoveFirst
                iFldCount = .Fields.Count   'Number of fields/columns returned by the table/query
               
                Dim tggle As String
                Dim cellstring As String
                      
                For i = 0 To iRecCount - 1
                    tggle = Nz(rs5.Fields(2).Value, "")
                    If tggle = 1 Then
                        Set oWordTbl = doc.Tables(10)
                        'oWordTbl.Cell(1, 1) = Nz(rs5.Fields(1).Value, "") + vbCr 
                        'oWordTbl.Cell(1, 1).txtactivedate.InsertAfter "Nz(rs5.Fields(1).Value, "")"
                        cellstring = Nz(rs5.Fields(0).Value, "") + vbCr + Nz(rs5.Fields(1).Value, "")
                        oWordTbl.Cell(1, 1) = cellstring
                        '.Bookmarks("txtactionitem").Range.Fields(1).Result.Text = cellstring
                    Else
                        Set oWordTbl = doc.Tables(13)
                        oWordTbl.Cell(1, 1) = Nz(rs5.Fields(0).Value, "")
                    End If
                    tggle = ""
                Next i
           End If
         End With
    Any ideas on how I could do this?

    Many thanks!
    Sean

  2. #2
    Perineum is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2012
    Posts
    28
    I'm stumped. I hope someone out there knows how to do this. Or maybe there is no way to do this.

    thanks

  3. #3
    Perineum is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2012
    Posts
    28
    I tried something completely new and it almost works. I use a query to select multiple rows. I use a second query based on the first query to select only the rows that equal a PM#. I also use a concatenate function that concatenates data from several records into one field. I then put the second query into a recordset. I loop through the recordset and put the concatenated field contents into a table cell in Word. This works. One catch. I hardcorded the PM# into the second query. When I replace the PM# with [Forms]![frm_PM_Main]![txt_PM#], it does not work. I run the code off a button on the form with the [txt_PM#] field on the form. This should work, shouldn't it? What am I doing wrong?

    thanks in advance

    Set rs5 = db.OpenRecordset("qry_concattoggle2", dbOpenDynaset)

    With rs5
    If .RecordCount <> 0 Then
    .MoveLast 'Ensure proper count
    iRecCount = .RecordCount 'Number of records returned by the table/query
    .MoveFirst
    iFldCount = .Fields.Count 'Number of fields/columns returned by the table/query

    Dim tggle As String

    For i = 0 To iRecCount - 1
    tggle = Nz(rs5.Fields(1).Value, "")
    If tggle = 1 Then
    Set oWordTbl = doc.Tables(10)
    oWordTbl.Cell(1, 1) = Nz(rs5.Fields(0).Value, "") + vbCr + vbCr
    qry_toggle1:
    SELECT tbl_PM_Updates.[PM#], tbl_PM_Updates.[Update Details], tbl_PM_Updates.Toggle
    FROM tbl_PM_Updates
    WHERE (((tbl_PM_Updates.Toggle)="1"));
    qry_concattoggle2
    SELECT DISTINCT ConcatRelated("[Update Details]","qry_toggle1","[PM#] = """ & [PM#] & """") AS Expr1, qry_toggle1.Toggle
    FROM qry_toggle1
    WHERE (((qry_toggle1.Toggle)="1") AND ((qry_toggle1.[PM#])="1000004-0-0"));

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    What does 'does not work' mean - error message, wrong results, nothing happens?

    Should show the failing query WHERE clause. Did you try:

    WHERE qry_toggle1.Toggle="1" AND qry_toggle1.[PM#]='" & [Forms]![frm_PM_Main]![txt_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.

  5. #5
    Perineum is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2012
    Posts
    28
    Sorry, I was off for a few days. Thanks for the reply. When I ran it with [Forms]![frm_PM_Main]![txt_PM#] instead of the PM#, no update details were copied over to Word.

    In the query that failed I simply modified the query: qry_concattoggle2. I went into design mode and in the Criteria box under PM# I changed "1000004-0-0" to [Forms]![frm_PM_Main]![txt_PM#]. Here is the sql:
    SELECT DISTINCT ConcatRelated("[Update Details]","qry_toggle1","[PM#] = """ & [PM#] & """") AS Expr1, qry_toggle1.Toggle
    FROM qry_toggle1
    WHERE (((qry_toggle1.Toggle)="1") AND ((qry_toggle1.[PM#])=[Forms]![frm_PM_Main]![txt_PM#]));

    In the code, if I hover of the rs5.Fields(0) area, I get the message: "Object variable or With block variable not set".

    I tried your suggestion. I get a syntax error. I dropped the double quotes at the end and added a semi-colon and it ran but the update details did not get copied over to Word.

    WHERE qry_toggle1.Toggle="1" AND qry_toggle1.[PM#]='" & [Forms]![frm_PM_Main]![txt_PM#] & "'"

    I changed it to: WHERE qry_toggle1.Toggle="1" AND qry_toggle1.[PM#]='" & [Forms]![frm_PM_Main]![txt_PM#] & "';

    thanks

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Sorry, I was mixing Access query object statement with VBA constructing SQL statement.

    In QueryDesign should not need the apostrophes for the PM# parameter in the WHERE clause. Below the PM# field in design grid just enter [Forms]![frm_PM_Main]![txt_PM#]. Access intellisense popups should help you get this correct.

    Then the result in SQLView should be:

    SELECT DISTINCT ConcatRelated("[Update Details]","qry_toggle1","[PM#] = '" & [PM#] & "'") AS Expr1, qry_toggle1.Toggle
    FROM qry_toggle1
    WHERE qry_toggle1.Toggle="1" AND qry_toggle1.[PM#]=[Forms]![frm_PM_Main]![txt_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.

  7. #7
    Perineum is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2012
    Posts
    28
    For the query I was trying, I did just enter [Forms]![frm_PM_Main]![txt_PM#] without the quotes in Design mode. I tested it but the update details did not get copied over to Word.

    I copied your sql above into SQL View and got the same result - update details did not get copied over to Word...

    thanks

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Set breakpoint, step debug. Hover cursor over recordset field references. Are values in the recordset fields as expected?

    Do you really need to apply filter in the Concat() query? Maybe should do it in the VBA Recordset construction.
    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.

  9. #9
    Perineum is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2012
    Posts
    28
    Brilliant Dude! It worked. I didn't know that you could do a select in the recordset creation.

    qry_concattoggle3:
    SELECT DISTINCT ConcatRelated("[Update Details]","qry_toggle1", "[PM#] = """ & [PM#] & """") AS Expr1, qry_toggle1.Toggle, qry_toggle1.[PM#]
    FROM qry_toggle1;

    VBA code: Set rs5 = db.OpenRecordset("SELECT * from qry_concattoggle3 WHERE ((qry_concattoggle3.[PM#])='" & PMnum & "')", dbOpenDynaset)

    I removed the filter from the Concat() function like you suggested but it returned rows from other PM#'s. I added it back in (as you see above) and it worked.

    Many thanks! What a relief

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I did not mean for you to remove filter from the Concat() function - I said to remove from the Concat query WHERE clause, the query using the Concat(). Guess I should have said from qry_concattoggle2 WHERE clause. Sorry for confusion and glad you got it sorted out.

    Also, your original code does show concatenating dynamic parameter in the SQL construction.
    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.

  11. #11
    Perineum is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2012
    Posts
    28
    Yes, I removed the WHERE clause from qry_concattoggle3....it was redundant...

    I guess the query could not get the PM# using
    [Forms]![frm_PM_Main]![txt_PM#]...it had to be attained using sql embedded in the VBA code...

    anyway, I really appreciate your help! thanks again

Please reply to this thread with any new information or opinions.

Similar Threads

  1. extract multiple values from a cell to a separate table?
    By benlogo in forum Import/Export Data
    Replies: 4
    Last Post: 08-18-2015, 06:35 AM
  2. Replies: 2
    Last Post: 09-15-2014, 04:20 PM
  3. Replies: 1
    Last Post: 07-12-2013, 01:48 PM
  4. Call word object and import word fields
    By silverspr in forum Programming
    Replies: 3
    Last Post: 12-10-2012, 11:32 PM
  5. Copying Access Form to a Word Doc
    By sxottwc in forum Forms
    Replies: 3
    Last Post: 07-16-2011, 06:19 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
  •  
Other Forums: Microsoft Office Forums