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 Replies


ADVERTISEMENT

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 :: 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 :: 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 :: 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 14 Replies View Related

General :: Large Data Entry - Access Mail Merge

Sep 10, 2013

I have created a rather large data entry form for one of our departments which will be used to run a mail merge document. The merge is run off of a query of the form, rather than the tables themselves...I think that is what I've read is the right thing to do?

Anyways, most of it is working great so far but I've run into an issue where a few of the form fields are combo boxes. In access and in the query the data looks correct, but when you look at it in word (in edit recipient list) and after the merge, it will have a file path name instead.

So a combo box that has "Medical Plans and Visual Plans" in that field in the query, comes over saying "c:Users
ameAppData

Ex: New Hires are provided information about Medical Plans and Visual Plans.
Ex: New Hires are provided information about MeC:UsersbrooksAppData

It always shows the first two letters before putting the filepath name in there.

I have tried doing it multiple ways...a lookup to another table, lookup right in the field itself....get the same results.

View 3 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

General :: Mail Merge Word File (using Data From Access File)

Mar 11, 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 download 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 wish to use VBA in access file to automatically perform the steps that I have outlined.

View 3 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

Mail Merge

Apr 12, 2006

Hi, i have set up a mail merge between a query and a word document. My system is a system that processes transactions from an online cd shop. I have made it so that each cd that is bought by each customer is a different transaction in a transaction table, and each transaction is given an order number to group them. How do i get the mail merge to show all the transactions in an order on the invoice in my word doc because at the moment, even if i add two of the title name fields, they both show the same transaction.

View 4 Replies View Related

Mail Merge

Apr 2, 2007

Anyone have a good online resource for generating a single thank you letter, eg. by way of a mail merge using MS Access 2003 with Word 2003? I know you need to write a form letter first with various fields that conincide with fields in Access. I have done this many times with large mailings but need to know how to write individual letters for thank yous, invites, etc.

Thanks in advance

View 4 Replies View Related

Mail Merge Help!!

Feb 15, 2007

Hi,

I am editing the design of a census, and I would like to be able to mail merge form the query directly to a new document in microsoft word...so you can create the document and then merge to it, is this possible using a macro or VBA? I can't work out how to do it!! The query name is "List of Families" and I want to merge to a blank document, so I can create a different document everytime...as the letters aren't circulars!

Chris

View 1 Replies View Related

Mail Merge - Please Help!

Oct 20, 2004

I just finished creating an access file. Here are the contents:

TABLE Customers - Includes email, contact name, company name, address, zip, city, etc.
TABLE Materials - Includes company name and material. Each record is listed as a company with a material requested. Companies are listed multiple times, as they are unique records because the materials are different.

I have Company name set up as the primary key, and it sets the relationship between the two tables

I have a Report fully working. The first page is a letter with the customers' mailing info at the top.
The second page is a list of all the materials this company ordered for the 2004 year.

The report pulls from a QUERY that comes from the customers table and the materials table.

This report is hundreds of pages long, as we have multiple customers who order many materials each.

My question is "How do I mail merge this report out to the customer?" I do not want to send each customer the whole 500 page report, but only their specific record that they should receive.

I do not care if I have to merge with Word, Outlook, etc. I just dont want to have to send these out individually.

I hope that makes some sense.

PLEASE HELP!

-Preston

View 3 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 6 Replies View Related

Mail Merge

Jan 17, 2008

Im after a bit of advice really. Im looking at building a mail merge facility, i already have one example where you have to select the letter you want to merge the data to.

The problem i have with this at the moment is i have allot of letters that are all basically very similar and the user might pic the wrong one, each letter is slight different and runs by a contract name.

Is there a way that the user can just select the type of letter they want, then select teh contract and then when they click the mail merge button access will know which letter to open.

Just looking for possible ways or examples.

Thanks

View 5 Replies View Related

Mail Merge Question...

Jul 1, 2005

I'd like to set up a mail merge that pulls information from individual records. For example... If I want to do a pre-formated Fax Cover Sheet in Word, and fill the information in with the mail merge.

I don't want this to pull information from all of the records. I want to choose which record to pull the information from. First of all, can this be done? If so, can someone help me out with this? I have no idea where to even begin with this one. I have several mail merge documents set up right now that pull information from queries, but this is something that I'd like to do that is completely different.

Thanks in advance for any help that you can give.

View 3 Replies View Related

Mail Merge Questions

Aug 31, 2005

What is the advantage of using mail merge?
It has been suggested to me that i use mail merge in the current application that im building. This is mainly because my client needs to send a lot of custom letters that will be different each time one is sent.
From what i know about mail merge you can only display information in a document in a flat format ie u couldnt have customer details at the top and then a table of all the orders/ transactions they have carried out below? is this correct?

View 1 Replies View Related







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