Vba Typetext - Retain Formatting?

[alpinegroove]

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.

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

 

[boblarson]

You didn't say which field so I am just grabbing one for an example. You would use

.TypeText FormatCurrency([TA], 2)

 

[alpinegroove]

Perfect, thank you!

 

[alpinegroove]

Can FormatCurrency be used with .TypeText Nz?

This is working for me:

.TypeText Nz(Me.[CourseFinalDate], "N/A")

But this is not working:

.TypeText Nz(FormatCurrency(Me.[TA]), "N/A")

 

[boblarson]

Sorry for such a late reply but I haven't been able to be on lately.

.TypeText FormatCurrency(Nz(Me.[TA], "N/A"))

would be the correct way to do it.