Solved

Parse Memo Field from linked table

Posted on 2009-04-09
2
242 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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

821 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