Collect Data by Email Help Please

[Cat129]

Hi Everyone

I need some help understanding this please,

I have created a database and would like to collect data by email.

The database is on NCR's, so an NCR would be raised by someone within thecompany, they would fill in the details that they know and then send it to thesupplier for them to complete.

Previously this was all done in word but it’s getting silly so I am in theprocess of creating a database to simplify things and run reports etc.

I have created a form that the user fills in half of and then I would liketo have a button that will email this form to the supplier to complete.

Once they have completed it return it and it will append my database.

I have found how to send a completely blank form, but I need to haveinformation in the emailed form for the supplier to reply to.

I also need to enter the email address manually as the supplier is mostlikely different for each form therefore unable to pre-enter the emails foraccess to pull up.

Any help would be greatly appreciated.

Thank You

 

[Cat129]

Can Anyone Help?

 

[GaryPanic]

You are going to run in to problems (I imagine) the form you send may not show correct the other end ..

a simpler route ( simple may not be the best route) would be to have this in a xls basis and then import the data ?

you can then force x data field as requirements

-- just an idea

G

 

[CJ_London]

Hi,

I would look at the similar threads at the bottom of this post, but you may want to rephrase your question - the question implies populating Access with data from an email whereas your post wants to populate and send an email.

There a a multitude of threads on both subjects - this one may be close to what you are looking for
http://www.access-programmers.co.uk/forums/showthread.php?t=202278

If you look at the similar threads on this post you will find out more

 

[Cat129]

a simpler route ( simple may not be the best route) would be to have this in a xls basis and then import the data ?

We can't use an xls because the information is going to be sent to other bases that dont have excel.

I was hoping because access can send info as HTML everyone we email with a few exceptions can input into the form.

Thanks for the idea, but unfortunatly this wouldn't work

 

[Cat129]

Hi,

I would look at the similar threads at the bottom of this post, but you may want to rephrase your question - the question implies populating Access with data from an email whereas your post wants to populate and send an email.

There a a multitude of threads on both subjects - this one may be close to what you are looking for

If you look at the similar threads on this post you will find out more

Thank you, I've had a look at the thread and this doesn't really answer my question either.

I'll try and rephrase it a bit better

In a perfect world

The user will input some information into a form, they will then click a button and email that form to someone who will complete the rest of the form. When the form is sent back it will automatically update the relevent fields in the form.

The person who is completing the form would need to see the information the user input to know what to complete.

I would put a screen dump in that would explain it a lot better but the forum wont let me yet

 

[CJ_London]

Ok,

The form is an access form? - which can only be run within an Access application - it cannot be run on its own.

We can't use an xls because the information is going to be sent to other bases that dont have excel

and if this is true, they probably don't have Access (I'm surprised they don't have excel if they have word, it's pretty standard in the corporate world)

I'm not sure what you mean by NCR so I may be missing something about the form

when you say

I have found how to send a completely blank form

can you explain further - if the form is blank, can you complete some fields just to prove the principle?

 

[Cat129]

I'll try and get a screen dump

 

[Cat129]

Ok,

User will fill in
Customer Name
Customer PO
Product Cose
Description of Item
EF Number
Quantity of Items
NCR Owner
Description of Item
Description of Fault.

It would then be emailed to the offending supplier to complete the rest

 

[Cat129]

 

[Cat129]

The offending supplier would need to know the information at the begging of the form to complete the rest of it. They could then send it back and it would update my database.

When I say I can create a blank form that works, I mean using the built in collect data create email.

 

[CJ_London]

built in collect data create email

I'm sorry, I am not familar with this functionality, can you tell me more - like where is it? - I'm using 2010 so if it is a 2013 function I won't be able to help

 

[Cat129]

Hiya,

I'm using 2010

Go into the external data tab, on the far right there is a section called data collection, within this is a button called 'Create- E-Mail'.

This is access' automated system. However this doesn't work for me as is and im not clever enough to change the code.

 

[CJ_London]

Thanks,

Had a look but there is a bit of setting up to do for me to investigate further and I don't have the time right now.

I have determined you do need to have emails in Access, not outlook and you can only send tables or queries, not forms or reports.

I also can't see how you can automate this in VBA rather than using the manual option.

I suspect that the 'form' generated for the email will be very much like the standard datasheet view you get for tables and queries.

However to progress a bit further I suggest you create a query which details all the records where the data needs to be completed, including the blank fields - very simplistically it will be along the following lines:

SELECT * FROM tblNCR WHERE CorrectiveAction is NULL and AlreadySent = False

(assuming corrective action is a field to be completed by the supplier) AlreadySent is a new field which is set to true once the email has been sent so it is not sent again

This is the query you would then send to your suppliers. I would follow this routine manually initially to ensure it is populating as expected. Once you have that sorted, you can move on to looking for a VBA solution

 

[Cat129]

Hi CJ London

Sorry it took me a while to get back to you,

I've just created a query that pulls information from the table that relevant form is inputting data into, when I tried the to use Create Email, it came back to me with

You cannot collect data for action queries, SQL-specific querier, and parameter queries by using e-mail messages.

I dont understand the message, is it saying this because within the query it references a certain record?

Thanks for all your help so far, its been really good

 

[CJ_London]

You cannot collect data for action queries, SQL-specific querier, and parameter queries by using e-mail messages

It sounds like you are trying to use a query in your routine which either updates or makes a table.

If you have, you either need to change it to a select query or if you are updating/making a table, change your routine to use this table instead.

 

[Cat129]

I have one main table that holds all information, then smaller tables containing info for my combo box's.

There are then lots of queries that run for different things, and various forms that input data into the main table.

I'm not sure how I would do what you have suggested with my set up, or am I being a bit dim? excuse me if I am

 

[CJ_London]

you said

I've just created a query that pulls information from the table that relevant form is inputting data into

I'm assuming that is what you are trying to email and my comments in my last post referred to this query

 

[Anakardian]

If all parties have word, would it not be possible to use that as the carrier for the data?

Word has the option of inserting text fields which you can name and manipulate in various ways.
You can also "lock" the form so that you can only fill in the fields you have made.

 

[Cat129]

If all parties have word, would it not be possible to use that as the carrier for the data?

Word has the option of inserting text fields which you can name and manipulate in various ways.
You can also "lock" the form so that you can only fill in the fields you have made.

Hiya,

If I did this, can I get access to create the word document with information in it from a table, and when the word document is returned import the data into access?