Modules & VBA :: Recreate Old-style Parameter Query Mail Merge

Sep 15, 2014

I am trying to create VBA code (I have very little experience of VBA) to recreate a parameter query mail merge, which Access 2013 cannot achieve with the controls provided.I think that I need to assign a variable to the value of a control on my form then assign that variable to the relevant criteria section of my query.

Code:
DIM RefNo as String
DIM IDNo as String
RefNo = Me.[Reference Number].Value
IDNo = Me.Text582.Value
DoCmd.OpenQuery SORTER
How do you paste into criteria?

From there I would like to do a Word Mail Merge by having VBA open a document in Word, then activate the Merge to a New Document instruction.

View Replies


ADVERTISEMENT

Parameter Query With Mail Merge

Aug 19, 2005

I have a database on a machine with office xp. I store the database in C:Database.
I can run a query then link the results of that query to a template document I have setup via mail merge. The document then opens and fills out the fields based on the query results.
I transferred the database onto another machine with office 2003 and put it in exactly the same directory c:database. and when i try to link the query to word, word just comes up saying it cant find the source.
I have gone around various machines at work and the merge runs fine on all machines with office xp and not with 2003.

Any Ideas?

View 1 Replies View Related

Mail Merge With A Parameter Query

Jul 5, 2012

I am trying to put a button on a form that will run a mail merge to populate a Word (2007) document with information from the single record in the form. I would think this would be easy to do, but after working on it, and searching the internet, I'm finding it is not. Normally I would simply use an Access Report to do this, but the document is about 7 pages long (a contract), and people want to have the ability to modify the result.

What I have done so far:

-I've created a form that displays a single record from my Customers table
-I've created a query that pulls the Customer fields I need, and is filtered on the Customer ID that is active in the form. When I open this query by itself, it pulls back the one record I want.
-I then opened both the form and query, and then run through the External Data - Mail Merge wizard.

Problems I've run into:

1) Initially when I tried to select my query in the Mail Merge Wizard, it wouldn't show. I then found tips to prompt for the data connection type when the file is opened, and to set it to MS Access with DDE. This allowed me to see the query, and seemed to work OK when I added the fields I needed to the Word document. But then when I tried to rerun the Word doc, I would get a connection error. If I run through the connection setup process again, it seems to work, but again when I rerun it later, I get the connection error. For simplicity I have put the Access DB and Word document on my desktop. Eventually these will go on the network.

At this point I have a button on my form, and it successfully opens the Word document, but the "merged" fields just show the record that I had saved the document with, so there is no true merging going on. If I open the Access DB Form & query and then open the Word document directly, I get the error "Word could not re-establish a DDE connection to Microsoft Access to complete the current task".

View 2 Replies View Related

Creating Conditional Mail Merge From Parameter Query

Apr 2, 2012

I use an access db that stores our customer info on a mssql backend. I need to be able to create a mail merge based on a customers renewal date. Now I do know how to set parameters in a query but is there a way I can use a access report to enter a month and day they are set to renew then have it create the mail merge based on their info?

The Layout is fairly simple just a renewal notice with what they have on their plan.

View 6 Replies View Related

Queries :: Mail Merge - Enter Parameter Value

Jul 13, 2013

I am trying to mail merge from word 2013 to a query in access 2013. I initially had problems displaying the query until I came across a solution regarding connecting via DDE. I can now link to the query but it then asks me to 'enter parameter values' from the query. I have already run these values into the query via a form which ends up in a report. (incidentally I have left the report open). If I manually enter these parameters it works fine.

View 6 Replies View Related

Modules & VBA :: One To Many Mail Merge

Sep 8, 2014

Any simple working mail merge application with a one to many field filling into the word document. I have no problem with a one to one, but if the subform has 2 or more associated records related to the main form, I cannot pass all the fields. Build a recordset loop or concatenated script so I can learn from it and add it to an existing project.

Example:
main form: CustomerID, Customer, Address
subform: ProductID, Product, Cost, fk_CustomerID

