Collect Data by Email Help Please

[Anakardian]

If you use a template that access can pull out you would save a lot of coding.
I have attached an example you can look at to see what it would look like.
The example is using the legacy form fields.

In order for you to be able to fill out the form fileds, the form must be locked. There if no password so you can unlock it to see the properties for each form field.

A way to work with the form fields from access can be found here:
http://msdn.microsoft.com/en-us/library/office/aa140082(v=office.10).aspx

The only problem you can face is if someone decides to print and scan your document before returning it.

 

[Cat129]

Thank you, I will try and work through this and get back once I've figured it all out

 

[Anakardian]

You are welcome

 

[CJ_London]

Just a heads up - I have a client who I developed a similar functionality (but using Excel) everthing worked well until IT security decided that all emails received which contain macros or code would be deleted and not delivered.

Consternation all round as to why the system had stopped working - the workaround was to zip the attachment and add the zipped file to the email before sending.

Might be worth checking you don't have the same problem (just send yourself a macro laden attachment). If it is a problem, this link should help

http://accessexperts.com/blog/2012/02/06/zipandunzipfrommicrosoftvba/

 

[Cat129]

Hi CJ London

Thanks for the tip, I have checked with IT, and they come through our system fine.
Sent a test file with an embedded macro and code and it came through fine.

Glad I checked though, would have been heart breaking to do loads of work for it not to get through emails

 

[Anakardian]

The beuaty about using teh legacy form fields is that they are not code or anything like that.
We have the same IT rules and these go through without problems.

Since all the work done by code is from access, the word form should not contain anything that they will stop.
If needed, you can perform some types of calculations in the form fields but that can become pretty hairy.

 

[Cat129]

Oh, do i need to use the legacy bits, I've been doing things with the 2010 bits.

I guess I better change them

 

[Anakardian]

Using the legacy versions also allows users to use an earlier version of word.
The new versions can only be used in 2007+ versions of word.

 

[Cat129]

Ok, I am now very very confused.

I don't understand all the coding, this bit in particular,

If Not IsNull(Me!ReportsTo) Then
strSQL = "SELECT [FirstName] & "" "" & [LastName] AS Name FROM " _
& "Employees WHERE [EmployeeID]=" & Nz(Me!ReportsTo)
rst.Open strSQL, CurrentProject.Connection, _
adOpenStatic, adLockReadOnly
If Not rst.EOF Then
strReportsTo = Nz(rst.Fields(0).Value)
rst.Close

Am I right in thinking that at the beging it is taking the information from access from [FirstName] etc and then it is inputting that data into the word document when it states "Employees WHERE [EmpoyeeID] etc

 

[Anakardian]

As I read the code it is first checking to see if there is an data at all (if not isnull)
If there is, combine the first and last name where the employeeID is the same as the selected person

You may have better luck in following this example:
http://www.techrepublic.com/blog/msoffice/how-do-i-fill-word-form-fields-with-access-data/164
At least I found that on a bit easier to follow than the microsoft thing