I was wondering why am I getting this error?-CodePudding

(fake entries and file directories)

This is the error I got when trying to run a macro that takes values from a query and assigns them to a bookmark'd location on a word .docx

this is the line it fails on

Set rs = CurrentDb.OpenRecordset("Query1", dbOpenDynaset)

this is the query that the vba code is taking values from

SELECT Table1.ProjectNo, Table1.FirstName, Table1.LastName, Table1.Phone, Table1.ProjectName, Table1.EMail, Table1.Client
FROM Table1
WHERE (((Table1.LastName) Like "*" & [Forms]![Form3]![SearchBox] & "*"));

Private Sub Command9_Click()
Dim wApp As Word.Application
Dim wDoc As Word.Document
Dim rs As DAO.Recordset


Set wApp = New Word.Application
Set wDoc = wApp.Documents.Open("C:\Documents\ECORtester.docx")
Set rs = CurrentDb.OpenRecordset("Query1", dbOpenDynaset)

If Not rs.EOF Then rs.MoveFirst

Do Until rs.EOF

    wDoc.Bookmarks("Attention").Range.Text = Nz(rs![FirstName], "") & Nz(rs![LastName], "")
    wDoc.Bookmarks("Client").Range.Text = Nz(rs![Client], "")
    wDoc.Bookmarks("Email").Range.Text = Nz(rs![Email], "")
    wDoc.Bookmarks("Phone").Range.Text = Nz(rs![Phone], "")
    wDoc.Bookmarks("ProjectName").Range.Text = Nz(rs![ProjectName], "")
    wDoc.Bookmarks("ProjectNumber").Range.Text = Nz(rs![ProjectNo], "")
    wDoc.SaveAs2 "C:\Documents" & rs!ProjectNumber & "_ECORtester.docx"
    rs.MoveNext
    
Loop
End Sub

CodePudding user response：

Your WHERE clause has a parameter in it that isn't being set.

Define it as a querydef and create the recordset from that.

    Dim qdf As QueryDef
    Dim rs As Recordset
    Set qdf = CurrentDb.QueryDefs("Query1")
    qdf.Parameters("[Forms]![Form3]![SearchBox]") = [Forms]![Form3]![SearchBox]
    
    Set rs = qdf.OpenRecordset

CodePudding user response：

Recordset based on query object that has undefined/unset dynamic parameter won't work. Instead, base recordset on table and build SQL in VBA.

strSQL = "SELECT * FROM Table1 WHERE LastName Like "*" & [Forms]![Form3]![SearchBox] & "*"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

Using LIKE with wildcard could return multiple clients. If there are multiple people with same/similar last name, they could all be retrieved. If you want only one client, then use unique record ID as filter criteria. A combobox may be more useful than a free-form input textbox. With code, combobox can implement pattern matching and 'filter as you type' functionality but then selection is made from listed items and unique ID is available.