Sample data:
main form: 1, James Smith, 118 Clover Lane
subform entry 1: 4, Small Shipping Boxes, 2.00, 1
subform entry 2: 5, packing tape, 1.75, 1
subform entry 3: 3, Large Shipping Boxes, 3.00, 1

View 2 Replies View Related

Modules & VBA :: Mail Merge From Report?

Aug 7, 2013

I have a report in my database with a button called "MailMerge".

My aim is that when clicked, this will open a word document, that has already been created, but automatically complete the merge based on the data in a table.

The catches:

1) I need to be able to ask the user the content of the word document from within access, and automatically populate it

2) I need to be able to ask the user who has written the word document and automatically populate it

3) I need to use a document which I have already created, because of various reasons, but obviously the merge fields can be updated.

Currently, I am trying to append the results of the query into a table, TBL_Merge and then use code to both ask the questions above, and then complete the merge.

View 1 Replies View Related

Modules & VBA :: Mail Merge With Attachment?

Jun 19, 2014

I'm wondering if it's possible to set up an automatic Outlook mail merge with attachments from Access. We have several account managers, and each one receives a different version of the same report - filtered to his/her accounts. For now, I have set it up for the user to select the account managers and hit "Print Reports" to save each individual report into a destination folder. The user then uses an email template, attaches the report, and sends the email to an account manager. She has to send this email separately to each account manager.

View 3 Replies View Related

Modules & VBA :: Mail Merge From Multiple Users?

Jun 11, 2014

I know this is possible (e.g. opendatasource) but search as I might I can't get the exact code.

Scenario: Multiple front ends, in different folders, linked to single back end.

When mail merging, a temp table is created in FE - thereby different source for each user.

The Word doct is linked to a source (via Mail Merge) and I want to change that source according to the logon of the user, which is held on an open form.

Ideally like:
Case Mary use C: Db1 emptable
Case Tom use C:Db2 emptable

View 4 Replies View Related

Modules & VBA :: Mail Merge For Single Record

Feb 18, 2015

I have an access .adp project which is linked to a sql server tables (2008 r2), I have a button on an access form which I click and successfully gets customer details based on a query. What I wanted to know is when I am viewing a specific record i.e. details for Patient Number X, when I click on a mail merge button from their record form can the mail merge document only open with their details not all customers?

I have tried to delve into vba and modules to acheive this but I am a newbie.

View 4 Replies View Related

Modules & VBA :: Mail Merge With Variable File Name

Aug 14, 2013

I might be able to get "super easy mail merge" to work, but the problem is i want clicking the "merge" button to launch a file chooser box- so i can navigate to the word doc i want to use for the merge (destination doc will change with the currently selected record- i'm not merging a single record, however).

Basically i want to do exactly what right clicking on a table/query, then choosing "export" then choosing "merge with word" does (access 2007).

Can't do as a report because we may have "complex" letters- with graphics and/or tables.

why is something that is a simple right-click menu option so difficult to automate??

View 4 Replies View Related

Modules & VBA :: Creating Mail Merge Letter

Aug 20, 2013

I basically have a form which the record source is either from a table or query and all I would like to do is to have a button which when pressed will open up a previously created word template and insert all relevant fields into the template. First Name, Last Name, Address etc.

View 1 Replies View Related

Modules & VBA :: Mail Merge And Create Individual Files

Jan 11, 2014

I am looking to perform a mail merge from access to Publisher. I have linked everything together and got everything looking the way that I want it to look however...

What I want to do is when I perform the mail merge I would like publisher to save a copy of each individual record from the database as a specific file based on one field of the mail merge.

I will try to clarify...

One of the mail merge fields is ClientAccountNumber I would like the merge to save a copy of the publisher file into c:mailmerge as a PDF including the year 2014.

So this file(s)for example would look like
C:mailmerge123456 - 2014.pdf C:mailmerge123457 - 2014.pdf etc...

Code:
Sub MailMerge()
'
' Macro1 Macro
Dim TempDocCreate
Dim FileNameTemp As MailMergeDataField
Set FileNameTemp = Application.ActiveDocument.MailMerge.DataSource.DataFields.Item("Box 22 Rcp Acct No")
With ActiveDocument.MailMerge.DataSource
Application.ActiveDocument.ExportAsFixedFormat pbFixedFormatTypePDF, Filename:= _
"L:Operations DatabaseProjects1042PublisherPDF2011 Merge" & "FileNameTemp" & " - 2011" & ".pdf"
End With

View 6 Replies View Related

Modules & VBA :: Mail Merge In MS Word (using Data From MS Access)

Mar 9, 2014

I often create contract using mail merge. I have an access file that I want to use as data source for word file. But it does not automatically.

Please see the attached file !

If there are 1 customer and 1 property, I do not need to do anything. Conversely, if there are many customers and many properties, I take time to manipulate.

Firstly, I open the word file. I have to copy and paste paragraphs that I want. Highlight of the original paragraphs is blue.

Secondly, I click 'Insert Word Field' -> select 'Next Record'.

In short, I want to use VBA in access file to automatically perform the steps that I have outlined.

View 1 Replies View Related

Modules & VBA :: Losing Decimal Place In Mail Merge

Sep 4, 2013

My DB is merge in a currency field set to 2 decimal places into word. It's doing this by declaring the fields as variable, calling an instance of work then dropping the values into the bookmarks. All works fine....

When a value is 360.64 is fine, but when it's something that ends with a 0 (360.60) - is loses the 0 giving me 360.6.

View 2 Replies View Related

Modules & VBA :: Fine Calculation - Supply Data For Mail Merge

Mar 18, 2014

I am currently working o a Library Management System. most of the work is done but there remains two major problems.

I want to calculate the fines that a member will have automatically on a form. i have a basic pseudocose which is

IF Loan.returned=0 AND ReturnDate<Today{note- i.e. date()Today(), etc.}
THEN Loan.fine_BDT=20*(Today-Loan.ReturnDate)
Note2- The form has the following fields from a query which is connected to table loan-
LoanID
LoanDate
ItemID
MemberID
ReturnDate
DateReturned
Returned(Yes/no)

2. The other one is with the Mail merge. i Want to use a query to supply the data for a mail merge. However, since the database id encrypted with a password it cannot be reached.

I am currently using Access 2007.

View 7 Replies View Related

Modules & VBA :: Mail Merge - Database Placed In A State By User That Prevents It From Being Opened Or Locked

Jun 19, 2014

I have the following code which errors on line

Code : .OpenDataSource Name:=CurrentProject.FullName, SQLStatement:="SELECT * FROM [qryMailMerge]"

The error (Error has occurred: The database has been placed in a state by user 'Admin' on machine 'W74XXXXXX' that prevents it from being opened or locked) appears in the Word document.

Code:
Public Sub CreateWordToPDF(strWordFile As String, strPDFFile As String, strSQL As String)
'Call CreateWordToPDF("C:Doc1.doc", "C:Doc1.pdf", "SELECT * FROM [qryMailMerge]")

Dim objWord As Word.Application
Dim docWord As Word.Document
'Open MS Word using early binding.
Set objWord = New Word.Application
Set docWord = objWord.Documents.Open(strWordFile)

[Code]......

View 8 Replies View Related

Mail Merge Of Query Results

Sep 13, 2006

I have four tables, each containing name of one family member. Dad, Mom, son, and other. One of the four called us, and in many instances, they ask that we send a letter to them. Sometimes, they say, send the letter to Mom, or send it to dad, or sis. We are creating an automated output query to print one of two letters, to go to the appropriate person. Depending on which person is selected as the one to receive mail, we need to merge out into Word that person's data, which may be in any of the four tables, but is the only one bearing an indicator (checkmark, etc).

Don't know how to word the query to look thru the data on that family (from four tables) and select the indicated one, output the address info.

View 2 Replies View Related

Mail Merge Query Results

Sep 15, 2006

I have a form on which users select fields to include in a query and can select their parameters. The query itself is then created using an SQL statement in the VBA behind the form.

I want to be able to use the records which this query selects through mail merge. With a standard query, this is easy because from within Word I can choose the query through the mail merge settings. However, since the query doesn't exist in this case (it is created every time it's run using VBA) I can't figure out how to do it.

