Access To Word Export Problem

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

Howdy folks. I have been spinning my wheels on this little issue for a
couple days now, and have been searching the net with no luck finding a
solution.

Within the Access file, I open a user's record. Then I can click a button
that calls a sub routine that populates the word template via bookmarks.
Works great, except someone wanted to change it - now I need to pull data for
a field from a different column. The column I need the data from is a number
field. The form does the lookup into another table to obtain the value of
that number. The two tables have a relationship on this value. Problem is
that when I export that field to the Word doc, I only get the number value -
not the text value. I know it's something simple, but I have not found the
right place to make the change.

This is the line of code I want to change:
.Item("Type").Range.Text = [Forms]![Contacts]![ContactTypeID] & " "

But I somehow need to change this so that it looks in the [Contact Types]
table, pulls the Description where the ID=ID.

Summarized command:
Private Sub Command180_Click()

'Declare an instance of Microsoft Word.
Dim Wrd As New Word.Application
Set Wrd = CreateObject("Word.Application")

'Specify the path and name to the Word document.
Wrd.Documents.Add "template.dot"
Wrd.Visible = True

'Replace each bookmark with current data.
With Wrd.ActiveDocument.Bookmarks
'[Removed]
.Item("Type").Range.Text = [Forms]![Contacts]![ContactTypeID] & " "
'[Removed]
End With

End Sub


----Any help appreciated. Thanks in advance.
 
Hi Robert

I'm not sure (if you need to) how you relate the relevant bookmark in your
word document to the relevant row of data in access but to get the
Description instead of the ID try the following line of code...

..Item("Type").Range.Text = dlookup("[Description]", "[Contact Types]",
"[ContactTypeID] = " & [Forms]![Contacts]![ContactTypeID])

(Watch out for how this has word wrapped)

I have assumed that there are fields named [Description] and [ContactTypeID]
in the [Contact Types] table so you will need to amend these if they are
different.

hth

Andy Hull
 
Hello Robert,

I'm assuming that this is a re-post / a "why didn't anybody answer?"
question.

Yours is an approach which makes use of challenging/expert type features of
both Word and Access. An answer will require someone who is an expert on
both, probably heavier on the Word side regarding fancy use of features.
It will probably take some extra fishing to find that person.

Also, the descriptive part of your post really just makes a few comments
about what you're doing rather than really explaining it. At best that
raises the bar even higher regarding who might be able to figure out what's
going on there.

Hope that helps a tiny bit.
 
Excellent, this is exactly what I was looking for.
However, it is only pulling record # 1 from Contact Types - even if I hard
code the ContactTypeID to something else.
At least this has me moving in the right direction, thank you.

Andy Hull said:
Hi Robert

I'm not sure (if you need to) how you relate the relevant bookmark in your
word document to the relevant row of data in access but to get the
Description instead of the ID try the following line of code...

.Item("Type").Range.Text = dlookup("[Description]", "[Contact Types]",
"[ContactTypeID] = " & [Forms]![Contacts]![ContactTypeID])

(Watch out for how this has word wrapped)

I have assumed that there are fields named [Description] and [ContactTypeID]
in the [Contact Types] table so you will need to amend these if they are
different.

hth

Andy Hull


Robert said:
Howdy folks. I have been spinning my wheels on this little issue for a
couple days now, and have been searching the net with no luck finding a
solution.

Within the Access file, I open a user's record. Then I can click a button
that calls a sub routine that populates the word template via bookmarks.
Works great, except someone wanted to change it - now I need to pull data for
a field from a different column. The column I need the data from is a number
field. The form does the lookup into another table to obtain the value of
that number. The two tables have a relationship on this value. Problem is
that when I export that field to the Word doc, I only get the number value -
not the text value. I know it's something simple, but I have not found the
right place to make the change.

This is the line of code I want to change:
.Item("Type").Range.Text = [Forms]![Contacts]![ContactTypeID] & " "

But I somehow need to change this so that it looks in the [Contact Types]
table, pulls the Description where the ID=ID.

Summarized command:
Private Sub Command180_Click()

'Declare an instance of Microsoft Word.
Dim Wrd As New Word.Application
Set Wrd = CreateObject("Word.Application")

'Specify the path and name to the Word document.
Wrd.Documents.Add "template.dot"
Wrd.Visible = True

'Replace each bookmark with current data.
With Wrd.ActiveDocument.Bookmarks
'[Removed]
.Item("Type").Range.Text = [Forms]![Contacts]![ContactTypeID] & " "
'[Removed]
End With

End Sub


----Any help appreciated. Thanks in advance.
Click to expand...
Click to expand...
 
Typo in my modification - works perfectly!

Robert said:
Excellent, this is exactly what I was looking for.
However, it is only pulling record # 1 from Contact Types - even if I hard
code the ContactTypeID to something else.
At least this has me moving in the right direction, thank you.

