Solved

MS Access mail Merge default record

Posted on 2014-12-15
14
167 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
0
Comment
Question by:gksewell
  • 7
  • 5
  • 2
14 Comments
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40500245
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

0
 

Author Comment

by:gksewell
ID: 40500579
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
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40500798
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.
0
 

Author Comment

by:gksewell
ID: 40500822
A functionn would be terrific
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40500942
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.
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40501726
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.
0
 

Author Comment

by:gksewell
ID: 40502497
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.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40502597
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

0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40502617
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.
0
 

Author Comment

by:gksewell
ID: 40503164
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
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40503193
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.
0
 

Author Comment

by:gksewell
ID: 40503418
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,,,,
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40503696
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.
0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 500 total points
ID: 40504572
See my old (but still workable) Four Ways to Merge to Word code sample:

http://www.helenfeddema.com/Files/code24.zip

or my newer (covers office versions up to 2013) Working with Word ebook from Office Watch.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now