Solved

Parse Memo Field from linked table

Posted on 2009-04-09
2
240 Views
Last Modified: 2013-11-28
Hi,
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
Address1=Address1
Address2=Address1
City=City1
State=State1
Zip=Zip1
Country=USA
EmailAddress=tfarmer@millicent.com 1
REMOTE_HOST: 198.12.16.4

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: 198.12.16.4).  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

0
Comment
Question by:rkmilam
2 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 24106744
replace the { : } with {= } sign

Me!tbxContents=Replace(Me!tbxContents,":","=")
myArray = Split(Me!tbxContents, vbCrLf) '




0
 

Author Closing Comment

by:rkmilam
ID: 31568491
Worked great.  Thanks.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now