MS Access mail Merge default record

It is possible, but you'd need a lot more lines of code (use OLE automation to open the Word document and search through the mail-merge for the record you've just created).
If you want to output a single document (rather than a mail-merge to multiple reccipients), I'd tend to use a Word template with CustomDocumentProperties instead of Word Mailmerge.

this is the MS reference for the MailMergeDatasource.FindRecord method,
But you'd have to expand your code to use it, by creating object references to the Word application, then the mailmerge document before being able to call the method on that document.

here's a basic example (without much error handling) that you could use to replace your method of following hyperlink to open the Word document.

Sub Mailmerge_FindRecord()
'http://msdn.microsoft.com/en-us/library/office/ff191983%28v=office.15%29.aspx
Dim dsMain As MailMergeDataSource
Dim numRecord As Integer
Dim objWord As Object
Dim objDoc As Object
on error resume next
Set objWord = GetObject(, "Word.application")
if err<>0 then
err.clear
Set objWord = CreateObject(, "Word.application")
on error goto 0
end if
Set objDoc = objWord.Documents.Add("S:\Support Services\test.docx")
 
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = False
Set dsMain = ActiveDocument.MailMerge.DataSource
dsMain.ActiveRecord = wdFirstRecord
If dsMain.FindRecord(FindText:="2576", _
 Field:="RecNo") = True Then
 numRecord = dsMain.ActiveRecord
End If

You'd need to choose the field to search (I've used RecNo) and parameterise the value to search for. 
End Sub

Select allOpen in new window

My  only goal is to merge one document with one record and the word document already has all the field object references.
MY coding is weak and just wanted a simple solutionnto ty the two together. I did try the code above but without success, thank-you

What was the error when you tried the code I posted? Can you identify which line it broke at or post a screenshot of the error, or a description of what went wrong or didn't happen as expected?

Looking back at my last post I can see I edited it badly and included a line of normal text inside my code block in error. That might be the cause of it not working for you.

Looking at your code, you'd need to pass your document filepath string, a unique value from the record you've just created/updated, and the field to search for that value to the sub I posted. I can make it into a function that takes the three parameters if you like.

A functionn would be terrific

Hmmm.. Now I've moved the code to Access, it doesn't work. It looks like a change that MS made to protect the security of mailmerge in Office 2007 means that when a mailmerge document is opened via VBA from a different app, they open without data, so either you'd have to do a registry hack or the data connection has to be rebuilt. It can be done but requires more complexity for you to implement it.

I'm thinking you'd be better off with custom document property solution, but I don't have anything ready to post at the moment, so if any other experts want to jump in, please do.

OK. I got a working function for you. I had some difficulty making it work when Word wasn't already running (FollowHyperlink won't launch the Word.exe if is isn't already running in my Office 2010).

Also, because you're using your database as the mailmerge datasource, you must save all your design changes and save and restart the database before trying the code, because the mailmerge actually opens a second copy of your database behind the scenes.

For this example, I've used a field called ID, but you can call the function with any string values for fieldname and value to find in that field.

Private Sub command106_Click()
    Dim DC2RTR As String
    'First ensure that Word is running - FollowHyperlink won't launch Word
On Error Resume Next
    Set objWord = GetObject(, "Word.Application")
    If Err <> 0 Then
    Set objWord = CreateObject("Word.Application")
    objWord.Visible = True
    'objWord.UserControl = True
    Err.Clear
    End If
On Error GoTo 0
    Set objWord = Nothing
    AppActivate "Microsoft Word"
    
    DC2RTR = "D:\ExampleMailmerge.docx"
    Application.FollowHyperlink DC2RTR
    Start = Timer
    Do Until Timer > Start + 0.2
        DoEvents
    Loop
    retval = Mailmerge_GotoRecord("2", "ID")
    Debug.Print retval
End Sub



Function Mailmerge_GotoRecord(valueToFind As String, FieldName As String) As String
Dim objWord As Object 'Word.Application
Dim objDoc As Object
Dim dsMain As Object

    'On Error GoTo ErrorHandler
Set objWord = GetObject(, "Word.Application")
objWord.Visible = True
Set objDoc = objWord.ActiveDocument
Set dsMain = objDoc.MailMerge.DataSource
dsMain.ActiveRecord = wdFirstRecord
If dsMain.FindRecord(FindText:=valueToFind, _
 Field:=FieldName) = True Then
 Mailmerge_GotoRecord = dsMain.ActiveRecord
 Else
 Mailmerge_GotoRecord = ""
