?
Solved

I want to use a calculated field in a word mail merge, but all the other information is from the table, how can I split it?

Posted on 2005-04-26
6
Medium Priority
?
897 Views
Last Modified: 2012-06-27
I want to use a calculated field in an access form in a mail merge. But, when it merges, it's blank. This is as I am getting the information from the table, not the similarly named calculation field. I need to link the calculated field to input information in the table to merge it to a word doc. Here's the scenario, we are a law firm. We have an access data base to calculate a "demand amount," lets say, $500.00. The statute this case falls under allows for TRIPLE damages. So, in an input form, our data entry people type...$500.00 for the [Demand amount]. This control is bound to the table, so the table field says, "$500.00." I also have a calculated FORM field called, [triple damages amount] which control states =[demand amount]*3. I want this field to place the calculated information into the TABLE for [triple damages], BUT I DO NOT KNOW HOW TO PROGRAM.

The purpose of this field is to create automated lawsuits as these are small nuisance suits under $5,000.00. We have hundreds of them, so the complaints (lawsuits) need to be automated. I created a mail merge for the complaint (which works fantastic, except for calculated fields, all these are blank)...but one of the fields must be the [triple damages amount] in the complaint. A mail merge only works off one table so I can't use multiple forms or queries. How can I get a calculated field to update a table when both controls are named identically.

I basically need to state:
When I input $500 in the form field [form!damages], multiply it by 3 ([form!damages]*3), and place the answer on this form field (form!triple damages)(=[form!damages]*3) but also, load this answer into a table field called triple damages ([table!triple damages]; how do I do this part, where is it entered, and what is the specific command?); once the table is updated, I can use my existing mail merge (which is 37 pages long with multiple fields (about 200 fields throughout the merge and input form, I'd prefer not to rewrite the merges if possible). Please help you guys/gals...and by the way, thank you all in advance for your assistance.
0
Comment
Question by:satzm
  • 3
5 Comments
 
LVL 11

Expert Comment

by:Stephen_Perrett
ID: 13873523
I think what you need to do is create a query based on the table which contains all the fields you need for your mail merge

2 create a calculated field in your query by placing the following

triple damages: [damages]*3

into a blank cell of the Field row when query is viewed in design view.

When you view the query in datasheet view you should see the field triple damages.

save your query with an appropriate name e.g.

qryTripleDamages

Base your mail merge on this query and you will find the triple damages field is available

Steve
0
 

Author Comment

by:satzm
ID: 13873681
Steve: Thank you, but this would necessitate that I rewrite my entire document which merges over 200 fields over dozens of pages for the lawsuit complaints and discovery materials. Is there any other way to do this, using the existing table? I spent days writing the silly thing and making sure all the merges worked properly as so many fields and text are interdependent. Thus, I would like to TRY to keep using the existing TABLE as the basis for the merge.
0
 
LVL 11

Accepted Solution

by:
Stephen_Perrett earned 2000 total points
ID: 13873718
I wouldn't have thought that you would need to write a completely new Word document. Most (all but one of the fields will be identical to what you already have. I will do a quick test and get back to you.

Steve
0
 
LVL 11

Expert Comment

by:Stephen_Perrett
ID: 13873762
It seems OK to me!

Try the following

1. First save a second copy of your mail merge Word Document using Save As

2. Close the document

3. Open the newly created  document

4. When prompted to use data source, click No.

5. Go through the mail merge setup a second time but this time choose the query instead.

All the fields should now be pointing to the query. All you have to do is to insert the calculated fields where you want them.

Steve

0
 
LVL 16

Expert Comment

by:GreymanMSC
ID: 13873763
Some posibilities come to mind.

1. Create an update query which should be run prior to doing the merge to ensure current data.  The best time is probably after you update the table.

The query would be:

   Update [PutYourTableNameHere] set [triple damages]=3*[damages] where not [triple damages]=3*[damages];

2.  The place to run it would be in the Form_AfterUpdate() event.

  Private Sub Form_AfterUodate()
    Dim dB as DAO.Database
    Set dB = CurrentDB()
    dB.Execute "PutTheQuerryNameHere"
    '<-- although I would use something like this to cut out the middleman:
    ' dB.Execute _
        "Update [PutYourTableNameHere] set [tripple damages]=3*[damages] where [PutTheIDFieldHere]=" _
        & Me.[PutTheIDControlHere].Value & ";"
    ' -->
    dB.Close
    Set dB=Nothing
  End Sub

3. OR you could just use a calculated field in the merge document itself.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

840 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