Insert tab space when text field grows to 2nd line 2

[osurfc]

I am working on an application with a text field width set to 160. The report width for that text field is set to 50 and can grow property is yes. Is there a way to indent the second and following lines by 5 spaces.

example of the text breaking would look
like this. Which is how my users
want the text to appear.

I have tried embedding spaces in the field to give me the desired effect but Access changes the breakpoint making that useless.(not to mention labor intensive for maintenance).
Thanks for any help/advice.

 

[snyperx3]

You can tell it where to break. Say at 40 chars add a vbCrLf and five spaces.

-Pete

 

[osurfc]

Pete,
Where in the coding would I put that and what would be the syntax?

Mark

 

[snyperx3]

You would do it in the VBA, on format event of whatever section that field is in. Make the field unbound.

I haven't worked on it too much but this will give you a start:

Public Sub SplitIt(strfield As String)
Dim SplitString As Variant
Dim i As Integer
i = 0

SplitString = Split(strfield)
strfield = ""
Do Until Len(strfield) >= 40 Or i = UBound(SplitString)
strfield = strfield & SplitString(i) & " "
i = i + 1
Loop
If i <> UBound(SplitString) Then
strfield = strfield & vbCrLf & " "
End If
End Sub

-Pete

 

[osurfc]

Pete I'm not a programmer so bear with me on this thread..
Why should the field be an unbound object as opposed to bound? I have started coding in the section for the control using the on formatting event. Having never worked with arrays I am completely lost with your coding. I am tring to read up and find some simple examples to work with but in the mean time can you describe what is happening in each segment of the code
you have provided.

thanks

 

[snyperx3]

Sure!

You want to use an unbound object because what we are displaying will not directly reflect what is in the field. It will be altered slightly.

My Code:

[green]'i made this a public sub routine, it can just as
'easily be put inside the onformat event[/green]
Public Sub SplitIt(strfield As String)
    [green]'this will contain the array of words[/green]
    Dim SplitString As Variant

    [green]'this is the index for the array starting with zero[/green]
    Dim i As Integer
    i = 0
    
    [green]'Split is a VB function that splits a string
    'based on a delimiter into an array. the default
    'delimiter is a space. You could split on a comma 
    'by using Split(strfield,",")[/green]
    SplitString = Split(strfield)

    [green]'reset the string to empty[/green]
    strfield = ""

    [green]'we want the first line to be around 40 chars long
    'and we cant reference the array past the last element
    'UBound(array) is the index of the last element in an array[/green]
    Do Until Len(strfield) >= 40 Or i = UBound(SplitString)

        [green]'store the next word into the string with
        'all the previous words before it[/green]

        strfield = strfield & SplitString(i) & " "
        [green]'increment the index[/green]
        i = i + 1
    Loop

    [green]'if we didnt get the last element then we need to
    'add a new line, five spaces so the next line will be
    'set up correctly[/green]
    If i <> UBound(SplitString) Then
        strfield = strfield & vbCrLf & "     "
    End If

    [green]'finally at the end, insert the text into the field[/green]
    txtSentence.Value = strfield
End Sub

Note: This is not done exactly as it should be. This is just a starting point...the only thing you need to add is a loop to go back and make the next line (which would be only 35 long because of the added spaces), and next line and so on. I'll look at it more in a bit if you need more help.

-Pete

 

[snyperx3]

Alright. Here is the correct, and working way to do this. Ive tested it.

Create a module. Call it whatever you want modFunctions or something like that. Put this code inside:

Public Function SplitIt(strField As String, intRowLength As Integer) As String
    Dim SplitString As Variant
    Dim i, intLineCount As Integer
    Dim strFinal As String
    intLineCount = 1
    
    SplitString = Split(strField)
    strField = ""
    For i = 0 To UBound(SplitString)
        strField = strField & SplitString(i) & " "
        If Len(strField) >= intRowLength And i <> UBound(SplitString) Then
            strFinal = strFinal & strField & vbCrLf & "     "
            strField = ""
            intLineCount = intLineCount + 1
            If intLineCount = 2 Then
                intRowLength = intRowLength - 5
            End If
        End If
    Next
    If strField <> "" Then
        strFinal = strFinal & strField
    End If
    SplitIt = strFinal
End Function

It does the same thing above, but takes care of the portions i left out.

Now on your report. Your unbound textbox should be set to can grow, and your ControlSource should be:

=SplitIt([myfield],40)

replacing myfield with whatever your field is called.

-Pete

 

[osurfc]

Wow I am not sure what to say except thank you. I by no means had envisioned you writing the code for me. I do appreciate the step by step notatation of what is going on. That helps me considerably in growing my knowledge base. I had envisioned this small piece of the project being my "night job" for a while.
Thanks again
Mark