Form data output "gobi" <trgobeille[ at ]gmail.com> 30.01.2006 16:07:58 Not really a VBA issue but... I've got a really long form (~50 pages) template. I need to export the data into Access and I know the standard output method for saving as a CSV file. But, since Word exports the data as a single row of comma separated data, and the form has around 400 inputs, this blows the capacity of Access to import. What I'd like to do is insert some kind of break in the form -- without creating separate forms -- that will take the first 50 inputs and put them in a row, put the next 50 in a row, and so on. So, anyone have any good ideas on how one can manipulate the "save data for forms" output so that it triggers a new row?

Re: Form data output Cindy M -WordMVP- <C.Meister-C[ at ]hispeed.ch> 30.01.2006 17:37:00 Hi Gobi, [Quoted Text] > Not really a VBA issue but... > > I've got a really long form (~50 pages) template. I need to export the > data into Access and I know the standard output method for saving as a > CSV file. But, since Word exports the data as a single row of comma > separated data, and the form has around 400 inputs, this blows the > capacity of Access to import. What I'd like to do is insert some kind > of break in the form -- without creating separate forms -- that will > take the first 50 inputs and put them in a row, put the next 50 in a > row, and so on. So, anyone have any good ideas on how one can > manipulate the "save data for forms" output so that it triggers a new > row? > It's not possible to change that output. Nor can I envision how Access should deal with the format you propose, as each "row" would have the same field name, thus creating multiple records... The best solution would probably be to either use automation from within Access to extract the data from Word or the text file (ADO or DAO connection). Or to use DAO or ADO to put the data directly from Word into the database. Cindy Meister INTER-Solutions, Switzerland http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004) http://www.word.mvps.org This reply is posted in the Newsgroup; please post any follow question or reply in the newsgroup and not by e-mail :-)

Re: Form data output "gobi" <trgobeille[ at ]gmail.com> 30.01.2006 17:53:19 Access is the easy part. We'd just be importing CSV data, appending it into a preexisting table and then splitting and re-stacking the data via query. Ideally the data from the form would create multiple records -- each page of the form is designed around the same response format (6 text boxes, 3 check boxes) but the questions used to solicit responses are different on each page. I was hoping that something simple, like inserting a section break (or a similar programmatic option), would prompt Word to trigger a carriage return in the CSV file, but no such luck.

Re: Form data output Cindy M -WordMVP- <C.Meister-C[ at ]hispeed.ch> 30.01.2006 18:03:19 Hi Gobi, [Quoted Text] > Access is the easy part. We'd just be importing CSV data, appending it > into a preexisting table and then splitting and re-stacking the data > via query. > > Ideally the data from the form would create multiple records -- each > page of the form is designed around the same response format (6 text > boxes, 3 check boxes) but the questions used to solicit responses are > different on each page. I was hoping that something simple, like > inserting a section break (or a similar programmatic option), would > prompt Word to trigger a carriage return in the CSV file, but no such > luck. > No, no such luck. But given your description, it should be an easy enough matter to use a macro in Word to generate the text file you describe (or put the data directly into Access). Would that work for you? Or, if the problem is that the data is coming back in the format you describe, I suppose you'd have to use code in Access to process the text file directly. Cindy Meister INTER-Solutions, Switzerland http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004) http://www.word.mvps.org This reply is posted in the Newsgroup; please post any follow question or reply in the newsgroup and not by e-mail :-)

Re: Form data output "gobi" <trgobeille[ at ]gmail.com> 30.01.2006 18:16:35 Thanks for your comments, by the way. It's really gotten me thinking about different ways to take care of the problem. I think the easiest way for me to tackle this will be to set a disabled text form field onto each page that flags where the carriage return should be. Once I get the forms back, I can run a find-replace on the CSV output looking for the flag (essentially, swapping the text for a carriage return code) and do the import process. Hmmm. That should work. Thanks again!