End If
CleanExit:
Exit Function
ErrorHandler:
    MsgBox Err.Description & "(code: " & Err.Number & ")"
    Mailmerge_GotoRecord = ""
    Resume CleanExit
End Function

Select allOpen in new window

Personally, I still prefer using CustomDocProperties for this sort of job, and the mailmerge templates are easy to convert (just show field definitions and change every instance of MERGEFIELD to DOCPROPERTY). However, in this instance I hope the above will suit your purpose.

So very close , i used the same ID field to minimize issues and the same record ID "12" keeps showing up as the merge even though  itry several of the 2000 records as the active record. Appreciate your patience.
I truly didn't realize MS Access didn't have the code consolidation behind it to make this an easier solution.

You may need to create a temp table or query filtered for the specific ID you want to merge.  I prefer using doc properties instead of standard mail merge, but sometimes mail merge is necessary.  To avoid problems with memory-hogging when using mail merge, I like to export the data to be merged to a text file, like this portion of a procedure from the Working with Word sample database for my ebook of the same name:

   'Export merge list to a text file (to avoid having to open the
   'database when the document is opened later on)
   DoCmd.TransferText transfertype:=acExportDelim, _
      TableName:=strTable, _
      FileName:=strTextFile, _
      HasFieldNames:=True
   
   'Open a new merge document based on the selected template
   Set doc = appWord.Documents.Add(strTemplateNameAndPath)
   
   'Set the merge data source to the text file just created,
   'and do the merge
   With doc
      .MailMerge.OpenDataSource Name:=strTextFile, _
         Format:=wdOpenFormatText
      
      If Nz(InStr(strTemplateName, "Label")) > 0 Then
         .MailMerge.MainDocumentType = wdMailingLabels
      Else
         .MailMerge.MainDocumentType = wdFormLetters
      End If
      
      .MailMerge.Destination = wdSendToNewDocument
      .MailMerge.Execute
      
      'Set another Document variable to the newly merged document,
      'to ensure that the correct document is saved
      Set docMerge = appWord.ActiveDocument
      .Close savechanges:=wdDoNotSaveChanges
   End With
   
   docMerge.SaveAs2 strSaveNamePath

Select allOpen in new window

In your mailmerge document, do the records with the other ID values actually show up if you browse through the records manually?
Is the record with ID =12 the first document in the datasource?

I am wondering if the mailmerge is filtering the datasource - this can be difficult to spot from the Word document. The only clue is when you first open the merge doc you get the prompt.

"Opening this document will run the following SQL command:

Select * from tblYourTablename"

If it has a WHERE clause in the statement it means a filter has previously been applied to the merge.

I did have an issue with indexes which i cleared up but the following are two errors
that show up
For my current purposes if the merge went to just the last record of the DB it would be sufficient
Again thanks for your patience
 
fail.doc

Hi, the ODBC error looks like you didn't close and re-open the database after making design changes and the word doc is then trying to open it again as the datasource.

I'm not sure what the VBA invalid procedure call error is caused by. I didn't encounter it. You'd have to step through the code to find the line that causes it.

I'd like to suggest that you look at Helen's solution, as I'm not happy that what I did is ever going to be robust. I could add some further error handling and reporting, but it's not ever going to be a sweet solution. If I were to work further on this it would be to provide the simplest version I can of a custom doc properties version. It performs much better, and doesn't have the limitations on SQL statement complexity that mail-merge does.

doesn't matter if I have Word open or not I get

On Error GoTo 0
    Set objWord = Nothing
 ]AppActivate "Microsoft Word     (this line is highlighted)

Yes i always close word and Access prior to reinitiating a new update.
tried Helen's idea but had some errors and am too tired to troubleshoot  new solution.



I looked for your suggestion on using CustomDocProperties for this sort of job, and the mailmerge templates are easy to convert (just show field definitions and change every instance of MERGEFIELD to DOCPROPERTY)
 Just don't know where to do this and I've used Word Word Perfect fo a lot of years,,,,

Hi GK. A couple of suggestions -
1. you could comment or remove the AppActivate line - it's not strictly necessary, it was just to try to get Word application to the foreground. If I remember correctly it wasn't an issue prior to Windows7. The code would work without it, but you may have to manually switch to Word to OK the "Opening this document will run the following SQL command" prompt.

2. There's no point converting your Word template unless you have the code to push the docproperty values to Word first, but here's the reference for displaying and adding field codes in Word, and here's Helen's page on using custom doc props instead of mailmerge. If I can find the time and energy I'll post a working example by cutting down some code that is used daily in my apps.