I have created a database of allotment gardens, and use the following VBA (copied and modified from elsewhere) to generate an invoice for the allotment holder whose name is selected from a combo box in a form.
The combo box displays the holder's name from a query, and returns the numeric HolderID which is used to select records for the mailmerge.
It all works as intended, but I would like the merged Word document name to include the holder's name.
I have no idea how to modify the code to extract this information from the database. Can anyone help please?
TIA - Mike
Private Sub cboHolderName_AfterUpdate()
Dim strMergedDocName As String
strMergedDocName = "Allotment Invoice " & Format(CStr(Date), "dd MMM yyyy")
Dim objWord As Word.Document
Set objWord = GetObject(CurrentProject.Path & "\templates\InvoiceTemplate.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the allotments database.
objWord.MailMerge.OpenDataSource Name:=CurrentDb.Name, _
LinkToSource:=True, Connection:="QUERY qryInvoice", _
SQLStatement:="SELECT * FROM [qryInvoice] WHERE qryInvoice.HolderID = " & cboHolderName.Value
' Execute the mail merge.
objWord.Application.Documents(1).SaveAs (CurrentProject.Path & "\mailmerge\" & strMergedDocName & ".doc")
DoCmd.Close acForm, Me.Name