How extract certain parts of data from memo and make it a variable?

[ryaanmichael]

VBA question for Access 2010:
I have a MEMO field in a form where users will paste a few lines of information. The structure/characters of the pasted information will always be in the same format, however the data will change (For example, 05/12/2012 may be 01/01/2013, but it will always be in the format of 99/99/9999. The information will also always be in the same order.(For example: the word "amount" will always come before $0.00.) There are multiple lines in the information that contain currency, dates, categories, etc. How can I use VBA to read the entire set of information that is pasted in the MEMO field, but set certain parts of the information to Variables. I want to call the extracted variables and place them in certain form fields.

My goal is to have VBA code read through the few lines of information and separate the data into different fields without the USER manually doing it.

Thanks for your help!

[zmbd]

Courious, why are you using a memo field for this information instead of seperate fields within the data table?
-z

[ryaanmichael]

zmbd- Because of the amount of data and frequency of input, my goal is to have VBA code read through the few lines of information and separate the data into different fields without the USER manually doing it.

[zmbd]

Once again... I still don't understand the use of the memo field.

The reason I propose for the separate fields is to help ensure that the data is entered into the correct fields all of the time... what happens if the user enters the "Amount" first when the date is supposed to be first?

I have several very picky users in my work-group.... I have forms with as few as 1 or 2 controls and as many as a dozen... the entries number from one or two a year to somewhere in the hundreds per day... the users are happy and the data can be verified; thus a properly designed form should make your life SO much easier and the users shouldn't have an issue as the "learning-curve" should be fairly easy.

Can you give me an actual example of the user's data entry, obviously using some "Safe" data.

[Mihail]

If the data in your memo field have the same format (I understand: length and words positions) must be easy to design code to extract certain information by simple using MID() function.
Of course, depending your data, can be other (better) approaches.

I have a question:
You say that the user paste data in this field. So, the information is, somewhere, in your computer before the user copy it.
Are you think about using directly the source ? (Avoiding the copy-paste process and even avoiding to store in your database the memo field ?)

[ryaanmichael]

Hi ZMBD and MiHail- Thanks for your responses. The information they will copy and paste will always be in the same format/position, so there will be no issue with the information being in a different order. Here is an example of what they will copy:

5/1/2012 Category1 $99.99
5/1/2012 Category2 $99.99
5/1/2012 Category3 $99.99
5/1/2012 Category4 $99.99
5/1/2012 Category5 $99.99

As you can see the data will always have the date, then tab, then the Category, then tab, then the dollar amount. Then a new line will be a different field containing data for category 2.

The user will copy all the data from a website as if it were a paragraph. Then they will paste it in a form field, and it will read through the code and organize it into the respective fields.

I chose MEMO field, because I'm not sure what would be a better option for this type of task. Thank you for your help!

[ryaanmichael]

MiHail- I have them manually pulling the data because it will be available online, but not always at the same time/date. So I figure it would be better to just go to the site and copy the info.

[zmbd]

@ryaanmichael

Text String:
If this is a tab delimited string then something along the following:
http://msdn.microsoft.com/en-us/libr...ffice.10).aspx
might work... it reads like stereo instructions. I found this while looking for a solution to a somewhat similar problem.

It would be nice if I could help you with automating the update; however, I'm not sure how one would open the web-page and then pull the data.
-z

[TheSmileyCoder]

I have created a small database that should serve fine as an example of how it can be done. Depending on your regional settings you may need to make small changes to how the text is parsed.

The sample contains a table to store the parsed information, based on your example data. It also contains a main form handling the processing, as well as a bound subform to display the parsed data.

Note that I don't use a memo field, I simply use a unbound textbox on the unbound main form, to paste the data into. I use the textbox OnChange event to trigger the processing. You may want to switch it, so it only happens on button click, but thats up to you.

Code behind the form, in case someone does not want to open the attachment:

Option Compare Database
Option Explicit
 
Private Sub Form_Open(Cancel As Integer)
   'Delete any records in temporary table
      CurrentDb.Execute "DELETE * FROM tbl_Parsed"
 
End Sub
 
 
 
Private Sub tb_ParseInput_Change()
   On Error GoTo err_Handler:
   Dim strInput As String
   'Get pasted string
      strInput = Trim(Me.tb_ParseInput.Text) 'Important, use Text, not value
 
   'Remove any linebreaks that preceeds parse info
      Do While (Left(strInput, 1) = Chr(13) Or Left(strInput, 1) = Chr(10))
         strInput = Trim(Mid(strInput, 2))
      Loop
 
   'Store each row in an array
      Dim strRecords() As String
      strRecords = Split(strInput, Chr(10)) 'May have to replace chr(10) with chr(13)
 
   'Check that info is present
      If UBound(strRecords) < 1 Then
         MsgBox "No valid information pasted", vbOKOnly + vbInformation
         GoTo exitSub
      End If
 
   'Open recordset so we can store data
      Dim rsParse As DAO.Recordset
      Set rsParse = CurrentDb.OpenRecordset("tbl_Parsed", dbOpenDynaset)
 
   'Loop through strRecords adding each record to table
      Dim intI As Integer
      Dim strRecord() As String
      For intI = 0 To UBound(strRecords) - 1
         strRecord = Split(Trim(strRecords(intI)))
         rsParse.AddNew
            rsParse!dt_Date = strRecord(0)
            rsParse!tx_Category = strRecord(1)
            rsParse!cur_Amount = Replace(strRecord(2), "$", "")
         rsParse.Update
      Next
 
 
 
exitSub:
   On Error Resume Next
      rsParse.Close
      Set rsParse = Nothing
      Me.tb_ParseInput = Null
      Me.ctrlSubform_DisplayParsedInfo.Requery
   Exit Sub
 
 
err_Handler:
   MsgBox "An error has occured" & vbNewLine & Err.Number & " - " & Err.Description, vbOKOnly
   Resume exitSub
 
End Sub