Access form to word doceument using bookmarks 2

[oggstar1]

I have the following code that takes text form a from and inserts it into a word document.

It works well however I would like the text inserted to be in currency format.

In access the format is as follows. $10,000.00. When converted to word as a bookmark it displays as 10000.
The bookmark is in a text from field in word, albeit I have changed the drop down field to currency.
I have searched everywhere for what I would have thought should be quite simple. Any help appreciated.
I would have thought some coding after the following may help.

.ActiveDocument.Bookmarks("payment").Select
.Selection.Text = (CStr(Forms!frm_ladnownerpayments!payment))

Something like this
'ActiveDocument.Bookmarks("payment ").Range.Text = sCurrency'

Private Sub Command491_Click()
    On Error GoTo Command491_Err

    Dim objWord As Word.Application

    Set objWord = CreateObject("Word.Application")
 
    With objWord
        'Make the application visible.
        .Visible = True

        'Open the document.
        .Documents.Open ("")
       
        'Move to each bookmark and insert text from the form.
        
                .ActiveDocument.Bookmarks("pyament").Select
        .Selection.Text = (CStr(Forms!frm_ladnownerpayments!payment))
        
    'objWord.ActiveDocument.PrintOut Background:=False'

    'Close the document without saving changes.
              
    'objWord.ActiveDocument.SaveAs ("\\")
    objWord.ActiveDocument.SaveAs ")
    
    ', FileFormat:=wdFormatPDF'
    'objWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges'
    'Quit Microsoft Word and release the object variable.
    'objWord.Quit'
    'Set objWord = Nothing'
    
    
    End With
    
    Exit Sub

Command491_Err:
    'If a field on the form is empty, remove the bookmark text, and
    'continue.
    If Err.Number = 94 Then
        objWord.Selection.Text = ""
        Resume Next

    'If the Photo field is empty.
    ElseIf Err.Number = 2046 Then
        MsgBox "Please add a photo to this record and try again."
    Else
        MsgBox Err.Number & vbCr & Err.Description
    End If

    Exit Sub
 
End Sub

 

[Andrzejek]

Instead of:

.ActiveDocument.Bookmarks("pyament").Select
.Selection.Text = (CStr(Forms!frm_ladnownerpayments!payment))

Try:

.ActiveDocument.Bookmarks("pyament").Range.Text =[blue] "$" & [/blue](CStr(Forms!frm_ladnownerpayments!payment))

---- Andy

There is a great need for a sarcasm font.

 

[oggstar1]

Thanks for the reply albeit it did not quite work, I am getting the message 6028 the range cannot be deleted.

I did manage to get the code below to work, however it only put a $ in front of the number. i.e $10000. I am hoping for formatting as per $10,000.00, bearing in mind it could be 3 digits or 6 digits before the decimal.

.ActiveDocument.Bookmarks("gstamount1").Select
        .Selection.Text = "$" & (CStr(Forms!frm_ladnownerpayments!gstamount))

I have played around with this below but unable to get it to work

    '.Format (gstamount.Value = "$###,##")
        '& (CStr(Forms!frm_ladnownerpayments!gstamount))
         '.Value = Format(gstamount.Value, "$###,##")
         '.Format = "$#,###,##0.00;($#,###,##0.00)"
         '.Selection.Range.Font.Italic = True

 

[Andrzejek]

OK, try:

.ActiveDocument.Bookmarks("gstamount1").Select
.Selection.Text = [blue]FormatCurrency[/blue](Forms!frm_ladnownerpayments!gstamount)

Info from here


---- Andy

There is a great need for a sarcasm font.

 

[oggstar1]

Sorry i could not log in the other day for some reason, thanks very much it works really well.

I also have a button that i click from a form to create an email. It takes data from the form.
I have the same problem with the email in the number does not display in currency format, it comes out as 4512.223292226

The code is as follows;

strBody = strBody & "Payment Amount: $" & [Forms]![frm_ladnownerpayments]![paymenttotal] & Chr(13)

 

[oggstar1]

Final thing

I can get the word document to automatically save as a word document, but not as a pdf, any ideas.

   objWord.ActiveDocument.SaveAs ("\\dfkuusdb\payments\" & Me.IDoptionpayments & "_" & Me.site & ".pdf")
   , FileFormat:=wdFormatPDF'

 

[Andrzejek]

>the number does not display in currency format

Did you try:

strBody = strBody & "Payment Amount: " & [blue]FormatCurrency[/blue]([Forms]![frm_ladnownerpayments]![paymenttotal]) & Chr(13)

>to automatically save as a word document, but not as a pdf

Did you try to record a macro in Word to SaveAs PDF? If you do, you would get something like this:

ActiveDocument.ExportAsFixedFormat OutputFileName:= _
"\\dfkuusdb\payments\" & Me.IDoptionpayments & "_" & Me.site & ".pdf", ExportFormat:=wdExportFormatPDF, _
OpenAfterExport:=True, OptimizeFor:=wdExportOptimizeForPrint, Range:= _
wdExportAllDocument, From:=1, To:=1, Item:=wdExportDocumentContent, _
IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:= _
wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:= _
True, UseISO19005_1:=False

---- Andy

There is a great need for a sarcasm font.

 

[oggstar1]

Great the PDf works, thanks.

The email still does not format the currency properly. I did try the code below.

strBody = strBody & "Payment Amount: " & FormatCurrency([Forms]![frm_ladnownerpayments]![paymenttotal]) & Chr(13)


Also any ideas on how you attach your email signature?

Private Sub Command719_Click()

DoCmd.RunCommand acCmdRefresh

Dim strEmail, strBody As String
Dim objEmail As Outlook.MailItem

'**creates an instance of Outlook
Dim objOutlook As Outlook.Application
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)


