We help IT Professionals succeed at work.

MS Access mail Merge default record

gksewell
gksewell asked
on
289 Views
Last Modified: 2014-12-20
Below is button assignment code however after it brings up the document the next step is choosing mail merge and using preview. Preview show some other DB record rather than the the one just filled out fon the DB Form.Can we default the current filled and saved DB record to show up in the Mail Merge?
Private Sub Command106_Click()
Dim DC2RTR As String
DC2RTR = "C:\Users\sewellg\Desktop\New Router Config Tool 2\DC2 VPN Router Config.docx"
Application.FollowHyperlink DC2RTR
End Sub
Comment
Watch Question

SimonPrincipal Analyst
CERTIFIED EXPERT

Commented:
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

Open in new window

Author

Commented:
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
SimonPrincipal Analyst
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
A functionn would be terrific
SimonPrincipal Analyst
CERTIFIED EXPERT

Commented:
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.
SimonPrincipal Analyst
CERTIFIED EXPERT

Commented:
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

Open 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.

Author

Commented:
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.
CERTIFIED EXPERT
Top Expert 2009

Commented:
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

Open in new window

SimonPrincipal Analyst
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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
SimonPrincipal Analyst
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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,,,,
SimonPrincipal Analyst
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.