Solved

Parse Memo Field from linked table

Posted on 2009-04-09
2
244 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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.

734 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