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