Array Loop - Need Help

[T.Smith]

Hi everyone!

I really have no experience in Arrays and they are very confusing. I' would like to change this code (total of 20 if statement) and make it loop. Where I'm running into a problem is using an array in a concatenated string starting at the last position and moving backwards. Any help would be greatly appreciated as this code has been boggling my brain for weeks. Thanks!

Code that currently works but is very long.

Function NoteBlock$(NoteLine1, NoteLine2, NoteLine3, NoteLine4, NoteLine5, NoteLine6, NoteLine7, NoteLine8, NoteLine9, NoteLine10, NoteLine11, NoteLine12, NoteLine13, NoteLine14, NoteLine15, NoteLine16, NoteLine17, NoteLine18, NoteLine19, NoteLine20)

    Dim A1, A2, A3, A4, A5, A6, A7, A8, A9, A10, A11, A12, _
       A13, A14, A15, A16, A17, A18, A19, A20, CR$, CU$
    Dim strCount As Integer

    CR$ = Chr(13) & Chr(10)  'Carriage return and line feed

strCount = 0    ' From the bottom we will go up the lines to find the
                ' first value. When we get the first value this will change to 1
    
    ' This is 1 of 20 if statements
    If strCount = 0 Then    'Test line 20
        If IsNull(NoteLine20) Then
            A20 = ""
        Else: A20 = NoteLine20 & CR$
            strCount = 1
        End If
    Else
        If IsNull(NoteLine20) Then
        A20 = CR$
        Else: A20 = NoteLine20 & CR$
        End If
   
    ' and the start of NoteLine19 If statement and so on.

     NoteBlock = A1 & A2 & A3 & A4 & A5 & A6 & A7 & A8 & A9 & A10 & _
     A11 & A12 & A13 & A14 & A15 & A16 & A17 & A18 & A19 & A20

Thanks.

 

[ajetrumpet]

Smith,

where are the NoteLine values evaluated from? What are they?

Looks like you can use the array function for a variant here. (MyArray = Array('A' Values)

 

[ChrisO]

See if something like this formats it correctly: -

Public Function NoteBlock(ParamArray vntArgList() As Variant) As String
    Dim blnFound As Boolean
    Dim intIndex As Integer
    Dim strTemp  As String
                     
    For intIndex = UBound(vntArgList) To LBound(vntArgList) Step -1
        If blnFound Then
            If IsNull(vntArgList(intIndex)) Then
                strTemp = vbNewLine & strTemp
            Else
                strTemp = vntArgList(intIndex) & vbNewLine & strTemp
            End If
        Else
            If Not IsNull(vntArgList(intIndex)) Then
                strTemp = vntArgList(intIndex) & vbNewLine & strTemp
                blnFound = True
            End If
        End If
    Next intIndex
    
    NoteBlock = strTemp
                     
End Function

Hope that helps.

Regards,
Chris.

 

[T.Smith]

Adam,

The notelines are evaluated when a report opens. One of the reports recordsource is a table containing 20 fields (NoteLine1, NoteLine2, ect.) On the report, there is one textbox with its control source set to =NoteBlock([NoteLine1],[NoteLine2] ect). The textbox is set to grow for the various notes. What I'm trying to do is keep the spacing in the notes "if" there is and shrink everything after the last line the user entered data one. Do I have to declare each textbox in an array and how do i join 20 array values based on a 20 textboxes into a string? Any help would be greatly appreciated.

 

[T.Smith]

Chris,

Sweet! That works perfect. I don't know how long I've been trying to get something like that to work. Thanks for your help!

Toby