Hi All,
I have an issue that I hope you guys will be able to resolve at a glance.
I am trying to use a button to generate a mail merge that draws data from a query in the access database.
I have executed the opening of the form/template that I would like to merge. I have also managed to merge the data on the word document successfully.
However....
This data does not merge successfully when I click the button to run the code that executes this.
I have got them working independently of each other however that is not sufficient for the users that will need this function.
Here is the code I am using to generate this mail merge.
I have used similar code before and was able to achieve the desired result on another database however I think I have missed a few tricks on the connection lines of the code.
The previous database that worked was .mdb whilst the new database is .accdb. The previous Access data base engine was: Jet 4.0 whilst the database in question is now using: Microsoft Office 12.0 Access Database Engine OLE DB Provider. The lines highlighted red is where I believe the issue lies however I am unable to unpick this with the knowledge I have.
Could someone confirm whether the red section is indeed incorrect?
I have used previously mentioned working code as a template and adjusted for the current database however I believe I have made an error somewhere.
Private Sub Command0_Click()
DoCmd.SetWarnings False
Set wordDoc = CreateObject("Word.Application")
Dim templatePath As String, sourceQuery As String
Dim qryDate As String
Dim qryDateUS As String
Dim dbDate As Date
qryDate = InputBox("Enter beginning date or leave blank for most recent forms...", "Please enter date")
If qryDate <> "" Then
qryDateUS = Format(qryDate, "mm/dd/yyyy")
Else
qryDateUS = Format(DateAdd("d", Date, -(Weekday(Date)) + 1), "mm/dd/yyyy")
End If
templatePath = "R:\Forms\MasterDupuytrensEvaluationRecord.doc x" 'your template here
sourceQuery = "Merge_Other_6M" 'name of the query in your database
wordDoc.DisplayAlerts = wdAlertsNone
wordDoc.Visible = True 'set connection as visible
wordDoc.Documents.Open templatePath 'open template
wordDoc.ActiveDocument.MailMerge.OpenDataSource Name:= _
"R:\Disc Logging Database.accdb" _
, ConfirmConversions:=False, ReadOnly:=False, linktosource:=False, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Connection:= _
"Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=R:\Disc Logging Database.accdb;;Password="""";User ID=Admin;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase P" _
, SQLStatement:="SELECT * FROM `" & sourceQuery & "` WHERE ((([Merge_Other_6M].DateAdded)>#" & qryDateUS & "#))", SQLStatement1:=""
wordDoc.ActiveDocument.MailMerge.Execute Pause:=False
wordDoc.PrintOut Background:=False
wordDoc.DisplayAlerts = wdAlertsAll
wordDoc.Documents.Close savechanges:=False
wordDoc.Application.Quit 'exit Word
Set wordDoc = Nothing
DoCmd.SetWarnings True
End Sub
Many thanks in advance.