Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jan 2013
    Posts
    8

    Unanswered: Mail-merge The Current Record, Office 2000

    I hope someone can help; furthermore, please be aware that my knowledge of the correct terminology may not be 100% accurate but I do hope that you will afford me some understanding because of that.

    I have created some code (linked to a command button on a form ("Reservations2")) which opens a word document (with mergefields) and then performs the intended mail merge: the code is below.

    However, I am in need of some help please with enabling the merge to take place only on the open record of the form; ie, if I am looking at record 23 of "Reservations2" and I press the command button, it merges only the data from record 23.

    I've set a criteria in the query (see attachment) to be:

    [forms]![Reservations2]![Booking Reference]

    ...and this successfully brings up the query (in Datasheet View) with the data from the open record in the form.

    However, when I click on the command button on my form, to perform the mail merge, it brings up a prompt box asking "Enter Parameter Value" for [forms]![Reservations2]![Booking Reference].

    When I click OK on that, it also says that it couldn't merge the main document with the data source as the fields were empty...even though I know the query (in Datasheet view) is showing the data I need to merge.

    I have searched various forums and googled for 3 whole days this week to try and find the solution/s but to no avail; in addition, I do appreciate that many members may wish to suggest the use of a report instead of mail merge; however, I most definitely want to use mail merge as I need to edit the merged document which a report will not afford.

    One website (Click Here) suggested that "The only drawback to this solution is that if the Order form is not currently loaded and you run the query above, the following window will appear." and I wondered if that might be helpful; however, I know the form is loaded (well, it's open...is that the same thing ?) as that "feeds" the query.

    Can anyone figure out what is happening please ?

    Many thanks

    Function MergeIt()
    Dim objWord As Word.Document
    Set objWord = GetObject("H:\enjoy120210\CorrespondenceLetters\Ma ilMerge\BrksDednsEM.doc", "Word.Document")
    ' Make Word visible.
    objWord.Application.Visible = True
    ' Set the mail merge data source as the Reservations.
    objWord.MailMerge.OpenDataSource _
    Name:="H:\enjoy120210\Reservations.mdb", _
    LinkToSource:=True, _
    Connection:="QUERY MasterDataSource", _
    SQLStatement:="SELECT * FROM [MasterDataSource]"
    'Execute the mail merge.
    objWord.MailMerge.Execute
    ' Close BrksDednsEM.doc
    Word.Documents("BrksDednsEM.doc").Close (Word.WdSaveOptions.wdSaveChanges)

    End Function
    Attached Thumbnails Attached Thumbnails QueryDesignView.JPG  

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use:
    Code:
    SQLStatement:="SELECT * FROM [MasterDataSource] WHERE RecordID = 23;"
    Where RecordID is the name of the column that identifies the row you want use in the mail merge process.
    Have a nice day!

  3. #3
    Join Date
    Jan 2013
    Posts
    8
    Thanks Sinndho

    Unfortunately, that won't work as I want the merge to be performed on the open record in the form "Reservations2".

    If I enter "23" as you suggest, it will only ever merge the data from record 23.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by bellevue View Post
    Thanks Sinndho

    Unfortunately, that won't work as I want the merge to be performed on the open record in the form "Reservations2".
    It can work indeed: nothing prevents you from replacing the litteral (23) with a value obtained from the form:
    Code:
    SQLStatement:="SELECT * FROM [MasterDataSource] WHERE RecordID = " & Me.RecordID.Value
    If RecordID is not numeric, use:
    Code:
    SQLStatement:="SELECT * FROM [MasterDataSource] WHERE RecordID = '" & Me.RecordID.Value & "'"
    Have a nice day!

  5. #5
    Join Date
    Jan 2013
    Posts
    8
    Hello Sinndho

    My apologies, I must have misunderstood what you'd suggested; I'm pleased that there may be a solution here.

    Looking at your line of code, I'm a little confused as to what to change and to what.

    If I altered it for my needs, would I change:

    a) Record ID to Booking Reference (this being the field on the form and query)
    b) Me to Reservations2 (the name of the form) or MasterDataSource (the name of the query

    Am I right in thinking that the code performs the "link" to the open record thgus I need to remove the criteria from the query ?

    One final question; the booking reference is a 5 digit number but I note the table field type is text, does that make a difference ?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No need to apologize, I can be too elliptic, sometimes, and asking for clarification does not hurt

    a) If Booking Reference is the name of the column in the table (or query) and the name of the Control (probably TextBox) on the form, you have two similar problems to solve first:
    - In any SQL expression, you need to enclose the name in square brackets :
    Code:
    [Booking Reference]
    - In VBA you have to either use the square brackets with the ! operator:
    Code:
    Me![Booking Reference]
    or you can use a full reference to the control:
    Code:
    Me.Controls("Booking Reference").Value
    Generally speaking, it's better to refrain from using spaces or any non-alphanumeric characters in the names of a database object (Tables, Columns, Forms, Controls, etc), the underscore (_) being an exception (Booking_Reference is ok).

    Me is a reference to the current form (i.e. the form that is associated whith the module where the code is run). If the name of the form is Reservations2, it's full reference is:
    Code:
    Forms!Reservations2
    or (see above with the control):
    Code:
    Forms("Reservations2")
    The explanation is that any open form (except a subform open in a parent form) is member of the Forms collection of the Access.Application object (i.e. the currently open Access database), while any control on a form is a member of the Controls collection of this form.

    c) In the original code you posted, you build a SQL expression that select all rows from a table (or query):
    Code:
    SQLStatement:="SELECT * FROM [MasterDataSource]"
    All rows are selected because there is no WHERE clause in the SQL expression. The solution I proposed adds such a restriction (a criteria) in the form of a WHERE clause to select only one (or, in other circumstances, several) row in the table (or query) that matches the condition (RecordID = 23 in my first example). This does not implies that you need to change anything in the original query: you build a new query (which will return a new data set) on an original query (which returns its own data set).

    d) If the booking reference is defined as a Text type column in the source table, you need to use the second form of the syntax (' before and after the sought value), the fact that the column contains only digits does not change anything to that.
    Have a nice day!

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    off top of head
    I dont think "Mail-merge" can read a query that is pointing to a form

    why not turn the query into a Make Table then get the Mail-merge doc to look at the New Table

    in your function run the Make table just after the DIM objWord

    some like
    Dim objWord As Word.Document
    docmd.setwarning false
    Docmd. runSQL "your_Make_Table"
    docmd.setwarning True
    Set objWord = GetObject("H:\........

    then each time you run the "MergeIt" it will rewrite the data to the table
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    @myle:
    In the code posted by bellevue, the MailMerge object does not try to read a query that is pointing to a form. The form creates a SQL expression that is used by MailMerge to open a data set on an existing table or query in an Access database:
    Code:
    Name:="H:\enjoy120210\Reservations.mdb", _
    LinkToSource:=True, _
    Connection:="QUERY MasterDataSource", _
    SQLStatement:="SELECT * FROM [MasterDataSource]"
    'Execute the mail merge.
    objWord.MailMerge.Execute
    Have a nice day!

  9. #9
    Join Date
    Jan 2013
    Posts
    8

    Talking

    Thanks Sinndho

    Very gracious of you, and I do appreciate your time and patience here.

    I do feel a bit stupid with all of this though and big words like "elliptic" challenge me even further...but I've looked it up now !!

    Right, "Booking Reference" is indeed the name of the column in the query and also the name of the Control (it is a TextBox) on the form.

    So...and treat me gently please if I have this wrong as I had to look it up online...the code would be:

    SQLStatement:="SELECT * FROM [MasterDataSource]"
    WHERE Booking_Reference = Form.Reservations2.Booking_Reference

    Have I got that right ? I'm not confident I have.

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by bellevue View Post
    the code would be:

    SQLStatement:="SELECT * FROM [MasterDataSource]"
    WHERE Booking_Reference = Form.Reservations2.Booking_Reference

    Have I got that right ? I'm not confident I have.
    Not exactly.

    1. Since the name of the column contains a space, it MUST be enclosed in square brackets in the query expression.

    2. Since (I suppose) you did not changed the name of the control (the space between Booking and Reference is still there) you MUST also use square brackets or use an alternative syntax to reference the control too (see my previous post).

    3. The WHERE part must be in the same string as the SELECT part.

    4. As myle pointed out rightly, you cannot have a DIRECT reference to a form in the query. This is because the MailMerge object does not have any possibility to communicate with the form that is open (= that runs) in Access. The query expression must be a string and nothing else when it is passed to the MailMerge object. If the control contains the value 23 when the code is executed, that value (as a litteral) must be in the string expression of the query, not a reference to the control itself.

    5. All in all, it should be something like:
    Code:
    SQLStatement:="SELECT * FROM MasterDataSource WHERE [Booking Reference] = '" & Forms("Reservations2").Controls("Booking Reference").Value & "'"
    or, more simply:
    Code:
    SQLStatement:="SELECT * FROM MasterDataSource WHERE [Booking Reference] = '" & Me![Booking Reference] & "'"
    Have a nice day!

  11. #11
    Join Date
    Jan 2013
    Posts
    8
    Hello Sinndho

    Thanks very much for that.

    Re points 1 and 2, I understand that now; the naming of the control and field was done a long time ago and, probably, my ignorance made me name it as such and not something more user friendly for the "programming" future.

    Re point 3, understood, I (in my last post) just used an exmaple from a website of a SQL WHERE statement and they had laid it out like that; I've now made sure the WHERE bit is all on the same line.

    Re point 4, I think I understand that now, basically, the query can't talk to the form; therefore, the command needs to be in code in the module (VBA Editor).

    I've removed the criteria from the query.

    Attempt A

    I've entered your first suggestion (let's call it "A") into the Module as follows:

    SQLStatement:="SELECT * FROM MasterDataSource WHERE [Booking Reference] = '" & Forms("Reservations2").Controls("Booking Reference").Value & "'"

    When I press the command button (in an open record in the form) it opens up the Mail Merge Main Document, fails to perform the merge and brings up the error message per attachment "A".

    Once I click on "Debug", the whole of the code under "objWord.MailMerge.OpenDataSource" is highlighted in yellow with an arrow pointing to the line of code you suggested.

    Attempt B

    I entered your second suggestion (let's call it "B") into the Module as follows ensuring that I change Me to :

    SQLStatement:="SELECT * FROM MasterDataSource WHERE [Booking Reference] = '" & Forms!Reservations2!Controls[Booking Reference] & "'"

    When I try to exit the module, it brings up an error message as shown in attachment "B" and I can't even get to press the command button.

    I'm sorry to be a pain but where am I going wrong please ?
    Attached Thumbnails Attached Thumbnails A.JPG   B.JPG  

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The second error is easy to understand. The way you try to reference the control is syntactically incorrect. See my second post: either you use the Controls collection of the Form ( Forms("FormName").Controls("Control Name").Value or Me.Controls("Control Name").Value ), or you enclose the control name into square brackets and use the ! operator ( Forms("FormName")![Control Name] or Me![Control Name] ).

    The first error is an error from the MailMerge object in Word, which, at least, proves that the syntax you used to build the SQL expression is correct. I'll try to see if I can find the cause of this error, however I seldom use Word and its mail-merge features even less.

    Perhaps someone with more experience in using Word that I have will be able to provide a quick solution to the problem.

    I don't call it quit, but cannot guarantee that I'll be able to find a solution. I'll keep you informed of what I'll come to, if anything.
    Have a nice day!

  13. #13
    Join Date
    Jan 2013
    Posts
    8
    Hello Sinndho

    Thanks for that.

    I'll keep trying to work out how to enter the correct code for the code in Attempt B and I do appreciate your help with regard to the Attempt A.

    Bless you

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Don't spend too much type with the B solution. The correct syntax in this case is:
    Code:
    SQLStatement:="SELECT * FROM MasterDataSource WHERE [Booking Reference] = '" & Forms!Reservations2![Booking Reference] & "'"
    But you'll end up with the same error code as with the first solution : The MailMerge object of Word will throw an error.

    I tried to set up a sample database (MailMerge.mdb) to test the code and analyse what's wrong. Here's what I came to:

    1. It seems that Word.MailMerge, not only cannot communicate with an open object in Access (that we already knew) but also that the OpenDataSource method of this object fails to open that database in which the code is run. An error message states that the database is locked or in a state that prevents it to be open.

    2. I then tried to split the Access database and stored the table (in my case there was only one named Tbl_DataSource) as well as a copy of the query MasterDataSource (which in my case simply consists in SELECT * FROM Tbl_DataSource;) in a second database named MailMerge_Data.mdb. Then, I deleted the table in MailMerge.mdb and created a linked table with the same name. I then had the classical Front-End/Back-End application schema.

    3. The parameters passed to the OpenDataSource method looked a little strange to me (once again, I'm far from being a Word specialist), and after looking around in MS-Word documentation, I tried different methods, namely different types of connection to the database (Name and Connection parameters of the OpenDataSource method).

    4. I was almost successful with the following code:
    Code:
        Set objWord = GetObject("U:\Access\BrksDednsEM.doc", "Word.Document")
        objWord.Application.Visible = True
        With objWord.MailMerge
            .MainDocumentType = wdFormLetters
            .OpenDataSource Name:="U:\Access\MailMerge_Data.mdb", ReadOnly:=True, _
            Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=U:\Access\MailMerge_Data.mdb;User Id=admin;Password=;", _
            SQLStatement:="SELECT * FROM MasterDataSource WHERE [Booking Reference] = '" & Me![Booking Reference] & "';"
            .Execute
        End With
    When the line invoking the OpenDataSource method is executed, a dialog box pops open in Word and shows the different received parameters, plus several additional ones, in various textbox controls. If I simply press the OK button at the bottom of the dialog box, the code resumes running without any error.

    I have the feeling that one or several parts of the Connection parameters are incomplete or missing, or that another argument of the OpenDataSource is missing too and that a default value is used, which causes the Dialog box to open in MS-Word.

    Unfortunately, I'll be rather busy from tomorrow (Friday) to the end of the week-end and I'm not sure to find the time to go on exploring these hypotheses.

    One thing you could try would consist in splitting your database (as explained here above), then you could declare the Back-End (MailMerge_Data.mdb in my example) as an ODBC data source. The way to open such an ODBC data source is explained in MS-Word documentation and does not seem to be very complex. If I can find some time during the week-end, I'll explore this path too.

    You could also try to post the question in a forum dedicated to MS-Word now that you have the right syntax in Access.
    Have a nice day!

  15. #15
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    hi there, not sure if this would help you, and could involve (from an admin level) an extra task, but personally I would have added an extra field to my table Merge this (yes/no), then create a query that uses the table and has the criteria set to Yes.

    I would then run the mailmerge from that query.

    This would also mean, if you have multiple records you want to add to your mailmerge and not just the current one you can do.

    Also, you may want to set up a way to clear the checkboxes at afterwards.

    dan
    sometimes simple is best.... and i'm just a simple fellow.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •