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!
8 5037 zmbd 5,501
Expert Mod 4TB
Courious, why are you using a memo field for this information instead of seperate fields within the data table?
-z
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 5,501
Expert Mod 4TB
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.
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 ?)
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!
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 5,501
Expert Mod 4TB @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
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
-
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Scott |
last post by:
I have a text field that I am using in a query. It looks like this
"123456".
What I want to do is pull out the "1" "2" "3" "4" "5" "6" separately
and put it in a report. I can pull out the...
|
by: Sashafay |
last post by:
Hi,
How I can count certain word in memo field?
For example I have 15 words in one field and "PLUMING" word repeats in
it 3 times. So I would like my code give me number three as the
answer....
|
by: klall |
last post by:
Hello.
I need to extract date information from a memo field entered in the
following way: 01/01/2005 - 31/12/2005 01/01/2004 - 31/12/2004
01/01/2003 - 31/12/2003 01/01/1996 - 31/12/1996. The...
|
by: Fabian Braennstroem |
last post by:
Hi,
I would like to remove certain lines from a log files. I had
some sed/awk scripts for this, but now, I want to use python
with its re module for this task.
Actually, I have two different...
|
by: Sandeep Khandelwal |
last post by:
Hi All.
I want to extract and Load data from PostgreSQL using Libpq C API. Please let me know which approach will be good to load large number of rows into PostgreSQL(Insert or COPY FROM) and,...
|
by: mix01 |
last post by:
Hi,
I am trying to get some VBA code working, but am preplex as to why it does not work. I would really appreciate any level of help.
Many thanks,
Mix01
Version of the program
|
by: samiul |
last post by:
HI,
Here is a question that is , is it ever possible to pass data from PHP variable to Javascript variable? if yes then please let me know and if not why? I think it is possible...
Samiul haque
|
by: Computernut234 |
last post by:
Hi, i'm doing a project for my Java class and I know how to add text to a .txt document and remove the entire text but I do not know how to only remove certain parts of text. My code is supposed to...
|
by: neeru29 |
last post by:
I'm using Pcapy and impacket module for packet sniffer.
I'm able to capture the whole data in a variable and display it.
I want extract the IP addresses , Port no's and Payload data into separate...
|
by: Peter Thomson |
last post by:
I have a memo field with a mixture of numbers and text. I would like to add up all the numeric values to obtain a total. Numbers can be in the format 01 or 1, all numbers are no more than 2 digits,...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |