Create Form For Access Database

[Gordon Padwick]

I'm working with a non-profit organization to create a database that records
volunteer activities. I've created the database in Microsoft Access and it's
working okay. The problem, though, is that people have to manually
transcribe paper records provided by volunteers into the database.

Many of our volunteers have Microsoft Word and could easily use that to
record their activities.

Is there some way to set up a profile in Word that would make it easy for
individuals to record their activities in a file that could, subsequently be
automatically imported into an Access database?

I've looked at Word's forms capability, but that doesn't seem to provide
what I'm looking for.

I'll appreciate any suggestions.

Gordon Padwick

 

[Jezebel]

Ask around amongst your volunteers for someone who knows enough VBA to write
a macro to transfer the data from a Word form to a database. It's not hard
to do, but it's not automatic, either.

 

[Doug Robbins - Word MVP]

If your volunteers fill in forms that use formfields in a protected
document, and those completed forms are saved in a folder by themselves, in
the case of the following code sample, assumed to be c:\volunteer, the
following command button click event in a form in an access database,
assumed in this code to be C:\volunteer\volunteer.mdb, will extract the
information from the formfields in each of the forms and place it in a table
(tblvolunteers) in the database. The forms that have been processed will be
moved to a subfolder c:\volunteers\processed so that a new lot of forms can
be added to the c:\volunteers folder and be processed at a later time. Note
that there are 3 sections in the code where it will have to be modified to
suit your particular data:

Private Sub ProcessVolunteerForms_Click()
Dim dbsVolunteer As Database
Dim rstVolunteer As Recordset
Dim wordApp As Object
'These declarations will need to be modified to suit your forms
Dim vAuthor As String
Dim vWorkingTitle As String
Dim vStreet As String
Dim vCity As String
Dim vState As String
Dim vZip As String
Dim vPhone As String
Dim vEmail As String
Dim vWebsite As String
Dim vDateSubmitted As String
Dim vTopic As String
Dim vFormat As String
Dim vSlides As String
Dim vTransparencies As String
Dim vPhotos As String
Dim vLineDrawings As String
Dim vAuthorPhoto As String
Dim vOtherVisuals As String
Dim vSASE As String
'End of section to be modified
Dim FldrPath As String
Dim RecordDoc As String
Dim Source As Object
Dim i As Long
Dim Filetokill As String

FldrPath = "C:\Volunteer\"

Set dbsVolunteer = OpenDatabase(FldrPath & "Volunteer.mdb")
Set rstVolunteer = dbsVolunteer.OpenRecordset("tblVolunteers",
dbOpenDynaset)

On Error GoTo CreateWordApp
Set wordApp = GetObject(, "Word.Application")
wordApp.Visible = False
On Error Resume Next

RecordDoc = Dir$(FldrPath & "*.doc")

i = 0
While RecordDoc <> ""
Set Source = wordApp.Documents.Open(FldrPath & RecordDoc)
'These statements will need to be modified to suit your forms
vAuthor = Source.FormFields("Author").result
vWorkingTitle = Source.FormFields("WorkingTitle").result
vStreet = Source.FormFields("Street").result
vCity = Source.FormFields("City").result
vState = Source.FormFields("State").result
vZip = Source.FormFields("Zip").result
vPhone = Source.FormFields("Phone").result
vEmail = Source.FormFields("Email").result
vWebsite = Source.FormFields("Website").result
vDateSubmitted = Source.FormFields("DateSubmitted").result
vTopic = Source.FormFields("Topic").result
vFormat = Source.FormFields("Format").result
vSlides = Source.FormFields("Slides").result
vTransparencies = Source.FormFields("Transparencies").result
vPhotos = Source.FormFields("Photos").result
vLineDrawings = Source.FormFields("LineDrawings").result
vAuthorPhoto = Source.FormFields("AuthorPhoto").result
vOtherVisuals = Source.FormFields("OtherVisuals").result
vSASE = Source.FormFields("SASE").result
'End of section to be modified
Filetokill = FldrPath & Source
Source.SaveAs FldrPath & "Processed\" & vAuthor
Source.Close wdDoNotSaveChanges
Kill Filetokill
With rstVolunteer
.AddNew
'These statements will need to be modified to suit your forms
If vAuthor <> "" Then !Author = vAuthor
If vWorkingTitle <> "" Then !WorkingTitle = vWorkingTitle
If vStreet <> "" Then !Street = vStreet
If vCity <> "" Then !City = vCity
If vState <> "" Then !State = vState
If vZip <> "" Then !Zip = vZip
If vPhone <> "" Then !Phone = vPhone
If vEmail <> "" Then !Email = vEmail
If vWebsite <> "" Then !Website = vWebsite
If vDateSubmitted <> "" Then !DateSubmitted = vDateSubmitted
If vTopic <> "" Then !Topic = vTopic
If vFormat <> "" Then !Format = vFormat
If vSlides <> "" Then !Slides = Val(vSlides)
If vTransparencies <> "" Then !Transparencies = Val(vTransparencies)
If vPhotos <> "" Then !Photos = Val(vPhotos)
If vLineDrawings <> "" Then !LineDrawings = Val(vLineDrawings)
If vAuthorPhoto <> "" Then !AuthorPhoto = Val(vAuthorPhoto)
If vOtherVisuals <> "" Then !OtherVisuals = Val(vOtherVisuals)
If vSASE <> "" Then !SASE = Val(vSASE)
'End of section to be modified
.Update
End With
i = i + 1

RecordDoc = Dir
Wend

MsgBox i & " Records Added."

Set wordApp = Nothing
Set WordDoc = Nothing

CreateWordApp:
Set wordApp = CreateObject("Word.Application")
Resume Next

End Sub

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP