Using VBA to open word doc, find/replace query results in ; delimited

[Goodge12]

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

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!

[pbaldy]

It appears that you're trying to use the same variable for the delimited list and the recordset. Declare a string variable for the list and use that instead:

VariableName = VariableName & ProdHierList![Prod_Hierarchy] & ";"

[Goodge12]

Thanks, that worked!

[pbaldy]

Happy to help!