Hi All-
I need some help, please! I've got a query (qry_prod_hierarchy_list) that pulls a list of items. I need to be able to change this from the normal query list:
Item 1
Item 2
Item 3
Item 4
to a string of delimited text in a word document:
Item 1; Item 2; Item 3; Item 4
I've been able to do this in the past with email addresses into the "To" box, but trying to take that code and apply it here isn't working. Here's what I've got...
Code:
Private Sub Command1_Click()
Dim db As DAO.Database
Dim ProdHierList As Object, count As Integer, sql As String
Set db = CurrentDb
Set ProdHierList = db.OpenRecordset("qry_prod_hierarchy_list")
Dim objWord As Word.Application
Set objWord = New Word.Application
With objWord
.Documents.Open "mypath\myworddoc.docx"
.Documents("mypath\myworddoc.docx").Activate
.Visible = True
End With
If ProdHierList.RecordCount > 0 Then
ProdHierList.MoveLast
ProdHierList.MoveFirst
recount = ProdHierList.RecordCount
For count = 1 To recount
If Not IsNull(ProdHierList![Prod_Hierarchy]) Then ProdHierList = ProdHierList & ProdHierList![Prod_Hierarchy] & ";"
ProdHierList.MoveNext
Next count
ProdHierList = Left(ProdHierList, Len(ProdHierList) - 1)
With objWord.Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "Enter Prod Hierarchy List"
.Replacement.Text = ProdHierList
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute Replace:=2 'wdReplaceAll
End With
End If
End Sub
I get a Run Time Error '3001': Invalid Argument
When I click the "Debug", it highlights "ProdHierList = ProdHierList & ProdHierList![Prod_Hierarchy] & ";"
I'm open to suggestions!
Thank you!