-
My first question is specifically related to Norie's code. Presently the format within the memo field shows the data in a straight line as follows:
Account No.: 025708, customer's Name: John Smith, Bill Payment Method: Weekly, Last Update: 06-June-2006,
customer's Name: John Smith,
Bill Payment Method: Weekly,
Last Update: 06-June-2006,
The problem occurs when I attempt to clean up the appearance of the data in the memo field and place each reference on its own row by selecting the Ctrl and Enter key. When the data looks like the following I get an error message.
Account No.: 025708,
customer's Name: John Smith,
Bill Payment Method: Weekly,
Last Update: 06-June-2006,
Does anyone know why this is occurring? The need to clean the data layout is to make it much easier for a user to enter and update the data.
My second question is related to OBP's code. Is there a way to drop the "," at the end of each field once it has been parsed to its corresponding field. For instance the user currently will see the following:
025708,
John Smith,
Any help is appreciated. Thank you in advance.
-
Hi again John, I am sorry, I didn't realise that the Comma was showing up in the field.
Has the database changed much since I did the previous work?
What is the error message that you get?
Last edited by OBP; 07-05-2006 at 09:06 AM.
-
John, the Code to parse without the commas is actually under the command button code in the original database but here it is -
[vba]
Dim count As Integer, count2 As Integer, text As String, field(4), _
length As Integer, fieldcount As Integer
fieldcount = 1
text = Me.comments
length = Len(text)
For count = 1 To length
If Mid(text, count, 1) = ":" Then
For count2 = count To length
If Mid(text, count2, 1) = "," Then
field(fieldcount) = Mid(text, count + 2, count2 - (count + 2))
Exit For
End If
Next count2
fieldcount = fieldcount + 1
End If
Next count
Me.AccountNo = field(1)
Me![Name] = field(2)
Me![Payment Method] = field(3)
Me.Updated = field(4)
[/vba]
I have removed the message boxes.
-
OBP your updated code did the trick. It works perfectly. I never did receive an error with your code. The error I received was when I used Norie's code. Norie's code works perfectly provided I keep all my information in a straight line. I get the error message the second I seperate the information onto seperate lines by pressing the Ctrl and Enter key and then running the code within the query.
Last edited by brorick; 07-06-2006 at 07:22 AM.
-
Her code?
-
I am sorry. "Her code" is a typo. I have since corrected the entry.
Last edited by brorick; 07-06-2006 at 08:28 AM.
-
Parse Data from One Table to Another
Hello OBP. Using your example I am trying to design the following attached database so that the user can parse data from two seperate memo fields in one table on a main form to fields in another table on the subform.
I was not sure how to modify the existing code to accomplish this. I would like to provide the flexibility to permit the user to parse from multiple memo fields if needed (Example 1 up to 10 memo fields) if necessary.
-
Try this, I just aded a second button for the second memo field, it seems like the easiest way.
If you want to do lots of memo fields I would suggest an Option group to let the user select which memo field to parse and just have on button with as many sets of VBA as necessary. I will help you again when you get all the memo fields that you want to work with. WE can add individual buttons to test each one seperately and then put it all together afterwards.
-
OBP. I will give it a try. Thank you.
-
OBP. Is it possible to do away with the button and have the code run at the beggining when the form is opened. If the database contains 20 records then all records would be automatically parsed as described in the current code to the appropriate table and fields. This would free up the user to just view the end result.
-
Can you email me with what the purpose of the database and this part of in particuar is?
-
Sample Parsed Database
Tony,
The first database I provided to you was just a demo. This attached copy is closer to the working database.
Note: The parsed data tab contains a subform that is not yet properly synched up with on the main form. Therefore to parse the changes made to the other tabs, you need to make sure the subform is reflecting the same record number as the main form and then click on the parsed record button twice. Please let me know if this sample database helps. Thank you.
-
THis is where I am currently, I am just creating your the code in the form's on Current event procedure.
-
Give this version a try and let me know what you think. I made some record changes in the memo fields and the VBA picks them OK.
-
Can you test this version for me.
-
Parse Data from One Table to Another
Hello,
I have attempted to take a different approach with the parsing of data. I decided it would be more efficient and user friendly if all the parsing occurred behind the scenes. As a result I am utilizing a module and incorporating some of the fantastic code that OBP-Tony provided in the previous databases. Unfortunately I am not quite there. I would like to parse the data from each memo field in the tblImport table and paste it to the appropriate fields in the tblImportResults table. I am at a loss.
The following code worked on another test database, but I discovered that this would only work if the data in the memo field was in a straight line separated by commas. (Example: Mr., John, Doe, Big City, TX,). I want to convert this code to achieve the same results using the following memo format.
Salutation: Mr.
First Name: John,
Last Name: Doe,
City: Big City,
State: TX,
Public Sub isSplitValues()
Dim rst As DAO.Recordset, strSQL As String, i As Integer
Dim aseller As Variant
Dim arating As Variant
Dim afeedback As Variant
Dim aprice As Variant
Dim SQLString As String
SQLString = "DELETE * FROM tblImportResult "
DoCmd.RunSQL (SQLString)
Set rst = CurrentDb.OpenRecordset("tblImport", dbOpenDynaset)
If Not (rst.BOF And rst.EOF) Then
rst.MoveFirst
Do
aseller = Split(rst("sellerID"), ",")
arating = Split(rst("feedbackrating"), ",")
afeedback = Split(rst("totalfeedback"), ",")
aprice = Split(rst("price"), ",")
For i = 0 To UBound(aseller)
CurrentDb.Execute "INSERT INTO tblImportResult ( " & "sellerID, feedbackrating, totalfeedback, price ) " & "SELECT '" & Trim(aseller(i)) & "', '" & Trim(arating(i)) & "', '" & Trim(afeedback(i)) & "', " & CCur(Trim(aprice(i))), dbFailOnError
Next i
rst.MoveNext
Loop Until rst.EOF
End If
rst.Close
Set rst = Nothing
MsgBox "done"
End Sub
Any help is greatly appreciated.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules