UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Carriage Return From Access To Word, Access 2016    
 
   
Hugoj
post Aug 23 2019, 12:03 PM
Post#1



Posts: 29
Joined: 22-August 10



Hi,

I send data into a Word document. At one bookmark in the document I have a bulleted list where I enter data. In my database sometimes a long text is entered with a few Carriage Returns (CR). I want that text to have only one bullet with many rows like when I'm entering a normal line feed manually in Word.

When I enter that text into Word from Access via VBA each CR results in a new bullet as expected. I tried to replace this CR in the string to a line feed with Replace(String, Chr(13), Chr(10), 1, ,vbBinaryCompare) before entering the text into Word. But the result is that an extra line is entered with the result that an additional bullet is created.
I also tried vbTextCompare and vbDatabascompare but no difference.

Does anyone have a solution for this problem?


Best regards
Go to the top of the page
 
theDBguy
post Aug 23 2019, 01:00 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,290
Joined: 19-June 07
From: SunnySandyEggo


Hi. Not sure what 1 is supposed to represent when you replace a carriage return with it, but how about using a line feed character instead? Just a thought...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Hugoj
post Aug 24 2019, 07:08 AM
Post#3



Posts: 29
Joined: 22-August 10



1 argument is to say to start from the first character in the string.

If I myself would enter all data I could use line feed in the database. But I cannot expect all users to use line feed instead of carriage return (CR). That will not work. The users will automatically use CR.

CHR(10) is the line feed character so I expected that that would solve the problem. But for some reason it doesnt work.
Go to the top of the page
 
Phil_cattivocara...
post Aug 24 2019, 07:27 AM
Post#4



Posts: 340
Joined: 2-April 18



Could you verify if it is line feed, carriage return or both? You can copy-paste the text to notepad++ and enable "View all characters" (or such a thing, I have Italian interface so I do not know how it appears in English version)

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
Mickjav
post Aug 24 2019, 07:30 AM
Post#5



Posts: 75
Joined: 25-November 18



QUOTE
start from the first character in the string.


If your trying to move the curser to the beginning of text so it doesn't get highlighted try

CODE
Private Sub Notes_Enter()
On Error GoTo HandleErr

Me![Notes].SelStart = 0 'Or Len(Me![Notes]) + 1 ' for end

HandleExit:
    Exit Sub
    
HandleErr:
    Select Case Err.Number
        Case 13
            Exit Sub
        Case Else
            MsgBox Err.Number & vbCrLf & Err.Description
            Resume HandleExit
        Resume
    End Select

This post has been edited by Mickjav: Aug 24 2019, 07:30 AM

--------------------
All open code examples and free projects are only available from: Database Dreams
Go to the top of the page
 
Hugoj
post Aug 24 2019, 08:04 AM
Post#6



Posts: 29
Joined: 22-August 10



Thanks for the reply. I couldnt use Notepad but instead I used Instr to check. And yes I have both linefeed and carriage return in the string.
Go to the top of the page
 
theDBguy
post Aug 24 2019, 08:12 AM
Post#7


Access Wiki and Forums Moderator
Posts: 76,290
Joined: 19-June 07
From: SunnySandyEggo


QUOTE (Hugoj)
1 argument is to say to start from the first character in the string.
Thanks. Now, it makes sense. Have you tried using?
CODE
Replace(YourString, Chr(13) & Chr(10), Chr(10))

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Hugoj
post Aug 24 2019, 08:43 AM
Post#8



Posts: 29
Joined: 22-August 10



Thanks for the reply.
I did a test now with your proposal (Replace(YourString, Chr(13) & Chr(10), Chr(10))) and the additional bullet doesnt apply anymore. So its a step forward but still the line feed in the text creates a new bullet in the Word document.



Go to the top of the page
 
Mickjav
post Aug 24 2019, 08:50 AM
Post#9



Posts: 75
Joined: 25-November 18



What is the code you are using.

I haven't done any word work for a while but don't remember having that problem.

mick

--------------------
All open code examples and free projects are only available from: Database Dreams
Go to the top of the page
 
Hugoj
post Aug 24 2019, 10:35 AM
Post#10



Posts: 29
Joined: 22-August 10



The initial code was wordApp.Selection.Goto what:=wdGoToBookmark, Name:="BMPrisingar"
For I = 1 to All
wordApp.Selection.TypeText Text:=CStr(prisData(I, 4))
wordApp.Selection.InsertParagraphAfter
wordApp.Selection.MoveDown Unit:=wdParagraph, Count:=1
Next I


Then I tried Nytext = Replace(prisData(I, 4), Chr(13) & Chr(10), Chr(110), 1, , vbBinaryCompare) (and vbTextCompare)
wordApp.Selection.TypeText Text:=Nytext


Go to the top of the page
 
Mickjav
post Aug 24 2019, 10:42 AM
Post#11



Posts: 75
Joined: 25-November 18



I built a wizard some years ago and used bookmarks I can dig the relevent code if you want as I didn't have your problem but I was using templates as well let me know if it'll help.

--------------------
All open code examples and free projects are only available from: Database Dreams
Go to the top of the page
 
Hugoj
post Aug 24 2019, 10:58 AM
Post#12



Posts: 29
Joined: 22-August 10



I would very much appriciate it. But I dont Think the problem is with the bookmark. You see the bookmark starts on a bulleted list. And thats the problem I Believe. When I insert a line feed from Access it Changes to a carriage return. And here is the heart of the problem.

Go to the top of the page
 
Mickjav
post Aug 24 2019, 11:32 AM
Post#13



Posts: 75
Joined: 25-November 18



Can't you add a line to the front until the bullet list doen't see it.
I might add this to my site as would make a good example built it nearly 20 years ago I feel so old lol

CODE
'============================================================================
===============
'Designer: Michael Javes
'Date: 30\06\2000 to
'Comments:-
'Module Uses The Following Tables:-
'StblPreferences to Retrive File Locations.
'QryOwnersAddressDetails A Prepaired Query With The Name And Address Details
'Of The Current Owner PreFormated.
'tblCVRighter This Table Remains Open Untill Sub Foinnished As Holds Details Required
'By Most Other Subs
'
Private m_Db As Database
Private m_TemDir As String
Private recCV As Recordset
Private Const m_Template As String = "My CVs.dot"
Private m_SaveDir As String
Sub DoCV(IntID As Integer)
Dim StrOwnerSQL As String
Dim recOwner As Recordset
Dim strSaveLine As String
Dim strCVSQL As String

'Get The Current Location Of The Template Directory
m_TemDir = DLookup("TemplateStore", "StblPreferences")
m_SaveDir = DLookup("OtherDocumentsDir", "StblPreferences")
'Get Handle To Current Database
Set m_Db = CurrentDb()
'Only one Record Just Make Shore
StrOwnerSQL = "SELECT * FROM QryOwnersAddressDetails WHERE OwnersID = 1"
'Table Containing Cv Main Details
strCVSQL = "SELECT * FROM tblCVRighter WHERE CVID=" & IntID
'RecordSet For Current Owner
Set recOwner = m_Db.OpenRecordset(StrOwnerSQL)
'RecordSet For Main CV
Set recCV = m_Db.OpenRecordset(strCVSQL)

Set m_objWord = New Word.Application
Set m_objDoc = m_objWord.Documents.Add(m_TemDir & m_Template)
'Owners Address Header
InsertAtBookMark "Name", recOwner("Owner")
InsertAtBookMark "Address", recOwner("Address")
InsertAtBookMark "City", recOwner("City")
InsertAtBookMark "CountyPostCode", recOwner("Region") & ", " & recOwner("PostalCode")
InsertAtBookMark "Phone", recOwner("Phone")
InsertAtBookMark "Email", recOwner("Email")
'Start Of CV Entry Details
InsertAtBookMark "Date", FormatDateTime(recCV("Date"), vbLongDate)
InsertAtBookMark "ProfileName", recCV("ProfileName")
InsertAtBookMark "Profile", recCV("Profile")
InsertAtBookMark "TableName", recCV("TableName")
InsertAtBookMark "EmploymentName", recCV("EmploymentName")
'Insert Table If Selected
If recCV("UseTable") Then
    InsertItemTable IntID
End If
'Insert The Details Of Current And Past Jobs
InsertJobs IntID
'Insert The Other Details Like Personal and qualifications Etc
InsertOther IntID
'Save The Document
strSaveLine = m_SaveDir & recCV("SaveLine")
'The Word Bits
m_objWord.Selection.EndKey wdStory, wdMove
m_objDoc.SaveAs Filename:=strSaveLine
m_objWord.Caption = "Michaels Demo"
m_objWord.Visible = True
m_objWord.ActiveWindow.Visible = True
'Clean Up
recOwner.Close
recCV.Close
Set recOwner = Nothing
Set recCV = Nothing
Set m_objDoc = Nothing
Set m_objWord = Nothing

End Sub
Private Sub InsertAtBookMark(strBkm As String, VarText As Variant)
'The Bit That Does The Real Work With Word
m_objDoc.Bookmarks(strBkm).Select
m_objWord.Selection.Text = VarText & ""

End Sub
Private Sub InsertItemTable(IntTID As Integer)
'Decs For This Sub
Dim recDetail As Recordset
Dim strDSQL As String
Dim StrTable As String
Dim m_objTable As Word.Table

'The RecordSets we Need To Work With
strDSQL = "SELECT * FROM tblCVTableDetails WHERE CVID= " & IntTID
'Open The Recordsets
Set recDetail = m_Db.OpenRecordset(strDSQL)
'Select Wether To Use Two Or Three Columns For The Data Two Columns Required
If IsNull(recCV("Heading3")) Then
    StrTable = recCV("Heading1") & vbTab & recCV("Heading2") & vbCr
    recDetail.MoveFirst
        While Not recDetail.EOF
            StrTable = StrTable & recDetail("Detail1") & vbTab & recDetail("Detail2") & vbCr
            recDetail.MoveNext
        Wend
Else
    StrTable = recCV("Heading1") & vbTab & recCV("Heading2") & vbTab & recCV("Heading3") & vbCr
    recDetail.MoveFirst
        While Not recDetail.EOF
            StrTable = StrTable & recDetail("Detail1") & vbTab & recDetail("Detail2") & vbTab & recDetail("Detail3") & vbCr
            recDetail.MoveNext
        Wend
End If
'Insert The Records Into The Document Using vbTab As Separator For The
'Conversion To Work With The Format Table
InsertAtBookMark "ItemTable", StrTable
    Set m_objTable = m_objWord.Selection.ConvertToTable(Separator:=vbTab)
        m_objTable.AutoFormat Format:=wdTableFormatClassic1, AutoFit:=True, _
        ApplyShading:=False
        
'Clean Up
recDetail.Close
Set recDetail = Nothing
Set m_objTable = Nothing

End Sub
Private Sub InsertJobs(IntJID As Integer)
Dim strJSQL As String
Dim recJobs As Recordset
Dim strJobs As String
strJSQL = "SELECT * FROM QryCVJobDetails WHERE CVID = " & IntJID

Set recJobs = m_Db.OpenRecordset(strJSQL)
    recJobs.MoveFirst
        While Not recJobs.EOF
                strJobs = vbCr & recJobs("ToFrom") & vbCrLf & vbCrLf & recJobs("Employer") & _
                vbCrLf & vbCrLf & IIf(IsNull(recJobs("JobTitle")), "", "Job Title: " & recJobs("JobTitle") & vbCrLf) & _
                "Job Description: " & _
                recJobs("JobDescription") & vbCr
                InsertAtBookMark "Jobs", strJobs
                recJobs.MoveNext
        Wend
        
recJobs.Close
Set recJobs = Nothing

End Sub
Private Sub InsertOther(IntOID As Integer)
Dim strOSQL As String
Dim recOther As Recordset
Dim strOther As String

strOSQL = "SELECT * FROM QryCVOtherDetails WHERE CVID = " & IntOID

Set recOther = m_Db.OpenRecordset(strOSQL)
    recOther.MoveFirst
        While Not recOther.EOF
            strOther = vbCr & recOther("SectionName") & vbCrLf & vbCrLf & _
            recOther("Details") & vbCr
            InsertAtBookMark "Other", strOther
            recOther.MoveNext
        Wend
        
recOther.Close
Set recOther = Nothing

End Sub



--------------------
All open code examples and free projects are only available from: Database Dreams
Go to the top of the page
 
Hugoj
post Aug 24 2019, 11:56 AM
Post#14



Posts: 29
Joined: 22-August 10



Thanks.
I will check next week. Finishing work today.
Go to the top of the page
 
Hugoj
post Aug 26 2019, 12:52 AM
Post#15



Posts: 29
Joined: 22-August 10



I have found the solution now.
The new character should be Chr(11). So the code should look like this:

Nytext = Replace(prisData(I, 4), Chr(13) & Chr(10), Chr(11), 1, , vbBinaryCompare)
Go to the top of the page
 
theDBguy
post Aug 26 2019, 10:38 AM
Post#16


Access Wiki and Forums Moderator
Posts: 76,290
Joined: 19-June 07
From: SunnySandyEggo


Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th September 2019 - 05:13 PM