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
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: 18.104.22.168). 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.
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)) & "',"
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 & ";"
INSERT INTO BikeFlightLegit ( [From], [To], Received, Created, [Has Attachments], Company, Name, Address1, Address2, City, State, Zip, Country, EmailAddress )
VALUES ('', 'firstname.lastname@example.org', #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', 'email@example.com 1');