Importing Word form data to Access

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

Guest

I'm trying to get data from a Word form (using form controls/bookmarks) but
can't seem to get the code quite figured out. Now I'm getting error 3265...
Please help. Thanks.

Richard C.

here's the code:

Public Sub recordset()

Dim appWord As Word.Application
Dim doc As Word.Document
Dim rst As New ADODB.recordset
Dim cnn As ADODB.Connection
Dim strDocName As String
Dim blnQuitWord As Boolean

On Error GoTo ErrorHandling

strDocName = "C:\NOC\forms\test1.doc"

Set appWord = GetObject(, "Word.Application")
Set doc = appWord.Documents.Open(strDocName)
Set cnn = New ADODB.Connection

cnn.Provider = "Microsoft.Jet.OLEDB.4.0"
cnn.Open "C:\NOC\NOCdbTest1.mdb"


rst.Open "rcNOCfields", cnn, _
adOpenKeyset, adLockOptimistic

With rst
.AddNew
!To = doc.FormFields("fldTo").Result
!Address = doc.FormFields("fldAddress").Result
!TechContact = doc.FormFields("drpdnTechContct").Result
!ProcAggentName = doc.FormFields("drpdnProcAgnt").Result
!Originator = doc.FormFields("drpdnOrigName").Result
!NocNum1 = doc.FormFields("txtNOC").Result
!NocNum2 = doc.FormFields("txtStar").Result
!NocNum3 = doc.FormFields("fldNocNumYr").Result
!NocNum4 = doc.FormFields("fldNocNum").Result
!NocRevLtr = doc.FormFields("fldRevLtr").Result
!NocRevDt = doc.FormFields("fldRevDt").Result
!Authority = doc.FormFields("drpdnAuth").Result
!NocType = doc.FormFields("drpdnNocType").Result
!Priority = doc.FormFields("drpdnPriority").Result
!Status = doc.FormFields("drpdnStatus").Result
!NocChngTitle = doc.FormFields("fldChngTitle").Result
!NocOrigDt = doc.FormFields("fldOrigDt").Result
!SowNum = doc.FormFields("fldSOWNum").Result
!ContractNum = doc.FormFields("fldContractNum").Result
!PR_Num = doc.FormFields("fldPRNum").Result
!CustAffected = doc.FormFields("fldCust").Result
!MdlAffected = doc.FormFields("fldMdls").Result
!NocChngDescr = doc.FormFields("fldDescr").Result
!CA_ModLvl = doc.FormFields("CkModLvl").Result
!CA_TestProc = doc.FormFields("CkTestProc").Result
!CA_SW = doc.FormFields("CkSW").Result
!CA_HW = doc.FormFields("CkHW").Result
!CA_SrvBul = doc.FormFields("CkSrvBul").Result
!CA_Mech = doc.FormFields("CkMech").Result
!CA_Elect = doc.FormFields("CkElect").Result
!CA_OutlnDwg = doc.FormFields("CkOutlineDwg").Result
!CA_CBBDocs = doc.FormFields("CkCBBDoc").Result
!ActionReq = doc.FormFields("fldActionReq").Result
!RespDueBack = doc.FormFields("fldReqDate").Result
!IncorpDt = doc.FormFields("fldReqIncrpDt").Result
!InSeqAP = doc.FormFields("fldPropInSeqAP").Result
.Update
.Close
End With
doc.Close
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "NOC Imported!"

Cleanup:
Set rst = Nothing
Set cnn = Nothing
Set doc = Nothing
Set appWord = Nothing
Exit Sub
ErrorHandling:
Select Case Err
Case -2147022986, 429
Set appWord = CreateObject("Word.Application")
blnQuitWord = True
Resume Next
Case 5121, 5174
MsgBox "You must select a valid Word document. " _
& "No data imported.", vbOKOnly, _
"Document Not Found"
Case 5941
MsgBox "The document you selected does not " _
& "contain the required form fields. " _
& "No data imported.", vbOKOnly, _
"Fields Not Found"
Case Else
MsgBox Err & ": " & Err.Description
End Select
GoTo Cleanup
End Sub
 
Hi John. "Error 3265: Item cannot be found in the collection corresponding to
the requested name or ordinal".

Now Access is freezing when I run the module so I don't know which line is
affected. Obviously I have more than one problem in the code. I need to get
this import function working ASAP.

I started with the Access 2000 code example in Sean Kavanagh's Inside office
2000 article "Import data directly from word forms to access tables' (2001),
which other people have also attempted and have posted messages to MSDN. It
doesn't seem to work in Access 2003...

I have created the Word form, identified all the field bookmarks, created
the receiving table in Access but can't get it to open the ADODB connection
and import the data. I think I've got all the necessary references set.

Can you please check the rest of the code and show where I have gone wrong?

Thanks. Richard C.
 
Hi Richard,

This could be a missing or mis-spelt bookmark or field name (among other
things). Comment out the
On Error GoTo ErrorHandling
and set a breakpoint on
Set appWord = GetObject(, "Word.Application")

Then step through the code until find the actual line that's raising the
error.
 
Back
Top