Merge Function

[TallMan]

Hello,

I am trying to conduct a mailmerge by calling a function. Basically the main table "60_Day_Clientlettertbl" has a "pending_notes" field. Depending on the pending field one of two letters needs to populate.

Once the code determines which letter to use it calls a function. This is the code that calls the function.

Private Sub Client_Letter_Mail_Merge_Click()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim stDoc As String
Dim stNote As String
'Get unique Pending_Notes
rst.Open "SELECT DISTINCT Pending_Notes FROM 60_Day_ClientletterTBL", CurrentProject.Connection, adOpenKeyset
While Not rst.EOF
stNote = rst.Fields(0)
Select Case stNote
 
Case Is = "Waiting"
            stDoc = "Waiting.DOC"
Case Is = "Authorization"
            stDoc = "Authorization.DOC"
End Select
 
Call Merge(stDoc)
Wend
rst.Close
Set rst = Nothing
End Sub

The function is called is below:

Private Function Merge(stDoc As String)Dim wdApp As Object         'Word.Application - Late Binding
Dim wdDoc As Object         'Word.Document - Late Binding
Dim myMerge As Object       'Word.Mailmerge - Late Binding
Dim strPath As String       'Mail merge data source file name
Dim strDocName, strConfirm, strDocPath As String
strDocPath = "S:\test"
strDocName = strDocPath & stDoc
'Open Word application and non-template document
Set wdApp = GetObject("", "Word.Application")
Set wdDoc = wdApp.Documents.Open(strDocName)
'Path for merge database
strPath = CurrentDb.Name
Dim stDBQ As String
Dim stDir As String
stDBQ = "DBQ=" & strPath
stDir = "DefaultDir=" & CurrentProject.Path
'Assign merge source
Set myMerge = wdDoc.MailMerge
'Perform merge
With myMerge
            .OpenDataSource Name:=strPath, _
            ConfirmConversions:=False, _
            ReadOnly:=False, _
            LinkToSource:=True, _
            AddToRecentFiles:=False, _
            Revert:=False, _
            Format:=0, _
            Connection:="Provider=MSDASQL.1;Persist Security Info=True;Extended Properties=""DSN=MS Access Database;stDBQ;stDir;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"";Initial Catalog=S:\test", _
            SQLStatement:="SELECT * FROM '60_Day_ClientletterTBL'", _
            SQLStatement1:="", _
            OpenExclusive:=False, _
            SubType:=1
    'specify that the document is not a merged document so that when you open it in the future it does not try to run the query.
    .MainDocumentType = -1
    .Destination = 0   'SendToNewDocument
    .SuppressBlankLines = True
    .Execute
End With
'close the original mail merge file
wdDoc.MailMerge.MainDocumentType = -1  'NotAMergeDocument
'saves document as PDF
wdApp.ActiveDocument.ExportAsFixedFormat OutputFileName:= _
    "s:\test" & stFileName & ".pdf" _
    , ExportFormat:=wdExportFormatPDF, OpenAfterExport:=True, OptimizeFor:= _
    wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _
    Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
    CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
    BitmapMissingFonts:=True, UseISO19005_1:=False
wdDoc.Save
wdDoc.Close 0
wdApp.Quit False
DoCmd.SetWarnings True
End Function

When the code button is click to execute this letter an error message populates that states:

"Error has occured: The database has been placed in a state by user 'admin" (my pc #) that prevents it from being opened or locked." Once I click this the "select data source" with is populated. This box has a two tabs that have "file data source" and "machine data source"

I have no idea why this would be populating.

I have been stuck on this one for a few days now. Thank you for any help you may provide!