Andy Hull said:
Hi Robert

I'm not sure (if you need to) how you relate the relevant bookmark in your
word document to the relevant row of data in access but to get the
Description instead of the ID try the following line of code...

.Item("Type").Range.Text = dlookup("[Description]", "[Contact Types]",
"[ContactTypeID] = " & [Forms]![Contacts]![ContactTypeID])

(Watch out for how this has word wrapped)

I have assumed that there are fields named [Description] and [ContactTypeID]
in the [Contact Types] table so you will need to amend these if they are
different.

hth

Andy Hull


Robert said:
Howdy folks. I have been spinning my wheels on this little issue for a
couple days now, and have been searching the net with no luck finding a
solution.

Within the Access file, I open a user's record. Then I can click a button
that calls a sub routine that populates the word template via bookmarks.
Works great, except someone wanted to change it - now I need to pull data for
a field from a different column. The column I need the data from is a number
field. The form does the lookup into another table to obtain the value of
that number. The two tables have a relationship on this value. Problem is
that when I export that field to the Word doc, I only get the number value -
not the text value. I know it's something simple, but I have not found the
right place to make the change.

This is the line of code I want to change:
.Item("Type").Range.Text = [Forms]![Contacts]![ContactTypeID] & " "

But I somehow need to change this so that it looks in the [Contact Types]
table, pulls the Description where the ID=ID.

Summarized command:
Private Sub Command180_Click()

'Declare an instance of Microsoft Word.
Dim Wrd As New Word.Application
Set Wrd = CreateObject("Word.Application")

'Specify the path and name to the Word document.
Wrd.Documents.Add "template.dot"
Wrd.Visible = True

'Replace each bookmark with current data.
With Wrd.ActiveDocument.Bookmarks
'[Removed]
.Item("Type").Range.Text = [Forms]![Contacts]![ContactTypeID] & " "
'[Removed]
End With

End Sub


----Any help appreciated. Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
There are any number of ways to overcome the issue that you've
expressed. However, you've goven us so little to go on that there's
no way to know what would work best for you. You could write a whole
post on the things you didn't tell us. :-) Lots and lots of posts
that receive no responses are ignored simply because the original
poster plays "I've got a secret and I ain't gonna' tell you". People
would rather address posts in which the problems are clearly and
completely presented and there is no need to start down the path of
"20 Questions". You might scan a bunch of posts and see how the
dialogue goes. You might also visit www.mvps.org/access and read the
item "Newiquette"

While continuing with your over all Automatin paradigm, I suggest that
just about any solution that will yield a reasonable path for
maintenance going forward will require changing the part of the VBA
that you have shown. Why do I think that there might be a need to
manage changes? Because there alreasy is/has been such a need.

What follows is one such path: The VBA for identifying and loading
Bookmarks with the correct values should be placed inside a Select
statement on the Bookmark name within the code. All bookmark name
values that will require special handling must have their own special
code. None of the bookmark values whose handling has not changed will
require any special code. All of the current bookmark loading code
that handles things as they were before the change should be placed in
the "Case Else" clause of the Select statement.

In the future, as other bookmark content becomes re-defined, you can
just put in a new "Case 'bookmark' clause (before the "Case Else"
clause) for the alteed bookmark and include the code or a call to ghe
code to return the proper value.

HTH
--
-Larry-
--

Robert said:
Perhaps I am looking at this the wrong way? Any help appreciated...

Robert said:
Howdy folks. I have been spinning my wheels on this little issue for a
couple days now, and have been searching the net with no luck finding a
solution.

Within the Access file, I open a user's record. Then I can click a button
that calls a sub routine that populates the word template via bookmarks.
Works great, except someone wanted to change it - now I need to pull data for
a field from a different column. The column I need the data from is a number
field. The form does the lookup into another table to obtain the value of
that number. The two tables have a relationship on this value. Problem is
that when I export that field to the Word doc, I only get the number value -
not the text value. I know it's something simple, but I have not found the
right place to make the change.

This is the line of code I want to change:
.Item("Type").Range.Text = [Forms]![Contacts]![ContactTypeID] & " "

But I somehow need to change this so that it looks in the [Contact Types]
table, pulls the Description where the ID=ID.

Summarized command:
Private Sub Command180_Click()

'Declare an instance of Microsoft Word.
Dim Wrd As New Word.Application
Set Wrd = CreateObject("Word.Application")

'Specify the path and name to the Word document.
Wrd.Documents.Add "template.dot"
Wrd.Visible = True

'Replace each bookmark with current data.
With Wrd.ActiveDocument.Bookmarks
'[Removed]
.Item("Type").Range.Text = [Forms]![Contacts]![ContactTypeID] & " "
'[Removed]
End With

End Sub


----Any help appreciated. Thanks in advance.
Click to expand...
Click to expand...
 
Back
Top