Reconciling Employee Forms and Database Records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an employee database. I want employees to fill in a form to provided the data that the database stores. I need to email one form to each user. In most cases, the employee records are partially completed and/or out of date so the users will be updating fields in existing records. When I export the data, it has to appear formatted i.e. with dropdown controls and in some cases multiple fields may be listed in one column for aesthetic purposes. When I receive the completed forms I need to import the new data into the database from those forms. How can I accomplish this? I'm guessing it involves Word mail merges but then how do I carry over form controls?

Note:
In the input form I email out to people, there may be several fields in one column, not one field per column as per regular Access data exports to Excel and Word
This database is not web enabled and for various reasons I cannot web enable it even if I wanted to.
 
Word is most likely on each PC but the problem with Word is that it is going
to be very difficult to maintain integrity and importing results will be
difficult.

Best solution would be an intranet web page that the users log onto, it
shows them the dheir data and allows them to update it.

You could also use Excel as the file format to send the data out and import
from. Excel is better than Word in that it has Cells so you can define what
Cell data you want to import. You can also lock the Worksheet so that it is
protected from changes.

Maybe have some code to create a CSV file specific for each employee and
send it along with your User interface (Word or Excel) Then when they open
the document the form loads from the CSV file and when they update a field
it re-writes the csv file. They can send the CSV file back and it can then
be imported.

Compu Geek said:
I have an employee database. I want employees to fill in a form to
Click to expand...
provided the data that the database stores. I need to email one form to
each user. In most cases, the employee records are partially completed
and/or out of date so the users will be updating fields in existing records.
When I export the data, it has to appear formatted i.e. with dropdown
controls and in some cases multiple fields may be listed in one column for
aesthetic purposes. When I receive the completed forms I need to import the
new data into the database from those forms. How can I accomplish this?
I'm guessing it involves Word mail merges but then how do I carry over form
controls?
Note:
In the input form I email out to people, there may be several fields in
Click to expand...
one column, not one field per column as per regular Access data exports to
Excel and Word.
This database is not web enabled and for various reasons I cannot web
Click to expand...
enable it even if I wanted to.
 
Hi Geek,

One way to do this is with Word formfields. You'd set up a Word document
with a formfield for each data field, protect it so users can only type
in the formfields, and send it out.

When the completed forms come back, use VBA code running in Access to
open the form, gather the data from its FormFields collection, and
append it to the relevant Access tables.

The articles below are about getting information from Access into Word
forms but they illustrate a lot of what's needed. Also check out other
articles on formfields on the Word MVP site.

Fill in Word Forms Using Information Stored in Access
Inside Microsoft Access, January 2002
by Sean Kavanagh
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnima02/html/ima0102.asp

Access a database and insert into a Word document the data that you find
there
by Bill Coan
http://word.mvps.org/FAQs/InterDev/GetDataFromDB.htm
 
Thanks John

I'm not used to working with code and 'finally' got your solution to work...with a limited number of fields. When I start trying to add more than 6 in the 'With doc...End With' section of the code, I end up getting lots of error messages and my MS Access application freezes and not all the fields past the initial 6 will transfer from MS Access to MS Word. There are perhaps 100 fields that I have to transfer from MS Access into MS Word

Any ideas on how to do this.
 
Thanks Doug

I thought about using MS Excel too but I didn't know how to carry over the formatting. I've since seen that Excel uses form controls too but I'm not as comfortable using them in Excel as I am in Word. I'll look at this for future projects
 
Unfortunately I can't look over your shoulder and see your code. But the
general idea to process a single Word document would be

Dim oWord as Word.Application
Dim oDoc As Word.Document
Dim dbD as DAO.Database
Dim rsR as DAO.Database
Dim F As Word.FormField


Set oWord = CreateObject("Word.Application")
Set dbD = CurrentDB()
Set rsR = dbD.OpenRecordset("MyTable")
'Each formfield in the document has the same name as the
'corresponding field in the Access table.
Set oDoc = oWord.Documents.Open "D:\Folder\TheDocument.doc"

rsR.AddNew
For Each F In oDoc.FormFields
'This code may have to be modified to handle different field
'types
rsR.Fields(F.Name).Value = F.Result
Next F
rsR.Update

rsR.Close
oDoc.Close False
Set rsR = Nothing
Set oDoc = Nothing

'To be done at the very end; skip this if
'you're going straight on to process another document
oWord.Quit
Set oWord = Nothing
Set dbD = Nothing






Thanks John,

I'm not used to working with code and 'finally' got your
Click to expand...
solution to work...with a limited number of fields. When I start trying
to add more than 6 in the 'With doc...End With' section of the code, I
end up getting lots of error messages and my MS Access application
freezes and not all the fields past the initial 6 will transfer from MS
Access to MS Word. There are perhaps 100 fields that I have to transfer
from MS Access into MS Word.
 
Back
Top