I tried your code and it worked great as an exercise! However, when I tried to customize it a bit by adding additional fields, I get several error messages.
Error 5941: The requested member of the collection does not exist.
I’ve double-checked your codes against mine, and other than the additional fields that I added, everything is the same.
When I try to debug, it keeps highlighting the “cnn.close” command
Here is a copy of my code:
Sub TransferCustomers()
‘Transfer new deal registration record to
‘Customers table in 2008 CA Deal Registration database.
Dim cnn As ADODB.Connection
Dim strConnection As String
Dim strSQL As String
Dim strPath As String
Dim doc As Word.Document
Dim strCDWAMFirst As String
Dim strCDWAMLast As String
Dim strCDWAMRepID As String
Dim strCDWAMPhone As String
Dim strCDWAMEmail As String
Dim strCompanyName As String
Dim strBillingAddress As String
Dim strCity As String
Dim strZipCode As String
Dim strIndustry As String
Dim strContactFirstName As String
Dim strContactLastName As String
Dim strContactPhone As String
Dim strContactEmail As String
Dim strQuoteOrder As String
Dim strProductDesc As String
Dim strQuoteOrderValue As String
Dim strEstClose As String
Dim strQtyItemSkus As String
Dim strComments As String
Dim bytContinue As Byte
Dim lngSuccess As Long
Set doc = ThisDocument
On Error GoTo ErrHandler
strCDWAMFirst = Chr(39) & doc.FormFields(“txtCDWAMFirst”).Result & Chr(39)
strCDWAMLast = Chr(39) & doc.FormFields(“txtCDWAMLast”).Result & Chr(39)
strCDWAMRepID = Chr(39) & doc.FormFields(“txtCDWAMRepID”).Result & Chr(39)
strCDWAMPhone = Chr(39) & doc.FormFields(“txtCDWAMPhone”).Result & Chr(39)
strCDWAMEmail = Chr(39) & doc.FormFields(“txtCDWAMEmail”).Result & Chr(39)
strCompanyName = Chr(39) & doc.FormFields(“txtCompanyName”).Result & Chr(39)
strBillingAddress = Chr(39) & doc.FormFields(“txtBillingAddress”).Result & Chr(39)
strCity = Chr(39) & doc.FormFields(“txtCity”).Result & Chr(39)
strStateProvince = Chr(39) & doc.FormFields(“txtStateProvince”).Result & Chr(39)
strZipCode = Chr(39) & doc.FormFields(“txtZipCode”).Result & Chr(39)
strIndustry = Chr(39) & doc.FormFields(“txtIndustry”).Result & Chr(39)
strContactFirstName = Chr(39) & doc.FormFields(“txtContactFirstName”).Result & Chr(39)
strContactLastName = Chr(39) & doc.FormFields(“txtContactLastName”).Result & Chr(39)
strContactPhone = Chr(39) & doc.FormFields(“txtContactPhone”).Result & Chr(39)
strContactEmail = Chr(39) & doc.FormFields(“txtContactEmail”).Result & Chr(39)
strQuoteOrder = Chr(39) & doc.FormFields(“txtQuoteOrder”).Result & Chr(39)
strProductDesc = Chr(39) & doc.FormFields(“txtProductDesc”).Result & Chr(39)
strQuoteOrderValue = Chr(39) & doc.FormFields(“txtQuoteOrderValue”).Result & Chr(39)
strEstClose = Chr(39) & doc.FormFields(“txtEstClose”).Result & Chr(39)
strQtyItemSkus = Chr(39) & doc.FormFields(“txtQtyItemSkus”).Result & Chr(39)
strComments = Chr(39) & doc.FormFields(“txtComments”).Result & Chr(39)
‘Confirm new record.
bytContinue = MsgBox(“Do you want to submit this registration?”, vbYesNo, “Add Record”)
Debug.Print bytContinue
‘Process input values.
If bytContinue = vbYes Then
strSQL = “INSERT INTO Customers ” _
& “(CDWAMFirst, CDWAMLast, CDWAMRepID, CDWAMPhone, CDWAMEmail, CompanyName, BillingAddress, City, StateProvince, ZipCode, Industry, ContactFirstName, ContactLastName, ContactPhone, ContactEmail, QuoteOrder, ProductDesc, QuoteOrderValue, EstClose, QtyItemSkus, Comments) ” _
& “VALUES (” _
& strCDWAMFirst & “, ” _
& strCDWAMLast & “, ” _
& strCDWAMRepID & “, ” _
& strCDWAMPhone & “, ” _
& strCDWAMEmail & “, ” _
& strCompanyName & “, ” _
& strBillingAddress & “, ” _
& strCity & “, ” _
& strStateProvince & “, ” _
& strZipCode & “, ” _
& strIndustry & “, ” _
& strContactFirstName & “, ” _
& strContactLastName & “, ” _
& strContactPhone & “, ” _
& strContactEmail & “, ” _
& strQuoteOrder & “, ” _
& strProductDesc & “, ” _
& strQuoteOrderValue & “, ” _
& strEstClose & “, ” _
& strQtyItemSkus & “, ” _
& strComments & “)”
Debug.Print strSQL
‘Substitute path and connection string with DSN if available.
strPath = “W:\Deal Registration\2008 CA Deal Registration.mdb”
strConnection = “Provider=Microsoft.Jet.OLEDB.4.0;” _
& “Data Source = ” & strPath
Debug.Print strConnection
Set cnn = New ADODB.Connection
cnn.Open strConnection
cnn.Execute strSQL, lngSuccess
cnn.Close
MsgBox “You inserted ” & lngSuccess & ” record”, _
vbOKOnly, “Error Added”
doc.FormFields(“txtCDWAMFirst”).TextInput.Clear
doc.FormFields(“txtCDWAMLast”).TextInput.Clear
doc.FormFields(“txtCDWAMRepID”).TextInput.Clear
doc.FormFields(“txtCDWAMPhone”).TextInput.Clear
doc.FormFields(“txtCDWAMEmail”).TextInput.Clear
doc.FormFields(“txtCompanyName”).TextInput.Clear
doc.FormFields(“txtBillingAddress”).TextInput.Clear
doc.FormFields(“txtCity”).TextInput.Clear
doc.FormFields(“txtStateProvince”).TextInput.Clear
doc.FormFields(“txtZipCode”).TextInput.Clear
doc.FormFields(“txtIndustry”).TextInput.Clear
doc.FormFields(“txtContactFirstName”).TextInput.Clear
doc.FormFields(“txtContactLastName”).TextInput.Clear
doc.FormFields(“txtContactPhone”).TextInput.Clear
doc.FormFields(“txtContactEmail”).TextInput.Clear
doc.FormFields(“txtQuoteOrder”).TextInput.Clear
doc.FormFields(“txtProductDesc”).TextInput.Clear
doc.FormFields(“txtCDWAMFirst”).TextInput.Clear
doc.FormFields(“txtQuoteOrderValue”).TextInput.Clear
doc.FormFields(“txtEstClose”).TextInput.Clear
doc.FormFields(“txtQtyItemSkus”).TextInput.Clear
doc.FormFields(“txtComments”).TextInput.Clear
End If
Set doc = Nothing
Set cnn = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Number & “: ” & Err.Description, _
vbOKOnly, “Error”
On Error GoTo 0
On Error Resume Next
cnn.Close
Set doc = Nothing
Set cnn = Nothing
End Sub
Any help would be greatly appreciated!
