copy and paste (1 Viewer)

[SAMZIE]

Hi all,
I get information sent to me on email. This information then needs to be transfered onto a database. the information is always the same.
name *****
Date of birth **/*****
consultant ******
ward BLA ******

Is it possible to copy and paste all the information in one go from the email and paste everything into its correct places on a form with one action? This would save alot of time & effort on the users part.

Many Thanks

Samzie

 

[KeithG]

I believe you could make an Outlook form and tie the fields on the form to fields in you database. Instead of email you could create a Data Access Page in Access and instead of sending an emai the person could enter the info into the page which in turn would enter the info directly into the database.

 

[SAMZIE]

thanks very much, kieth i really wish its was that simple, perhaps i should eleaberate a little.

we have around fifty wards who email requests for patients records to a medical records library. they each can send around 10 requests per day each. none of the wards have access. the cost of each license is £250. then take into account training hundreds of staff and maintaining the database. its just not feasable.

agian all i want to do is copy all the info from outlook and paste it into the correct places in one swoop. instead of manualy typing the information. ive tried copy and paste each individual line but it takes as much time as typing it in.

many thanks

Samzie

 

[KeithG]

Do the people that send the email have access to the same network as you? With Access Pages i don't believe thy actually have to have access installed on there computes, they just need to be able to access the web page

 

[SAMZIE]

Sadly no, we use a shared drive to keep the access file in. this shared file is only accessable by a few people for data protection. we are bound by the data protection act and we have to ensure that very few people can access the information we generate. for instance. if you had a daughter who came to hospital where you worked, you cannot even tell your wife. it really is that crazy so information generated can only be accessed by the people who use it.
i can see what your thinking but, trying to ask nurses and doctors to use a different way of using the computer would bring the hospital to a halt. i work with people who can just about turn a computer on. asking hundreds of people to do something new would cause crisis at the hospital. i dont want that, i just wanna copy and paste.

 

[JimmyK]

It is possible to do something like you want. I have done something similar at my work to retrieve email addresses (in bounced email reports) in an email folder.

I will take a look at my code on Tuesday when I get back to work and see if it can help you out.

 

[SAMZIE]

thanks i'd be really greatfull

Samzie

 

[ghudson]

You have no way of controling the way the users would submit and format their data to you. You are asking for the impossible.

If you secured your db with Access security and created a custom workgroup and assigned each group [persons] user permmision you can control what the users [groups] can access.

You can setup you data form for "data entry" only so that they can only key [create] a new record, not view other records.

You can distribute the runtime version of Access to those who do not have the retail version of Access. The developers edition of Access gives you the license

to distribute as many royality free copies of the runtime version of Access. Obtain and deploy the Access 2003 runtime

Using Microsoft's InfoPath might be another option for the users to create and submit data that the db can import.​

 

[rockman]

Samzie,

I don't think that you are "asking for the impossible." Your initial question is succinctly stated and certainly doable.

Create textboxes for the name, DOB, consultant, ward fields.
Create an unbound textbox on the form called txtRawText and attach the following code:

Private Sub txtRawText_Change()
Dim vTextLines() As String
  vTextLines = Split(txtRawText.Text, vbCrLf)
  If UBound(vTextLines) < 3 Then
    MsgBox "Invalid data entry"
  Else
    txtName = Trim(Mid(vTextLines(0), Len("name") + 1))
    txtDOB = Trim(Mid(vTextLines(1), Len("Date of birth") + 1))
    txtConsultant = Trim(Mid(vTextLines(2), Len("consultant") + 1))
    txtWardBLA = Trim(Mid(vTextLines(3), Len("ward BLA") + 1))
  End If
End Sub

The error checking leaves a lot to be desired but you can spruce that up to your needs. Give it a whirl.

HTH,
Jeff

 

[SAMZIE]

Hi Rockman,
Thanks very much for the post and the code you produced for me but it goes beyond my current knowlege of access. I've tried for an hour to replicate what you posted but I must be missing something.

Would it be possible to make me a small db with the code you have made so I can have a look how you have prepared it. Once i understand i will be able to transfer this to the main database.

i hope you dont think im taking a liberty or asking to much but it would help us so much.

Many thanks

Samzie

 

[rockman]

Try this:

Samzie,

I hope the attached DB makes it more clear.

Jeff

 

[ghudson]

Rockman,

What if the user emails only the data?

John Doe
7/4/1776
McKinsey & Company
BLA 1A-482

What if the user emails the data in a differnet format?

Doe,John
July 4, 76
Company, McKinsey & 
1A,482 - BLA

What if the user emails the data with a different header?

Patient John Doe
DOB 7/4/1776
company McKinsey & Company
Section BLA 1A-482

What if the user emails the data with a different header and in a differnet format?

Patient Doe,John
DOB July 4, 76
company Company, McKinsey & 
Section 1A,482 - BLA

Too many what ifs and I only mentioned a few possible problems. You can not control how or what the user sends when you allow them to free text the data in the body of an email. Trust me, unless you have a process in place that forces them to submit their data in a specific [set] format you will have more headaches with each received email with what you are trying to attempt. You will wind up hand keying or constantly editing the submitted data and then you increase the chances that you might make a mistake. Your data is worthless if you can not guarantee the results and the integrity of the data.

 

[rockman]

I fully understand the point that you are making ghudson, but the original question states "the information is always the same".

Certainly, you can't guarantee that the information will be perfect and hence my earlier comment:

The error checking leaves a lot to be desired but you can spruce that up to your needs.

But what guarantees are you looking for. I have a simple text box on one of my programs entitled "Name". I have a label that says enter the name in the format "Last Name, First Name". I've had to incorporate error checking to make sure there is a comma in the entry because occasionally the one-and-only data entry person would enter the information as "First Name Last Name". Clearly, there are no guarantees... but I digress.

 

[SAMZIE]

the form doesnt move the text into the fields for me. i think there must be something wrong with the code.

you are right rockman. the information is always in the same format everytime. information is collected from text fields on a specialist database then transfered onto outlook automatically.

hope this helps

 

[rockman]

I just tried the database on my computer and the text gets put into all the right places.

To work the demo: You need to select all the text in the textbox to the right, copy that text (ctrl-c), and then move your cursor to the textbox on the left, and paste (ctrl-v). The data should then automatically populate the fields below. At the very least you should get an error message box to pop-up if you do something wrong.

Try this as a test: Type a any character in the "Paste Text Here" box. It should pop-up the error message box.

Let me know if you still have problems.

Jeff

 

[KeithG]

How many emails do you receive daily? Do all your emails need entered in the database? You can create a table in your database that is linked to your inbox and will contain a field with the message in it. I thought you might be able to link your inbox to a database table and use a set of queries to format and append the data.

 

[SAMZIE]

i paste the text in, press enter and the cursor moves back to the original box,
i press enter again and it moves to the name field and so on. anyone else tried this?

 

[rockman]

This may sound like a silly (circular) question but, when you attempt to paste the text into the "Paste Text Here" textbox, does the text appear in that box? Your chain of events occurs to me when I place the cursor in the "Paste Text Here" textbox, DON'T paste anything, and press the Enter key.

By the way, you shouldn't have to press the Enter key at all. The mere pasting of the text will (should) trigger the txtRawText_Change event.

Check to make sure the txtRawText On Change property has the words "[Event Procedure]".

I would be very interested to hear from others that have downloaded this file if it is working on their machines.

 

[ghudson]

the information is always in the same format everytime. information is collected from text fields on a specialist database then transfered onto outlook automatically.

The data exists in one database, you export that data via email, then you want to extract the data from the email and back into your database.

Why not extract the data directly from the specialists db and into your db?

Or, Why not create an output function that saves a file onto the server [from the specialists db] and your db imports that file?

Or, Why not create an output function that emails the output file to you and you import the file into your db?

 

[SAMZIE]

it works! whooooooooooooooooooohooooooooooooooooooooo!
yessssssssssssssssssssssssssssssssssssssssssssssssssss!
rockman is god!
i'll start work on the database tommorow!
i'll keep you informed how it goes! YES! YES! YES!