• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

Parse Memo Field from linked table

I have created a linked table in my database.  The table is linked to a MS Outlook e-mail folder.  One of the fields in the linked table is called tbxContents.  This field contains data that was submitted via a webform.  The contents of the field look like this:

Company=Millicent Pty Limited
Name=Terry L. Farmer 1
EmailAddress=tfarmer@millicent.com 1

I have created a form that shows the contents of my linked table.  I would like to create a button on this form that parses and writes the values following the "=" sign of each line above to their own field on a sub form in a separate table.

Months ago, I was provided with code from EE that worked great.  Now, however, the e-mail form that I receive contains an additional bit of information (REMOTE_HOST:  The EE code will not process (Run time error 5)  with this new line of data (presumably b/c the last line of data does not contain an "=" sign??).  I'm not sure if it will be easier to modify the attached code or ask for something new?

Thanks for your help.
Ken Milam

Private Sub Command40_Click()
Dim myFlds As String, myVals As String, mysql As String, myArray As Variant
myArray = Split(Me!tbxContents, vbCrLf) 'array element per line of tbxContents
For i = 0 To UBound(myArray)
  myFlds = myFlds & Left(myArray(i), InStr(myArray(i), "=") - 1) & ", "
  'the single quotes in the next line denote text fields
  myVals = myVals & Trim("'" & Mid(myArray(i), InStr(myArray(i), "=") + 1)) & "',"
Next i
myFlds = Left(myFlds, Len(myFlds) - 2) 'remove trailing comma space
myVals = Left(myVals, Len(myVals) - 1) 'remove trailing comma
myVals = myVals & ")"
mysql = "INSERT INTO BikeFlightLegit ([From], To, Received, Created, HasAttachments, " & myFlds & ") Values ('" & Me!From & "', '" & Me!To & "',#" & Me!Received & "#,#" & Me!Created & "#,'" & Me!HasAttachments & "'," & myVals & ";"
MsgBox mysql
DoCmd.SetWarnings False
DoCmd.RunSQL mysql
DoCmd.SetWarnings True
End Sub
INSERT INTO BikeFlightLegit ( [From], [To], Received, Created, [Has Attachments], Company, Name, Address1, Address2, City, State, Zip, Country, EmailAddress )
VALUES ('', 'webhosting-userform@bikeflight.com', #8/22/2008 16:18:17#, #8/22/2008 15:48:33#, '0', 'Millicent Pty Limited', 'Terry L. Farmer 1', 'Address1', 'Address1', 'City1', 'State1', 'Zip1', 'USA', 'tfarmer@millicent.com 1');

Open in new window

1 Solution
Rey Obrero (Capricorn1)Commented:
replace the { : } with {= } sign

myArray = Split(Me!tbxContents, vbCrLf) '

rkmilamAuthor Commented:
Worked great.  Thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now