Results 1 to 3 of 3
  1. #1
    alpinegroove is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Jan 2011
    Posts
    114

    VBA TypeText - Retain Formatting?

    I am using .TypeText to insert the contents of a field in Access (2002) into a bookmarked location in a Word (2002) document.


    One of my fields in Access is set as Currency and displays properly in Access (e.g., $2,500.00).
    However, once the VBA code inserts that into a Word document, the number displays as 2500 in Word, without the formatting.

    Is there a way to retain the currency formatting from Access?

    Thank you.

    Code:
    Private Sub GenerateContract_Click()
    
       ' Check for empty fields and unsaved record.
       If IsNull(FirstName) Then
         MsgBox "First name cannot be empty"
         Me.FirstName.SetFocus
         Exit Sub
       End If
       If IsNull(LastName) Then
         MsgBox "Last name cannot be empty"
         Me.LastName.SetFocus
         Exit Sub
       End If
       If IsNull(StreetAddress) Then
         MsgBox "Street address cannot be empty"
         Me.StreetAddress.SetFocus
         Exit Sub
       End If
       
       If Me.Dirty Then
         If MsgBox("Record has not been saved. " & Chr(13) & _
             "Do you want to save it?", vbInformation + vbOKCancel) = vbOK Then
           DoCmd.RunCommand acCmdSaveRecord
         Else
           Exit Sub
         End If
       End If
            
       ' Create a Word document from template.
       Dim WordApp As Word.Application
       Dim strTemplateLocation As String
      
       ' Specify location of template
       strTemplateLocation = "H:\Instructor Contract Template.doc"
        
        
       On Error Resume Next
       Set WordApp = GetObject(, "Word.Application")
       If Err.Number <> 0 Then
         Set WordApp = CreateObject("Word.Application")
       End If
       On Error GoTo ErrHandler
       
       
       WordApp.Visible = True
       WordApp.WindowState = wdWindowStateMaximize
       WordApp.Documents.Add Template:=strTemplateLocation, NewTemplate:=False
        
       ' Replace each bookmark with field contents.
       With WordApp.Selection
       
         .Goto what:=wdGoToBookmark, Name:="Name"
         .TypeText Trim([FirstName] & " " & [LastName])
       
         .Goto what:=wdGoToBookmark, Name:="StreetAddress"
         .TypeText [StreetAddress]
        
         .Goto what:=wdGoToBookmark, Name:="City"
         .TypeText [City]
      
         .Goto what:=wdGoToBookmark, Name:="State"
         .TypeText [State]
        
         .Goto what:=wdGoToBookmark, Name:="ZipCode"
         .TypeText [ZipCode]
             
         .Goto what:=wdGoToBookmark, Name:="Name2"
         .TypeText Trim([FirstName] & " " & [LastName])
    
         ' These fields are currency in Access:'
        
         .Goto what:=wdGoToBookmark, Name:="InstructorComp"
         .TypeText [Instructor]
         
         .Goto what:=wdGoToBookmark, Name:="TAComp"
         .TypeText [TA]
         
         .Goto what:=wdGoToBookmark, Name:="ReaderComp"
         .TypeText [Reader]
              
       End With
        
       DoEvents
       WordApp.Activate
        
       Set WordApp = Nothing
       Exit Sub
    
    ErrHandler:
    Set WordApp = Nothing
    
    End Sub

  2. #2
    Oxydo is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    3
    Sorry Alpine, i responded too quickly - my solution is based on a not-similar problem and would not help your case.

    Regards,

    Oxydo

  3. #3
    alpinegroove is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Jan 2011
    Posts
    114
    Thank you. It turns out there is VBA code for the formatting:

    .TypeText FormatCurrency([FieldName])

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Conditional Formatting vs. VBA Formatting
    By gopherking in forum Reports
    Replies: 5
    Last Post: 11-09-2011, 06:53 PM
  2. Replies: 8
    Last Post: 09-05-2011, 11:16 PM
  3. Between formatting
    By reidn in forum Queries
    Replies: 2
    Last Post: 07-21-2011, 12:11 PM
  4. Data Entry Form: Retain Selected List Box Value
    By William McKinley in forum Forms
    Replies: 1
    Last Post: 11-05-2010, 10:14 AM
  5. Replies: 0
    Last Post: 02-12-2009, 05:23 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums