Creating A Submission Form

[aje35]

Hey All,
I have pretty extensive Excel skills and decent VBA skills so coding/logic is not where my problem lies. What I am trying to do is have some sort of digital form whether it be a Word document, Excel sheet, or Adobe pdf. The form will be sent out, filled out, and sent back from various entities. I want it to be user friendly (and idiot proof) so that only the editable fields can be changed including text boxes and multiple choice options. When this form gets sent back to me I would like to be able to implement some sort of VBA code to pull the data into Excel with columns being the various field entries and rows being the various submittals.

I understand I am not being very specific but thats only because I am leaving this open to interpretation. I know there must be many ways to approach this problem to I am looking forward to hearing some opinions.

Thanks for your input.

AJ

 

[paddydive]

If you are using MS Outlook then you can do this with the help of Access form

you can send a form to bulk of people and when the sent back the filled form
MS outlook automatically creats a personal folder. all you have to do is export it in excel.

I will get back you with the details if this is what you are looking for

 

[aje35]

That sounds like an excellent solution. I have begun playing with Access (it seems when I master one Microsoft product I end up having to start a new one) I have a Database with various "Field Names" and the "Data Types" and I have created a form and implemented my "Field Names" using the 'Field List' toolbox. Could you let me know if I am on the right track and where to go from here?

 

[paddydive]

ok... give me some time.. i will give you the steps to do that...
actually I am doing this after a long time.. so need time to recollect the steps.

 

[paddydive]

Hi AJ,

Sorry got stuck in some work.... here is the steps...
Basically Microsoft Infopath is the thing which enable us to do such thing

First open MS-Access and creat a database for the data for which you want to send out mail to gather information. You can asign the data types as per your requirement.

For example purpose i have created simple database for employee details like :

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION>Table1</CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>Name</TH><TH bgColor=#c0c0c0 borderColor=#000000>Emp_ID</TH><TH bgColor=#c0c0c0 borderColor=#000000>DOB</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#d0d7e5>abc</TD><TD borderColor=#d0d7e5>4444</TD><TD borderColor=#d0d7e5 align=right>09-May-78</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

Now goto External data -> Create e-mail option. A small wizard will popup, follow the steps . On "Specify how you want to process the replies" select the "Automatically process replies and add data to Table1 (or whaterver your table name is".
This will automatically update the records directly to the database table.

Choose the email-address book as MS-Outlook if you are using the outlook address book.

Change the Subject Line - Introduction Line as you want to appear in e-mail.

Check the form by sending it to yourself first.. when you fill up the form and press Submit. Outlook will creat a folder "Access Data Collection Replies" under Inbox and all the replies will go the this folder automatically.

all you have to do is select the mail and click "Export the Access" and all the data will go to the database table... you can also export it in excel...

note that if you add a Primary Key in the data table then it will not update duplicate records...

You can google and find more thing about me, let me also know if you discover any new in this....

Hope this works for you,

all the best.

 

[aje35]

This was exactly the type of solution I was looking for. Doing some other searching around I have narrowed it down to either the 'Create E-mail' option in Access, which sends out an HTML form, or the 'Print to PDF' option, which requires Adobe Acrobat.

My next problem to solve on this matter that you may be able to provide insight on is as follows.

I already have an existing database of client information and I want to be able to populate the form with this existing client info and then send it out to clients for updates. There are two underlying parts to this problem:

1) Populating a form with database info
2) The possibility of automating this process so it can go through a list of clients and email all of them a form with the respective data

I don't know the full capabilities of Access so what I may be asking for could be a piece-of-cake or it could be painstakingly difficult. I think I may bounce this around a few other forums as well.

Thanks for all your help.

AJ

 

[paddydive]

oooo... AJ

I am so sorry... I wud hv loved to help you in this but my knowledge of Access is just basic one or same to nothing....

Nevertheless... Mr Excel is here you can ask this in same Forum... i guess a Macro will help you ... but it is just a guess ....

Try and ask this in the Forum.... you never know they come up with more simple solution then what i gave... they are all Experts...

all the best.

Merry Christmas !!!