Solved

Parse Memo Field from linked table

Posted on 2009-04-09
2
239 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
Comment Utility
replace the { : } with {= } sign

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




0
 

Author Closing Comment

by:rkmilam
Comment Utility
Worked great.  Thanks.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
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 how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

772 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

9 Experts available now in Live!

Get 1:1 Help Now