Question re exchanging data between Access and a non-Access form

[Guest]

I'm hoping one of you wonderful Access experts out there can help me:

I need to create a form in Word or Excel to send to personnel who are
offsite (they don't know Access). I want to pre-populate some of the fields
on the form with Access data. I'll email them the "pre-slugged" form, they'll
fill out the rest of the fields and send it back. I'll then import the new
data into my Access database.

1. Is it best to create the form in Excel or in Word for this purpose?
2. What is the best way to facilitate that "give and take" of data between
the forms I receive back and my Access database?

Thank you!
NB

 

[John Nurick]

Hi NB,

There are quite a few possibilities here. One important consideration is
whether the form that you want each person to fill out corresponds to a
single record in a single database table or to multiple records in one
or tables.

An example of the first might be basic personal info: name, date and
place of birth, current home address and telephone number. An example of
the second might be compiling a list of employees' qualifications and
membership of trade and professional associations, where one person
could have several qualifications and memberships.

If it's one record per person, the simplest approach might be to use
formfields in a Word document. Create a Word template with a formfield
(the Forms toolbar) for each piece of data and whatever you like in the
way of layout and explanation. Protect it so users will only be able to
type in the formfields.

It's then possible to write Access VBA code that automates Word to
create a new document from the template, put one person's default data
into the formfields (by using the document's FormFields collection),
save it and email it. With a little cunning (using the SaveFormsData
argument of FileSaveAs) you can even arrange things so that when the
user saves the document all that is saved is a text file containing the
data in CSV format ready to import into Access; if that's not practical
then you just use the FormFields collection to read the data out of the
fields again.


If the data structure is more complicated, use Excel. Again, protect the
worksheet and set up data validation so users can only enter the stuff
you want where you want it. You'll need to use automation to insert the
default values for each user, but will probably be able to import the
data using queries drawing on named ranges in the workbook, each named
range pointing to a contiguous range of cells containing the data for
one particular table.