Can't update a recordset in Acess when VB calls another module

[zraj07]

This works: While I am in this Module, the code will update the two recordsets "Location Info" and "Other Info". I am using Access 2003-2007 and Word 97-2003.
My form fields import successfully to my database.


Option Compare Database

Sub GetWordData()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDocName As String
Dim blnQuitWord As Boolean

On Error GoTo ErrorHandling


strDocName = "C:\Test\" & _
InputBox("Type in and enter the name of your document " & _
"you want to import:", "Import test document")

Set appWord = GetObject(, "Word.Application")
Set doc = appWord.Documents.Open(strDocName)

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test\" & "Test.mdb;"


rst.Open "Location_Info", cnn, adOpenKeyset, adLockOptimistic

With rst
.AddNew
!fldCompany = doc.FormFields("fldCompany").Result
!fldName = doc.FormFields("fldName").Result
!fldNumber = doc.FormFields("fldNumber").Result


.Update
.Close



rst.Open "Other_Info", cnn, adOpenKeyset, adLockOptimistic

With rst
.AddNew

!chkvalidationY = doc.FormFields("chkvalidationY").Result
!chkvalidationN = doc.FormFields("chkvalidationN").Result
!fldValDesc = doc.FormFields("fldValDesc").Result



End With
End With

doc.Close
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "Test DocImported!"

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

****************************************************************************************************

However, if I try to do this as you see below, and call "Other_Info" from a separate module, I get the 3709 error or an Object required error.
I really want to call "Other_Info" and continue to update the database that was presumably still open, but it seems
Access forgets about that open connection once I step out and enter "Module1.Other_info




Option Compare Database

Sub GetWordData()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDocName As String
Dim blnQuitWord As Boolean

On Error GoTo ErrorHandling


strDocName = "C:\Test\" & _
InputBox("Type in and enter the name of your document " & _
"you want to import:", "Import test document")

Set appWord = GetObject(, "Word.Application")
Set doc = appWord.Documents.Open(strDocName)

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test\" & "Test.mdb;"


rst.Open "Location_Info", cnn, adOpenKeyset, adLockOptimistic

With rst
.AddNew
!fldCompany = doc.FormFields("fldCompany").Result
!fldName = doc.FormFields("fldName").Result
!fldNumber = doc.FormFields("fldNumber").Result


.Update
.Close

Module1.Other_Info

End With

doc.Close
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "Test Info 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

************************************************************************************************************************
Here is what "Module1.Other_Info looks like.
I have tried commenting out things that i don't think should be required given the module this was called from already has a database connection established.

"other_info"

Sub OtherInfo()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDocName As String
Dim blnQuitWord As Boolean

'On Error GoTo ErrorHandling


'strDocName = "C:\Test\" & _
' InputBox("Type in and enter the name of your Test document " & _
' "you want to import:", "Import Test Doc")

'Set appWord = GetObject(, "Word.Application")
'Set doc = appWord.Documents.Open(strDocName)

'cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
' "Data Source=C:\Test\" & "Test.mdb;"

rst.Open "Other_Info", cnn, adOpenKeyset, adLockOptimistic

With rst
.AddNew

!chkvalidationY = doc.FormFields("chkvalidationY").Result
!chkvalidationN = doc.FormFields("chkvalidationN").Result
!fldValDesc = doc.FormFields("fldValDesc").Result

.Update
.Close



End With
'doc.Close
'If blnQuitWord Then appWord.Quit
'cnn.Close
'MsgBox "IMF0405 Location_Pipe_Info 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

 

[Micron]

While I am in this Module

When is this? Is this module behind a form, or is it a standard (stand alone) module? If behind a form, it is private and cannot be called from elsewhere.

I get the 3709 error or an Object required error.

These two errors are likely unrelated. I'm afraid it doesn't help us much if you don't at least indicate what line the error occurs on, but the number and error message are also vital information to those trying to assist.

Note: I don't see "Option Explicit". If you do not set this, you are setting yourself up for a lot of grief.

 

[zraj07]

These are all stand alone modules.

If, in the module called "Other_Info", I uncomment these lines below, the code runs to completion.
But it means I am re-entering the name of the Word document.
So if you had 9 modules to call one at a time, you would have re-enter that Word doc name 9 times..
That is what I don't understand.


'strDocName = "C:\Test\" & _
' InputBox("Type in and enter the name of your Test document " & _
' "you want to import:", "Import Test Doc")

'Set appWord = GetObject(, "Word.Application")
'Set doc = appWord.Documents.Open(strDocName)

'cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
' "Data Source=C:\Test\" & "Test.mdb;"

 

[mole999]

I haven't used access for ages, but I would suggest that you need to declare the reference as a global value, then once it is populated once, you can reuse until you clear it

 

[Micron]

Procedures in a standard module are public by default. However, you cannot call OtherInfo with Other_Info.

call "Other_Info" from a separate module,

I for one, am confused as to what OtherInfo is and what Other_Info is.
I have never called a procedure in a standard module by using the module namev("module1"), so I don't even know if that's an issue here. To call "OtherInfo" in module mdlDbFunctions you simply invoke the name of the procedure. You can use the Call statement or not. Whether or not you need parentheses () depends on if there are parameters that need to be passed, for one reason:
Code to do stuff like get the document file name and location (like "C:\Test.docx")
GetMyFile ("C:\Test.docx") OR if it has been assigned to a variable
GetMyFile (strDocName)

Another reason why I'm confused about those names is that Other_Info looks like a query or table name

rst.Open "Other_Info", cnn, adOpenKeyset, adLockOptimistic

So if you had 9 modules to call one at a time, you would have re-enter that Word doc name 9 times..

No, on whatever form you are (or should be) using to drive the process, you'd pass the document name to the procedure and only need one procedure - not 9. And I'd use a form button to invoke the FilePicker to find the file, not have someone type it into an input box and have to deal with the errors resulting from typos or files that don't exist where the user thinks they are.

As for making a variable Global, you have to be very careful because they can persist throughout the entire session. If you are going to modify it several times during an operation, I would avoid it. I do not think it will solve your problem, but that's based on what I've just noted above.