Access Merge/Report Question

  Ben Avery 14:30 30 Mar 2004


In MS Access I wish to have a push button option to create a form, based on information contained within the database tables. The forms already exist in MS word format and the problems I have are as follows:

1) How do you link a MS Word document/template to Access and be able to select fields for the information to automatically fill in?

2) How do I create a push button on a form in the database to create this new merged form?

Any help greatly appreciated!


  Sir Radfordin 14:49 30 Mar 2004

Just need to get my head around something?!

When you say a 'create a form' do you mean you want a paper form in Word which uses data from the database?

If so then you can create your form in Word and use the Mail Merge Wizard to make your access database as your source.

To link the two you will need to use VBA to call the Word document from within Acess when the button is clicked.

You need to create a query that will select the information you want to be merged into the from. If you don't want to merge all the records every time (so only a specific record) then when you create your query on the primary key create a criteria that it has to be equal to the value on the Access form at the time the button is pressed. It is that query that you then want to use as the source for your Word mailmerge.

For some reason I've always found it simpler to go through Excel so you pass the data from Access to an Excel sheet and then use the Excel sheet as the data source for the Word mailmerge.

Never worked out if there was any way of using VBA to complete the merge so always had it so the user had to click the final 'merge' button once Word opened up.

Hmmm not a very good explination sorry! This is also just the way I solved the problem so it may not be the best or quickest! Post back if you need any snipets of code or more explination.

  Ben Avery 15:12 30 Mar 2004

"'Sir', You are both a gentleman and a scholar!"

That's along the lines of what I want. It is a database of all candidates who are on our records (hence the lack of using Word mail merge - I can do that ok). What I have is this:

* An Access form which is based on the main table of the database. This form contains all relevent information on each candidate, name address etc etc. It has a PRIMARY KEY based on a "candidate number" as this is different for each person.

On this form I also have space for the said buttons.

In another folder I have a set of company forms. These forms contain information about the candidates, one, which we will call "front sheet" contains name, job discipline (type) reference numbers, location, telephone number etc. All this information is on the database form.

Here's what i need (I'll try to explain a little better!):

I would like to link this Word sheet INTO the database in such a way that when I have up my Access form (hopefully to soon be complete with a shiny new button!) I can click on my "Print Front Sheet" button and the computer will generate a new "front sheet" for JUST that candidate based on the information contained in a query (from the main form/table) which is then merged into the word document (phew!)

Does that make it any more understandable or am I doing my usual waffling again?! :)


  Sir Radfordin 17:02 30 Mar 2004

What kind of files are the forms in the "another folder I have a set of company forms"??

If I'm right in my thinking that you want to produce printed forms could you refer to them as PFORM so I know the difference between those and the 'form' you use for data entry in access (little brain you see!)

The trick is to create the right query so only the data for the right candidate is selected. If you could give me details of tables/forms/queries you already have I may be able to work that out.

Even better if you have a blank version of the DB that you could email I'll look at that.

I think your making sense but I can't wake my brain up.

  Ben Avery 23:25 30 Mar 2004

It's my mindless babble that is confusing you - nothing new there eh?! I think I could garble for Britain if the circumstances called for it..., see what I mean!

A more detailed explaination with shiny pictures and as little hyperbole, methaphorical talk and general kak as I can:


Document Templates (.dot & .doc) which are our company's standard forms I want to print. From here will be referred to as "PFORM"


The Access Database form "AFORM" contains the following fields:

1. Title (Mr, Mrs etc)

2. Forename

3. Surname

4. Street Address (line 1)

5. Street Address (line 2)

6. Town

7. County

8. Postcode

9. Telephone No.

10. Registration Number (PRIMARY KEY) [Unique to each record]

11. Date Registered

12. Date of Update

13. Coding (similar to a standard of work from 1-5)

14. Date of Birth (In YY Format)

15. Discipline 1 (Job type eg. "19" - Electrical)

16. Discipline 2 (If applicable)

17. Discipline 3 (If applicable)

18. Seniority 1 (Job Position eg. Manager, Technician etc)

19. Seniority 2. (If applicable)

20. Seniority 3. (If applicable)


I would then create a query to pull out the following fields from the AFORM:

Forename, Surname, Registration No., Discipline 1-3, Seniority 1-3, Telephone No., Date of Birth, Coding, and a location from the Town & County fields.

This is all the information which would need to be filled in, in the spaces on our PFORM.

Then, on the AFORM, I would want a button, which, when pressed, prints off a copy of the PFORM for the SELECTED CANDIDATE ONLY (generated by the PRIMARY KEY - Registration No.) with all necessary fields filled in automatically.

I think that explains it a little better. My brain seems to work better when I'm laying in bed than it does sitting at an office desk. I think I wanna work from home! :)


  Sir Radfordin 00:35 31 Mar 2004

I know I wanna work from home!

I'll sleep on it and be back in the morning

  Sir Radfordin 01:07 31 Mar 2004

Ok...this counts as morning!

I'll assume that you have a single table that is the source for AFORM. If this isn't the case it'll need changing.

In SQL terms you would want a query that is:

Select Forename, Surname, Registration No., Discipline 1-3, Seniority 1-3, Telephone No., Date of Birth, Coding, and a location from the Town & County fields from AFORM where Registration No=[Form]![AFORM].Registration Number

That where clause may not quite be formatted correctly but is the right idea!

The simplest thing would be to then create an Access Report based on that query but they are a nightmare to get 'good layout' when it has a lot on it.

When you have created that query open up one of your PFORM documents and start the mail merge wizard. You can then use that query as the data source.

This is where I'm then not sure of what you need to do. For some reason (as I said above) I only seem to have got it to work when using Excel as the middle man.

You create a button on your form. In theory using VBA to open your mailmerge version of PFORM should then allow you to do what you want. However I think it doesn't work in practice. So you need to (using VBA) run the query and export the result to and excel file. Then open up the PFROM document (having previously changed the mailmerge source from the Access query to the Excel file access will create).

This may not be the most elegent solution to the problem but it normally works.

If you can get the query bit sorted your a long way to getting it solved. Post back if you need any of the VBA link bits or more help with the query.

A tip for when doing the where clause (Criteria in Access) open AFORM in design mode and then right click the criteria field and go to build. You have the option to view loaded forms and so should be able to find the matching field (Reg No) to create the correct synatx.

It looks more complicated than it really is...honest!

  Sir Radfordin 10:49 31 Mar 2004

Ok, have tested it now and worked out why I used Excel as the middle man - couldn't work out how to use an Access query as a data source in Access.

This is the VBA code for your button:

Private Sub Command40_Click()
'Thats the start and the name of the button

DoCmd.SetWarnings False
'Stops any warning messages

DoCmd.OutputTo acOutputQuery, "qr_AFORM", acFormatXLS, "g:\aform.xls"
'Outputs the result of the query to a file on the g: drive called aform.xls which is then the data source you have set up for your word mail merge

'closes excel

Dim stappname As String
'blanks the string that is next

stappname = "C:\program files\microsoft office\office10\winword.exe g:\benletter.doc /mmerge"
'This calls Word - if you have office 2000 you dont need the 10 if 2003 then you need 11 and then word shouldve been installed in the default place. the g:\benletter.doc is the path to the file you have created that you want to merge into

Call Shell(stappname, 1)

DoCmd.SetWarnings True
'Puts the warning messages back on

End Sub
'Thats the end

  Sir Radfordin 10:51 31 Mar 2004

The query used in that bit is "qr_AFORM" so if you call yours something different you need to change it.

When you create your query you need this to be the criteria:


Where [fr_AFORM] is the name of the form that the registration number is on and which your button is on.

You will need to change it slightly if you have any sub forms floating about the place.

Hope that helps and makes sense.

  Sir Radfordin 10:54 31 Mar 2004

..worked out how you use a Access query as the data source for mail merge (knew I knew it really) but I don't think it works in this context because you need to run the query from the form.

  Ben Avery 12:25 31 Mar 2004

Have set up the button and added the VB script as you said. Have also set up the query with the needed fields but don't know if I did it correctly.

When I setup the query I could only take information from a table or a query so had to select the fields from the original TABLE where the AFORM gets it's information from.

In the criteria box I wrote the following:


When I run the query I get a popup asking for a reg no. If I input for example "2", I get the record up for the person who has a registration number of "2". I guess that's how it should work right?

What do I do next? I've saved the query under the same name as yours and have a blank xls file also named the same.

When I click on the button on the AFORM however I get a popup saying:

You didn't specify search criteria with a FindRecord action

I'm lost!


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

AMD Radeon Adrenalin release date, new features, compatible graphics cards

Print designer Kelly Anna on confident mark making & modern femininity

New iMac Pro release date, UK price & specs rumours

Comment créer, utiliser et supprimer son compte Facebook ?