Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Fill Word Document with Data from Access Form 3

Status
Not open for further replies.
jpkeller55

jpkeller55

Technical User
Apr 11, 2003
131
US
Clearly I am a novice at this and need help. I am trying to utilize the code listed in faq702-2379 to populate data from an Access form to a Word document. I am using 2002 version of Access and Word. Code so far:
Code: 
Public Function CreateWordLetter(strDocPath As String)
   
   If IsNull(strDocPath) Or strDocPath = "" Then
      Exit Function
   End If

   Dim dbs As Database
   Dim objWord As Object
   Dim PrintResponse
   Set dbs = CurrentDb
   
   'create reference to Word Object
   
   Set objWord = CreateObject("Word.Application")
   
   'Word Object is created - now let's fill it with data.
   
   With objWord
       .Visible = True
       .Documents.Open (strDocPath)
       'move to each bookmark, and insert correct text.
       [COLOR=red].ActiveDocument.Bookmarks("FN".Select][/color]
       .Selection.Text = (CStr(Forms!NamesFrm!FirstName))
       [COLOR=red].ActiveDocument.Bookmarks.Add Name:=FN,[/color]
       Range = Selection.Range
    
    'continue the ActiveDocument and Selection statements for each bookmark that you have on the Word Document **
      
   End With

   'find out if the user would like to print the document
   'at this time.

   PrintResponse = MsgBox("Print this document?", vbYesNo)
   If PrintResponse = vbYes Then
      objWord.ActiveDocument.PrintOut Background:=False
   End If

   'release all objects
         
   Set objWord = Nothing
   Set dbs = Nothing

End Function
First I am getting a message that states: Compile error:User-defined type not defined. Also, the code in Red is in red in the VBA editor window. Can anybody lend some help to get me on track?

Thanks, jpkeller55
 
Sort by date Sort by votes
Hi

To put text at a bookmark use..

Assuming appword is an object of Word.Application

If appword.ActiveDocument.Bookmarks.Exists(bmk) = True Then
appword.ActiveDocument.Bookmarks(bmk).Select
appword.Selection.TypeText "Some text"
end if

and that's it!

I have a routine in my app that inserts a string at a bookmark.Then allows my users to create more than one bookmark, for example, "Client" can be a bookmark but if you want the same bookmark somewhere else you can;t since bookmarks are unique.. therefore i tell them to start at "Client" and then use "Client1", "Client2" etc..

The function I then use may be useful to you.i have included it in full below..


The params :
appword = object of word.application
bmk = the bookmark in the document
str = the text i want to put at the bookamrk

Also in this routine it allows for an interactive bookmark. If you put the bookmark "AskPhoneNumber" in the document and call the routine below with "AskPhoneNumber" for the bmk param. When it finds the bookmark it will prompt you for any data you want inserting. Might be useful..might not! ;-)


Sub PutAllBmkStr(appword As Object, bmk As String, str As String)
Dim newbmk As String
Dim i As Integer
If Mid$(bmk, 1, 3) <> "Ask" Then

If appword.ActiveDocument.Bookmarks.Exists(bmk) = True Then
appword.ActiveDocument.Bookmarks(bmk).Select
If Nz(str, 0) <> 0 Then
appword.Selection.Font.Bold = False
appword.Selection.Font.Underline = False
appword.Selection.TypeText str
End If
For i = 1 To 100
newbmk = bmk & i
If appword.ActiveDocument.Bookmarks.Exists(newbmk) = True Then
appword.ActiveDocument.Bookmarks(newbmk).Select
If Nz(str, 0) <> 0 Then
appword.Selection.Font.Bold = False
appword.Selection.Font.Underline = False
appword.Selection.TypeText str
End If
End If
Next i
End If
Else
Dim prompt, reply As String
Dim l As Integer

If appword.ActiveDocument.Bookmarks.Exists(bmk) = True Then
appword.ActiveDocument.Bookmarks(bmk).Select
l = Len(bmk) - 3

prompt = "Enter data for the bookmark encountered" + vbCrLf + Mid$(bmk, 4, l)
reply = InputBox(prompt, "Free Format")
If Len(reply) > 0 Then
appword.Selection.Font.Bold = False
appword.Selection.Font.Underline = False
appword.Selection.TypeText reply
For i = 1 To 100
newbmk = bmk & i
If appword.ActiveDocument.Bookmarks.Exists(newbmk) = True Then
appword.ActiveDocument.Bookmarks(newbmk).Select
appword.Selection.Font.Bold = False
appword.Selection.Font.Underline = False
appword.Selection.TypeText reply
End If
Next i
End If
End If


End If
End Sub



Hope this helps!

Regards

BuilderSpec
 
Upvote 0 Downvote
BuilderSpec,
I am looking to be able to populate a Word document from fields in an Access form that would be currently open. I am not sure how to apply your code to that scenario. Would your code work for that?

Thanks, JPKELLER
 
Upvote 0 Downvote
Hi

Using your code I think it would be...

Public Function CreateWordLetter(strDocPath As String)

If IsNull(strDocPath) Or strDocPath = "" Then
Exit Function
End If

Dim dbs As Database
Dim objWord As Object
Dim PrintResponse
Set dbs = CurrentDb

'create reference to Word Object

Set objWord = CreateObject("Word.Application")

'Word Object is created - now let's fill it with data.



With objWord
.Visible = True
.Documents.Open (strDocPath)
end with
If objWord.ActiveDocument.Bookmarks.Exists("FN") = True Then
objWord.ActiveDocument.Bookmarks("FN").Select
objWord.Selection.TypeText CStr(Forms!NamesFrm!FirstName)
End If



'find out if the user would like to print the document
'at this time.

PrintResponse = MsgBox("Print this document?", vbYesNo)
If PrintResponse = vbYes Then
objWord.ActiveDocument.PrintOut Background:=False
End If

'release all objects

Set objWord = Nothing
Set dbs = Nothing

End Function




Hope this helps!

Regards

BuilderSpec
 
Upvote 0 Downvote
OK, I put in the code an attached to a button on a form and I am getting the following error message:

Compile error: User-defined type not defined and the following line in the code is highlighted:

Dim dbs As Database

I am operating in Access 2002...does it not recoginize this statement?
 
Upvote 0 Downvote
menu Tools -> References ...
tick the Microsoft DAO 3.6 Object Library.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
Thank you both PHV and BuilderSpec. It works beautifully...now I will add the other text fields. Thanks much!!

jpkeller
 
Upvote 0 Downvote
One more question for you on this one...How do I deal with fields on the form that are null but have a bookmark on the Word form. I would prefer to have the null value pulled to the Word document so that the user could then enter manually on the Word document. As the code is written now, if a field is null on the Access form, I get this error message:
Run time error-94: Invalid use of Null

Thanks again for helping. jpkeller
 
Upvote 0 Downvote
objWord.Selection.TypeText Forms!NamesFrm!FirstName & ""

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
Just as a point if you look at the code..

Dim db as Database

set db = CurrentDb

set db = nothing

Is all you do with db, you don;t actually use it as far as I can tell so you could delete those lines of code could you not ?


Also

objWord.Selection.TypeText Nz(Forms!NamesFrm!FirstName,"")

is another way to protect from Nulls.

it is saying that if the first argument is Null then use the second one instead.






Hope this helps!

Regards

BuilderSpec
 
Upvote 0 Downvote
Hi BuilderSpec,

I tried removing the code that you outlined and indeed the functionality was retained. Thanks for your informative response...it helps me learn more about this stuff.

jpkeller
 
Upvote 0 Downvote
Hopefully, one final question on this thread...How do I format a date field from the form so that it prints out the long date on the Word Document. e.g. instead of printing 11/8/06 on the Word Document, I would like it to print out as November 8, 2006.

Code: 
 If objWord.ActiveDocument.Bookmarks.Exists("Dt") = True Then
     objWord.ActiveDocument.Bookmarks("Dt").Select
     objWord.Selection.TypeText Forms!NrsContFrm!TodayDate & ""
   End If
 
Upvote 0 Downvote
If objWord.ActiveDocument.Bookmarks.Exists("Dt") = True Then
objWord.ActiveDocument.Bookmarks("Dt").Select
If IsDate(Forms!NrsContFrm!TodayDate) Then
objWord.Selection.TypeText Format(Forms!NrsContFrm!TodayDate, "Long date")
Else
objWord.Selection.TypeText ""
End If
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
Thanks PHV, however this is printing out as Wednesday, November 8, 2006. Is there a way to eliminate the Wednesday and just return November 8, 2006?
 
Upvote 0 Downvote
objWord.Selection.TypeText Format(Forms!NrsContFrm!TodayDate, "mmmm d, yyyy")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

PrimoSteve
  • Locked
  • Question
Fill in 2nd Textbox2 from Textbox1
Replies
3
Views
689
PrimoSteve
PrimoSteve
TrekBiker
  • Locked
  • Question
Form Data Not Updating
Replies
6
Views
781
dhookom
dhookom
Kennelbloke
  • Locked
  • Solved
Subform not updating in parent form
Replies
10
Views
1K
Kennelbloke
Kennelbloke
PrimoSteve
  • Locked
  • Question
Grid From 1
2
Replies
21
Views
2K
PrimoSteve
PrimoSteve
Xavierparkash
  • Locked
  • Question
MS Access Slow Search in Linked Database
Replies
0
Views
442
Xavierparkash
Xavierparkash

Part and Inventory Search

Sponsor

Back
Top