Ideally, I want the form to come up during the mail merge process so that the user can specify which records to use, but I can't select a form as a mail merge source.

Can anyone help?

Gary

View 14 Replies View Related

Mail Merge Query Problem

Apr 16, 2008

Hello,

I wonder if anyone can help I have been using Access Queries in order to create merge letters in Word for years and now we have upgraded to Office 2003 everything seems to be going wrong. This is what I do:

1. Open a query in design view enter the relevant text in the appropriate field in order to select the records I want from the table, then I run it and the correct entries are there.

2. Open a standard letter in Word which was created and has been linked to the above mentioned query years ago.

This is where the problem starts in the past the letter opens up and the correct info is displayed so I go ahead and print. Now what is happening rather then linking to the query it is either displaying the last record that was used or nothing at all and in the record number box it says 0 when I know that there are several selected in the query.

I have tried re doing the merge setup and nothing happens the only way round it I have found is to merge it with the table rather then the query then using the Mail Merge Recipients Box you have to go through and reselect the records you want which is a pain as there are 945 records in the table.

Any ideas/help would be appreciated.

Thanks.

View 1 Replies View Related

Mail Merge Using Access Query

Jan 3, 2005

Happy New Year everyone-- I am trying to use an Access query to create mailing labels- Is there anyway to keep them alphabetized or sorted?

Thanks!

View 3 Replies View Related

Queries :: Union Query For Mail Merge?

Jun 3, 2015

it's possible to use a union query as a mail merge? I haven't found anything that says I can't do it, but I'm not getting my merge to complete, and when I switch to a plain query (and not changing anything else) my merge is successful, so I'm thinking there might be a limitation.

View 14 Replies View Related

Queries :: Query That Populates A Report Linked To Mail Merge

Aug 30, 2013

I have a query that populates a report linked to a mail merge. There is an IFF statement in it that checks to see if a condition is meet. If that condition is meet some standard text results. Is it possible to put a disclaimer with the standard text.For example,

Code:
IIF ([Results]="Negative","Nothing detected" (the disclamer would go here)

I am not certain the correct syntax to use to insert "not all regions were tested".

View 1 Replies View Related

Using Cc: With Mail Merge

Jul 20, 2005

When applicable, I have letters in Word already set up to show the cc: information at the bottom. However, Management needs a statement added to the bottom of the letters that ONLY show the cc:

I am having a slight problem trying to figure out how to make this statement show up only on certain letters.

Does anyone have any thoughts that could help me? I'd appreciate it very much.

View 2 Replies View Related

Mail Merge

Dec 28, 2005

I am totally new to Access and this is my first database.

I have a database for volunteers in which table A is basically a calendar and table B a list of volunteers with their details.

What I want to do is construct a report that will allow me to produce a mail merge letter such that:

1. One volunteer, with his/her information, is selected from table B and inserted into a mail merge letter. (The volunteer selected will change from letter to letter).

2 Certain days are selected from table A and added to the mail merge letter. This information will be the same in all letter.

I have posted a separate thread asking how I can make the selection of days in (2) above so you can assume that I know how to make the selection within table B.

How do I proceed?

If anyone knows a good example of this type of report then i will be extremely grateful.

View 3 Replies View Related

Mail Merge

Jan 23, 2006

I have a table that has, among others, Address1, Address2 and Address3. If Address2 andor Address3 are empty for a particular record, I get blank lines in the Word doc. How do I prevent these blank lines?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved