Using Access to send data to a word document

[Colin Foster]

Hi Group.
Using Office XP
I've been trying to resolve an issue for a while now, which I've left alone
for a few weeks in the hope that inspiration will strike! As it hasn't, I've
come back here!!

I have a database which sends data to a word document by making use of the
bookmark features of Word.

Part of the current code that is attached to a button within my form is as
follows...

.ActiveDocument.Bookmarks("Architect").Select
.Selection.Text = (CStr(Me.CustName))
.ActiveDocument.Bookmarks("MainContractor").Select
.Selection.Text = (CStr(Me.CustName))

In my tables, I have the following set up...

CustName Type
John Doe Architect
Jane Doe MainContractor
In my Word Document there are 2 bookmarks, one named Architect, the other
MainContractor. The data to be put in here is the CustName field.

However... by using the code shown above, then both bookmarks are completed
with the details from the record that I'm looking at within the form (i.e.
if I'm looking at "John Doe", then he shows as both Architect and Main
Contractor. What I need is that when I click the button, for "John Doe's"
details to appear in the Word Bookmark as the "Architect" & "Jane Doe" as
the MainContractor.

In a previous discussion on this issue, Chris Nebinger suggested the
following code...

..ActiveDocument.Bookmarks(Me.Controls("Type")).Text = CStr(me.CustName)

If I use this, then if my form is showing the details for "John Doe", then
only the "Architect" bookmark is completed, if I'm looking at "Jane Doe"
when I click the button, then only the "MainContractor" bookmark is
completed.

I've tried to make a "For...Next" and "If...Else" statement work, but with
exceptional lack of success! Hence the return to this Group.

Any suggestions will be gratefully taken!!

Regards

Colin Foster

 

[John Nurick]

Hi Colin,

Will there always be just one Architect and just one MainContractor in
your table? If not, what determines which Architect and which
MainContractor's names should be poked into the Word document?

If the name is displayed in a control in your form, you can use
something like

..ActiveDocument.Bookmarks("Architect").Range.Text = _
Me.CustName.Value

Otherwise you have to extract the name from the relevant record from
your table. Often the simplest way to do this is with DLookup().

 

[Colin Foster]

Hi John,
Thanks for the response. In answer to your questions...

There will be more than one of each within the table of "contacts", however,
there should only ever one of each associated with the job in question that
the word document is "dealing with"

The name is displayed in the "CustName" control on the form; The "Type"
(i.e. Architecht or MainContractor) is another control on the form.

In simple (non-database!) terms, what I need to be able to do is click on
the button to open up the word document and plug the Architect's name in the
Architect space on the document and the MainContractor in the
MainContractors box, each of which are defined by a Bookmark. It sounds so
simple, and I'm almost there, but it's the last step that is frustrating
me!!

Once again, thanks for your help.
Regards
Colin

 

[John Nurick]

Hi Colin,

I'd do this by setting up a query - let's call it qryJobsContractors -
that returns a list of jobs, each with its associated Architect and
MainContractor, i.e. three fields
JobID
Architect
MainContractor

Then - assuming that there's a numeric JobId displayed in a textbox
txtJobId on the form - I'd use statements like this to poke the
information into the Word bookmarks. (I'm working from memory here, so
you'll probably need to tweak it.)

.ActiveDocument.Bookmarks("Architect").Range.Text = _
DLookup("Architect", "qryJobsContractors", _
"JobID = " & Me.txtJobID.Value)

.ActiveDocument.Bookmarks("MainContractor").Range.Text = _
DLookup("MainContractor", "qryJobsContractors", _
"JobID = " & Me.txtJobID.Value)

Hi John,
Thanks for the response. In answer to your questions...

There will be more than one of each within the table of "contacts", however,
there should only ever one of each associated with the job in question that
the word document is "dealing with"

The name is displayed in the "CustName" control on the form; The "Type"
(i.e. Architecht or MainContractor) is another control on the form.

In simple (non-database!) terms, what I need to be able to do is click on
the button to open up the word document and plug the Architect's name in the
Architect space on the document and the MainContractor in the
MainContractors box, each of which are defined by a Bookmark. It sounds so
simple, and I'm almost there, but it's the last step that is frustrating
me!!

Once again, thanks for your help.
Regards
Colin

 

[Colin Foster]

Hi John,
Thanks for the response. Been having a play with the code that you sent, but
I now get an error: "2001 You canceled the previous operation"

The code that I'm using is...

* .ActiveDocument.Bookmarks("ProjectNumber").Select
* .Selection.Text = (CStr(Forms!FrmProjects.ProjectNumber))
* .ActiveDocument.Bookmarks("ProjectName").Select
*.Selection.Text = (CStr(Forms!FrmProjects.ProjectName))
*.ActiveDocument.Bookmarks("SiteName").Select
*.Selection.Text = (CStr(Forms!FrmProjects.SiteName))
.ActiveDocument.Bookmarks("Architect").Range.Text _
= DLookup("Architect", "qryArchMainC", _
"projectID = " & Forms!FrmProjects.ProjectNumber)
.ActiveDocument.Bookmarks("MainContractor").Range.Text _
= DLookup("MainContractor", "qryArchMainC", _
"projectID = " & Forms!FrmProjects.ProjectNumber)

The code prefixed with the * seems to work fine.

I have set up a query (qryArchMainC) wich has the following fields...

ProjectID
CustName
Type
The results that I have at the moment are (hope this comes through OK!)

QryArchMainC ProjectID CustName Type
11 Architects R us Architect
11 ACME Building Company MainContractor
12 Second Architect Architect
12 Second Main Contractor MainContractor


Any suggestions? I've tried looking on the MS Knowledge base for this error
message, but not found it!

Regards
Colin Foster

Hi Colin,

I'd do this by setting up a query - let's call it qryJobsContractors -
that returns a list of jobs, each with its associated Architect and
MainContractor, i.e. three fields
JobID
Architect
MainContractor

Then - assuming that there's a numeric JobId displayed in a textbox
txtJobId on the form - I'd use statements like this to poke the
information into the Word bookmarks. (I'm working from memory here, so
you'll probably need to tweak it.)

.ActiveDocument.Bookmarks("Architect").Range.Text = _
DLookup("Architect", "qryJobsContractors", _
"JobID = " & Me.txtJobID.Value)

.ActiveDocument.Bookmarks("MainContractor").Range.Text = _
DLookup("MainContractor", "qryJobsContractors", _
"JobID = " & Me.txtJobID.Value)

 

[John Nurick]

Hi Colin,

With qryArchMainC as you have it, the DLookup() calls will be more like
this, to pull out the Architect's name from one record:

DLookup("CustName", "qryArchMainC", _
"(projectID = " & Forms!FrmProjects.ProjectNumber _
") AND (Type = ""Architect"")")

and a slightly different one to get the MainContractor's name from
another. I'll leave it to you to work out why.

Hi John,
Thanks for the response. Been having a play with the code that you sent, but
I now get an error: "2001 You canceled the previous operation"

The code that I'm using is...

* .ActiveDocument.Bookmarks("ProjectNumber").Select
* .Selection.Text = (CStr(Forms!FrmProjects.ProjectNumber))
* .ActiveDocument.Bookmarks("ProjectName").Select
*.Selection.Text = (CStr(Forms!FrmProjects.ProjectName))
*.ActiveDocument.Bookmarks("SiteName").Select
*.Selection.Text = (CStr(Forms!FrmProjects.SiteName))
.ActiveDocument.Bookmarks("Architect").Range.Text _
= DLookup("Architect", "qryArchMainC", _
"projectID = " & Forms!FrmProjects.ProjectNumber)
.ActiveDocument.Bookmarks("MainContractor").Range.Text _
= DLookup("MainContractor", "qryArchMainC", _
"projectID = " & Forms!FrmProjects.ProjectNumber)

The code prefixed with the * seems to work fine.

I have set up a query (qryArchMainC) wich has the following fields...

ProjectID
CustName
Type
The results that I have at the moment are (hope this comes through OK!)

QryArchMainC ProjectID CustName Type
11 Architects R us Architect
11 ACME Building Company MainContractor
12 Second Architect Architect
12 Second Main Contractor MainContractor


Any suggestions? I've tried looking on the MS Knowledge base for this error
message, but not found it!

Regards
Colin Foster

 

[Colin Foster]

Frustrated of England replies!!

Hi John,
I guess that the "slightly different" one is replacing the final "architect"
with "MainContractor"...however, when I put in the "original" code, I'm
getting an error of "Compile Error: Expected: End of Statement" with the
final line of code: ") AND (Type = ""Architect"")") highlighted. I've
tried the closing ) in different places (and even added one, but to no
avail. I realise that I'm doing something stupid, but I can't spot what!!

The full code is...

With oword ' Make the application visible.
.Visible = True
' Open the document.
.Documents.Open ("C:\Documents and Settings\Colin\My
Documents\Universal Spreadsheets\" & _
"Glass Block Technology\Letters\S & F
enquiry db.doc")
' Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("ProjectNumber").Select
.Selection.Text = (CStr(Forms!FrmProjects.ProjectNumber))
.ActiveDocument.Bookmarks("ProjectName").Select
.Selection.Text = (CStr(Forms!FrmProjects.ProjectName))
.ActiveDocument.Bookmarks("SiteName").Select
.Selection.Text = (CStr(Forms!FrmProjects.SiteName))
.ActiveDocument.Bookmarks("Architect").Range.Text _
= DLookup("CustName", "qryArchMainC", _
"(projectID = " & Forms!FrmProjects.ProjectNumber") AND (Type =
""Architect"")")
.ActiveDocument.Bookmarks("Architect").Range.Text _
= DLookup("CustName", "qryArchMainC", _
"(projectID = " & Forms!FrmProjects.ProjectNumber") AND (Type =
""MainContractor"")")

Thanks for your ongoing help.
Regards
Colin

Hi Colin,

With qryArchMainC as you have it, the DLookup() calls will be more like
this, to pull out the Architect's name from one record:

DLookup("CustName", "qryArchMainC", _
"(projectID = " & Forms!FrmProjects.ProjectNumber _
") AND (Type = ""Architect"")")

and a slightly different one to get the MainContractor's name from
another. I'll leave it to you to work out why.

 

[John Nurick]

Frustrated of England replies!!

Hi John,
I guess that the "slightly different" one is replacing the final "architect"
with "MainContractor"...however, when I put in the "original" code, I'm
getting an error of "Compile Error: Expected: End of Statement" with the
final line of code: ") AND (Type = ""Architect"")") highlighted. I've
tried the closing ) in different places (and even added one, but to no
avail. I realise that I'm doing something stupid, but I can't spot what!!

I left out a &, should be

DLookup("CustName", "qryArchMainC", _
"(projectID = " & Forms!FrmProjects.ProjectNumber _
& ") AND (Type = ""Architect"")")

You can also simplify and speed up your code by replacing constructions
like this

.ActiveDocument.Bookmarks("ProjectNumber").Select
.Selection.Text = (CStr(Forms!FrmProjects.ProjectNumber))

with
.ActiveDocument.Bookmarks("ProjectNumber").Range.Text = _
CStr(Forms!FrmProjects.ProjectNumber))

