Access 2010: Collect data through email forms

[Katie Howard]

I have an Access 2010 database and am using the feature of collecting data by email. I’m using the InfoPath form as the means of collecting the data. In general, it works well – but I’ve run into 2 issues that I’m hoping for advice on:

1) Is there a way to set the default value for the InfoPath form fields? In Access, I’ve set the default values of 3 of my fields – but this doesn’t carry over to the InfoPath form that gets created by Access when I send the email. Is there another way to set this up?

2) One of the data points that I’m collecting is the Departure Date. This is a date/time field, and I have the format set as a Short date in Access (eg, 9/3/13). When we email the form and try to submit it, InfoPath displays the General Date (eg, 9/3/2013 1:26:18 PM) – and won’t enter this new record into my database due to a “data type conversion error”. I’ve tried changing the Access field to be the General Date format, but I still get the same error. I can submit the form just fine if I don’t enter a date (but I do need to collect the date) – and when I try entering anything into the date field, I get the error upon submission.

I’ve been looking online for solutions to these two issues but haven’t come across anything. Any help would be appreciated! Thank you!

[jimatqsi]

For the date problem, perhaps you are are trying to store the value of the text field holding the date (the "formatted date" which is a string of text) when you should be storing a date value, something defined to be a date type field. Are you using the name of the text box instead of the name of the bound field from the table?

Jim

[zmbd]

NO, there isn't a lot of documentation about this feature.

YES, The collect information by email feature of Access2010 is absolutly horrid in its design and implementation. You think the infopath forms are bad... try it with the HTML defaults... YUCK.

Unless you can set the default in the infopath form itself, you are most likely out of luck and I haven't found a foolproof method.

What I've found is that if the returned value has anything other than a numeric value (including commas) then the entire thing is treated as if it is a text value - so a date is treated as text on import. The only way I found around this was to push the incoming data into a temporary table, set all of the field types to either numeric or text, and then use VBA to parse the incoming information into the correct table(s) using the type conversion functions (CDATE(), CSTR(), etc...) to get the correct datatype.

I've gotten to the point with this, that I only use it for the most basic of data collection - mainly due to the fact that most of the people I send emails to do not have InfoPath installed so I'm stuck using the HTML option.

/Sorry/ - Wish I had better news.

[zmbd]

This site may prove interesting...
It goes into some depth on using a simple HTML form to collect the data; however, it also mentions using the CGI or other scripts for fancier forms. May be well worth the read thru - especially as I've learned that ACC2013 may have done away with the built-in data collection by email functionality. The Accces version being demo'd is ACC2000 so there may be some quirks to work out.

Here I'll pull the first little bit over and if you think it worthwhile then follow the link for the rest of the information:

From Access to Automated E-mail

Microsoft Access supports Microsoft Outlook and Microsoft Exchange through the Link Manager. This article shows you how to set up a Web-based system to help automate your e-mail collection using Access 2000, 2002, 2003. Access can work with more than just relational data. In this article, I'm going to show you how to tie Access into Microsoft Outlook. The processes involved are a simple HTML-based form, your e-mail software, Microsoft Outlook, and Microsoft Access. Even if you're completely HTML-clueless, you should be able to set up a complete mail processing Internet application with the material in this article.

Technology Introduction and Resource Files

The processes involved are a simple html based form, your email software, Microsoft Outlook/Microsoft Exchange and Microsoft Access. Unless you develop for a tightly controlled in-house email setup, you will find that the solutions offered here may have to be customized for different sites as each will have their own mail server configuration. All that aside, the processes that you will follow will be similar and the Access software side will be reusable. In this article, I have shown all the examples using Outlook .

This document comes with a download file called Exchange_BE.zip This has an Access 2000 software database called mail-fx.mdb and a HTML document called SoftwareDownloa d.htm.

[Katie Howard]

Thanks for your responses, jimatqsi and zmbd! I'll have to look at that article and see if I can make it work. I appreciate your help.

[zmbd]

For some reason, I just knew that it wouldn't be that simple.

I ran across a white paper on this, appears that ACC2010 now uses the MSWord HTML engine an not the IE HTML engine to render the forms; thus, the forms do not render for input as a straight email message.

What you can do is send the HTML form as an attachment, which does open in th the IE/Internet browser. Not the most elegant, however, it does work.

Now the rub here is that if we use the DCBM feature, then the forms do render for input - if if just basic; therefore, there must be a way to get outlook to handle this... I'll look into it a bit more.