Access to Word using bookmarks but from a Form rather than a tbl or qry

Question

Fairly new to access, this might be a really weird way of going about it but here's the situation, I have a form with a certain manager's information on it such as address and email, etc. I want to be able to create a letter with their address populated in the right spot on a Word Document and I've got pretty far I think But I've hit a roadblock. I can't figure out how to populate this word document with the address that's on the form, I've tried making a query that spits out the same information as the form but it kept coming back with the

"Run-time error 3061: Too few Parameters. Expected 1"

and the same thing is happening now when I've cut out the middle man and just put an SQL statement in the open recordset command.

The problem is with Line 12 (Starting Set rs = dbs.OpenRecordset...)

Thank you for any light you can shed on this!!

Private Sub btnManagerLetter_Click()

    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    Dim rs As DAO.Recordset
    Dim dbs As DAO.Database
    Dim address As String

    Set dbs = CurrentDb
    Set wdApp = New Word.Application
    Set wdDoc = wdApp.Documents.Open("\\middle\Data\DataBaseWordDocs&Ding\ding\InsCoLtrTemplate.docx")
    Set rs = dbs.OpenRecordset("SELECT * FROM tblManagers WHERE (((tblManagers.ManagerRef) Like Me![ManagerRef]))", dbOpenSnapshot)
        
    wdApp.Visible = True

    wdDoc.Bookmarks("ManagerName").Range.Text = Nz(rs![Manager Name], "")
    address = Nz(rs![Address Line 1], vbNullString)
    address = Append(address, Nz(rs![Address Line 2], vbNullString))
    address = Append(address, Nz(rs![Town], vbNullString))
    address = Append(address, Nz(rs![County], vbNullString))
    address = Append(address, Nz(rs![Post Code], vbNullString))
    
    wdDoc.Bookmarks("Address").Range.Text = address

End Sub

score 0 · Answer 1 · answered Sep 09 '20 at 16:51

Must concatenate Me![ManagerRef]. All those parens are not needed. LIKE without wildcard might as well be = sign. If ManagerRef is a text type field, will need delimiters.

Set rs = dbs.OpenRecordset("SELECT * FROM tblManagers WHERE ManagerRef ='" &  Me![ManagerRef] & "'", dbOpenSnapshot)

Otherwise, learn about using embedded parameters:
How do I use parameters in VBA in the different contexts in Microsoft Access?

Access to Word using bookmarks but from a Form rather than a tbl or qry

1 Answers1