This means Word doesn't have to scroll the document to the bookmark
before inserting the text.

The full code is...

With oword ' Make the application visible.
.Visible = True
' Open the document.
.Documents.Open ("C:\Documents and Settings\Colin\My
Documents\Universal Spreadsheets\" & _
"Glass Block Technology\Letters\S & F
enquiry db.doc")
' Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("ProjectNumber").Select
.Selection.Text = (CStr(Forms!FrmProjects.ProjectNumber))
.ActiveDocument.Bookmarks("ProjectName").Select
.Selection.Text = (CStr(Forms!FrmProjects.ProjectName))
.ActiveDocument.Bookmarks("SiteName").Select
.Selection.Text = (CStr(Forms!FrmProjects.SiteName))
.ActiveDocument.Bookmarks("Architect").Range.Text _
= DLookup("CustName", "qryArchMainC", _
"(projectID = " & Forms!FrmProjects.ProjectNumber") AND (Type =
""Architect"")")
.ActiveDocument.Bookmarks("Architect").Range.Text _
= DLookup("CustName", "qryArchMainC", _
"(projectID = " & Forms!FrmProjects.ProjectNumber") AND (Type =
""MainContractor"")")

Thanks for your ongoing help.
Regards
Colin

 

[Colin Foster]

John,
You are a star!!
This issue has been bugging me for ages, now. I used to incorporate "word
documents" by creating reports which took ages & the formatting never really
worked. Then, with this database, I came across the ability (I think by pure
fluke) to open up word from within Access and "poke" info directly into it.
I then received some advice on how to do it effectively, but this final
hurdle has been frustrating me for a long time and I was almost ready to
give up on it, but I thought that I would try the NGs one last time...I'm
glad that I did!
As a last thought, one of your earlier messages implied a different way of
writing my query "qryArchMainC"...

I'd do this by setting up a query - let's call it qryJobsContractors -
that returns a list of jobs, each with its associated Architect and
MainContractor, i.e. three fields
JobID
Architect
MainContractor

And your response to my reply of ...

With qryArchMainC as you have it, the DLookup() calls will be more like
this, to pull out the Architect's name from one record:

I think that I can see the advantages of using the query that you suggest in
that there is one "row" of information per JobID. However, I couldn't get my
query to do this. Presumably I need to set up calculated fields to achieve
this... or am I missing something obvious??

This is a minor point as you have solved my main issue, however, if you have
the time, I would be interested as that style of query result would then be
very similar to the lookup table that I am more used to within Excel (which
I enjoy playing with far more than Access!!)

Once again, thanks for your considerable help on this issue.

Regards
Colin

 

[John Nurick]

Hi Colin,

I'm glad it's coming together. One does rather have to keep knocking the
rocks together to learn.

WRT the query, it's not calculated fields. One approach is to join the
table to itself on JobID, so one record in the query is generated from
separate records in two "instances" of the table. It will be something
like this (note the aliases A and B for the table name):

SELECT A.JobID,
A.CustName AS Architect,
B.CustName AS MainContractor
FROM tblT AS A INNER JOIN tblT AS B ON A.JobID = B.JobID
WHERE (A.Type="Architect")
AND (B.Type="MainContractor");

That gives you three fields, JobId, Architect and MainContractor, and
will only return records for jobs that have both Architect and
MainContractor.

Another approach is to use a Crosstab query. I used the crosstab query
wizard to build the basic query. Then I went to the query properties
window in query design view and entered the only column headings we're
interested in, namely "Architect","MainContractor" (without this, the
query would include a column for every other value it found in the Type
field). The result was:

TRANSFORM First(tblT.CustName) AS FirstOfCustName
SELECT tblT.JobID
FROM tblT
GROUP BY tblT.JobID
PIVOT tblT.Type In ("Architect","MainContractor");