?
Solved

Parse Memo Field from linked table

Posted on 2009-04-09
2
Medium Priority
?
256 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 2000 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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

831 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