Form data output

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gobi - 30 Jan 2006 16:07 GMT

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?

Reply to this Message

Cindy M -WordMVP- - 30 Jan 2006 17:37 GMT

Hi Gobi,

> Not really a VBA issue but...
>
[quoted text clipped - 8 lines]
> 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 :-)

Reply to this Message

gobi - 30 Jan 2006 17:53 GMT

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.

Reply to this Message

Cindy M -WordMVP- - 30 Jan 2006 18:03 GMT

Hi Gobi,

> 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
[quoted text clipped - 7 lines]
> 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 :-)

Reply to this Message

gobi - 30 Jan 2006 18:16 GMT

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!

Reply to this Message

Rate this thread: