Debug.Print to Form

[Cody.K]

First and foremost, thanks for taking the time to check out my thread. What I have is a "volunteers database", which stores info on individuals and organizations who volunteer at the local homeless shelter. They receive their "volunteer applications" via email from their website, which doesn't support server side scripting.

What I've done is:
-Setup rules in Outlook 2013 to move these emails to a folder in the Inbox.
-Linked MS Access 2013 to said folder.
-Queried pertinent data.
-Created form and set the query as it's data source.

This is the way the emails are stored in the database:

The field name is Contents.
Data Type: Long Text
Text Format: Plain Text

You've just received a new submission to your VOLUNTEER FORM.



Submitted Information:

Name
John Doe

Phone Number
555 - 555 - 5555

Address
1510 Lovely LN, Apt 206
Dirty Myrtle, SC United States 29577

Email
volunteersemail@gmail.com

Church Or Organization Affiliation
Not Affiliated

How would you like to volunteer? (choose all that apply).Kitchen/Food Prep
1

How would you like to volunteer? (choose all that apply).Maintenance
1

How would you like to volunteer? (choose all that apply).Mentoring
1

How would you like to volunteer? (choose all that apply).Job Skills
1

How would you like to volunteer? (choose all that apply).Front Desk Receptionist
1

How would you like to volunteer? (choose all that apply).Painting
1

I'm attempting to "clean this up" so that I can append the information to [another table]:

Name would be parsed to [FName] and [LName], respectively.
Phone number to [PNumber]
Address to [Address], [City], [State], and [Zip]
and so on and so forth.

This is the code I have, thus far:

Private Sub cmdCleanUp_Click()
    Dim Contents As String
    Contents = Me.Contents
    
    Dim i As Integer
    Dim var
    
    Contents = Replace(Contents, "You've just received a new submission to your VOLUNTEER FORM ", "")
    Contents = Replace(Contents, "<link> .", "")
    Contents = Replace(Contents, "How would you like to volunteer? (choose all that apply).", "")
    var = Split(Contents, vbCrLf & vbCrLf)
    
    For i = 0 To UBound(var)
        Debug.Print var(i)
    Next
End Sub

The problem is that I'm not getting any output with "Debug.Print".

My question is: How do I go about printing (Debug.Print var(i) ) to either "Me.Contents" or an unbound text box?

 

[pr2-eugin]

The problem is that I'm not getting any output with "Debug.Print".

Suggests that there is nothing in the var Array.. However let us properly declare the array and see if it helps..

    Dim i As Integer
    Dim var[COLOR=Red][B]() As String[/B][/COLOR]
[COLOR=Green]:
'Your Normal Code[/COLOR]
    var = Split(Contents, vbCrLf & vbCrLf)
    Debug.Print "The Size of the Array is : " & UBound(var)
    For i = 0 To UBound(var)
        Debug.Print var(i)
    Next
End Sub

So if you get this as the Result..

The Size of the Array is : 0

Then the Split function returned nothing.. I mean the data does not have two vbCrLf in it..

 

[mdlueck]

The problem is that I'm not getting any output with "Debug.Print".

Press Ctrl+G to see the Immediate window. See if your output is showing up there.

My question is: How do I go about printing (Debug.Print var(i) ) to either "Me.Contents" or an unbound text box?

Do you really want output being directed to the Immediate window? Likely not. Debug.Print sends output there.

To send output to an unbound text field control, simply send string contents to the control's Value, Ex:

Me.fldtitle.Value = "This is a way cool test!"

Places into a text field control named fldtitle the static text. You may also transfer a string VBA variable to the text field control .Value property.

 

[Cody.K]

The Size of the Array is : 0

Then the Split function returned nothing.. I mean the data does not have two vbCrLf in it..

I made the adjustments you suggested and it's still not returning anything to the immediate. I assume this is because there are two vbCrLf's, but I'm probably wrong

 

[pr2-eugin]

Immediate window (after you click CTRL+G on the VBA Editor) does not even print this line?

The Size of the Array is : 0

If it does not print then the code is not executed at all..

 

[Cody.K]

To send output to an unbound text field control, simply send string contents to the control's Value, Ex:

Me.fldtitle.Value = "This is a way cool test!"

Places into a text field control named fldtitle the static text. You may also transfer a string VBA variable to the text field control .Value property.

Thanks for the reply, mdlueck. As you can probably already tell, I'm new to this. I'm hoping you wont mind walking me through this:

Private Sub cmdCleanUp_Click()
    Dim Contents As String
    Contents = Me.Contents

    Dim parsed As String
    parsed = Me.fldtitle
    
    Contents = Replace(Contents, "You've just received a new submission to your VOLUNTEER FORM ", "")
    Contents = Replace(Contents, "<link> .", "")
    Contents = Replace(Contents, "How would you like to volunteer? (choose all that apply).", "")
    parsed = Split(Contents, vbCrLf & vbCrLf)
    
End Sub

This, obviously, isn't going to work. I'm getting an "invalid use of null" at:

parsed = Me.fldtitle

when trying to send my parsed data to the field

 

[Cody.K]

(after you click CTRL+G on the VBA Editor) does not even print this line?

The Size of the Array is : 0

If it does not print then the code is not executed at all..

That's right.. Maybe you can help me with the question I posed to mdlueck....

 

[pr2-eugin]

You are clicking the button cmdCleanUp right? If you did click then the Immediate window should atleast have the line The size of the array : 0..

Regarding the Error Invalid Use of Null.. You have declared the variable as String, which is incapable of handling Null values.. So you should use Nz() to trap Null values..

parsed = Nz(Me.fldtitle, vbNullString)

However, if you need to set the content of the String Contents (which I would advice to change it as ContentsStr) to an Unbound control of the form it would be the other way..

Me.fldtitle = var(i)