'Add end of the body

'strBody = strBody & "Payment Amount: $" &
strBody = strBody & [Forms]![frm_ladnownerpayments]![paymenttotal] & Chr(13)
)
'***creates and sends email
With objEmail
    .To = [Forms]![frm_ladnownerpayments]![owner]
    '.To = "" & ";" & ""
    .CC = [Forms]![frm_ladnownerpayments]![email] & ";" & [Forms]![frm_ladnownerpayments]![pmail]
 .Subject = [Forms]![frm_ladnownerpayments]![name] & " " & [Forms]![frm_ladnownerpayments]![num]
 

   .Body = strBody
    .BodyFormat = olFormatRichText
    .Display
End With

Exit Sub

 

[Andrzejek]

Hmmm... interesting.
I've just tried this:

Dim strBody As String
Dim sngMoney As Single

sngMoney = 123.45
strBody = "Payment Amount: " & FormatCurrency(sngMoney)
Debug.Print strBody

And I've got this:
[tt]Payment Amount: $123.45 [/tt]

BTW - [tt]Private Sub Command719_Click()[/tt] ???
How many command buttons do you have on your Form


---- Andy

There is a great need for a sarcasm font.

 

[oggstar1]

Thanks works great, school boy error, thanks for all your patience.

Yes i think i had a go and a few difference versions in terms of command buttons albeit, not 719.
I am assuming some of these would be for other buttons as apart of the database. The database may have about 75 buttons. This form only has about 5.


One final question i promise.

When I save the word document i use the following file path;

"\\server\letter\" & Format(Date, "yyyy-mm-dd") & "_" & Me.name &.pdf",

I have a field name in access called datedue. Instead of having today's date. I would prefer to change it to datedue. Somthing like,

"\\server\letter\"& Me.duedate (Format("yyyy-mm-dd")) & "_" & Me.sitename &.pdf",

When i try the following below i get an error message, in that it does not like the file path name, i think it may be this character /. In case it would not give me this format. (Format("yyyy-mm-dd")

"\\server\letter\"& Me.duedate & "_" & Me.sitename &.pdf",

   objWord.ActiveDocument.ExportAsFixedFormat OutputFileName:= _
"\\syd-fs-001\letter\" & Format(Date, "yyyy-mm-dd") & "_" & Me.name &.pdf", ExportFormat:=wdExportFormatPDF, _
OpenAfterExport:=True, OptimizeFor:=wdExportOptimizeForPrint, Range:= _
wdExportAllDocument, From:=1, To:=1, Item:=wdExportDocumentContent, _
IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:= _
wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:= _
True, UseISO19005_1:=False
    objWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges '

 

[Andrzejek]

All of the paths/file names above will give you either an error or invalid path.

Do yourself a favor and try all of that on the side, not in your app.
Create just a simple form in Access, place a few controls, named them as in your 'big' app, It would be a lot easier to find the right syntax.

Eventually you will end up with this:

[blue]"\\server\letter\"[/blue] & Format([red]Me.duedate[/red], "yyyy-mm-dd") & [blue]"_"[/blue] & Me.sitename & [blue]".pdf"[/blue]

(Assuming [tt]Me.duedate[/tt] contains a valid date and [tt]Me.sitename[/tt] does NOT contain any characters like /, \, ?, *, etc.)

>The database may have about 75 buttons. This form only has about 5.
So name them appropriately. Otherwise you will not be a very happy camper when - in a year or so - you have to get back to this app and modify it.

Or somebody else would have to decipher which button number does what.

Just my opinion...


---- Andy

There is a great need for a sarcasm font.

 

[oggstar1]

Ok thanks, work perfectly.

Yes the database needs a bit of clean up.

I am pretty sure i once came across a program that automatically look for issues within an access file and allows you to;

[li]Look for fields that are no longer used[/li]
[li]If you change a field name it updates through the forms and queries and Vba code[/li]
[li]Look for queries that are no longer used[/li]
[li]Allow you to change the font and background on all forms etc[/li]

Do you know of any good software that does this.

Also a bit of an open ended question, but if you were looking at moving away from access to a browser based database and you wanted to convert access to a new platform. What package would you recommend.
Sql Server backend with ? frontend. Do you then start losing some of the functionality of access

 

[Andrzejek]

You may want to investigate MZTools, they do support Visual Basic for Applications (VBA) and it will do most of the features you describe. I use it and love it.

I don't know if there is such an animal as "browser based database". Data base is a data base. How and where you use it is up to you.

Some (‘serious’ data base) people claim Access to be the Fisher Price of data bases, but I’ve seen some amazing stuff done in Access (and some very lousy work done in Oracle or SQL Server). So the point is: how you use it and what language you use to access your DB.


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

In addition to MZTools FMS Inc has a number of Access tools that are top shelf.

For web based database backends I would recommend SQL Server. There is a push toward the Microsoft Power Platform.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[oggstar1]

Thanks both, i will check out all of these programs.