I am trying to take the user input from a Word form and place it into an Access DB. I have the document and database in the same folder on my C drive. I am getting an error at :
rst.Fields("FullName") = doc.FormFields("txtYourName").Result
It is not getting the data from the content control.
Here is all of the of the code: - 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:\SampleForm\" & _
-
InputBox("Enter the name of the Word document you want to import:", "Import document")
-
-
Set appWord = GetObject(, "Word.Application")
-
appWord.Visible = True
-
-
Set doc = appWord.Documents.Open(strDocName)
-
-
Set cnn = CurrentProject.Connection
-
-
rst.Open "FormData", cnn, adOpenKeyset, adLockOptimistic
-
-
-
rst.AddNew
-
-
-
rst.Fields("FullName") = doc.FormFields("txtYourName").Result
-
rst.Fields("Address") = doc.FormFields("txtYourAddress").Result
-
rst.Fields("Phone") = doc.FormFields("txtYourPhone").Result
-
rst.Fields("Male") = doc.FormFields("txtMale").Result
-
rst.Fields("Female") = doc.FormFields("txtFemale").Result
-
-
rst.Update
-
rst.Close
-
-
-
doc.Close
-
-
If blnQuitWord Then appWord.Quit
-
cnn.Close
-
MsgBox "Request Imported"
-
-
Cleanup:
-
Set rst = 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 5491
-
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
-
Any suggestions would be great. Thanks
17 9832 zmbd 5,501
Expert Mod 4TB
We can not do the basic trouble shooting for you; however,
if you will do the following, it will help us to help you:
As the line containing: - rst.Fields("FullName") = doc.FormFields("txtYourName").Result
may not be the actual source of the error:
Please place a STOP command at Line 11 and comment out Line 12
Once done
Please execute your code.
Your code will stop at line 11 on the STOP command.
Press [F8] slowly until you reach the line where the error occurs.
Please post the Line # where the error occurs
Please post:
The EXACT ERROR
Title
Number
Description.
Please do not omit or paraphrase any of the error message.
When I get to the line:
rst.Fields("FullName")=doc.FormFields("txtYourName ").Result
it sends me to the error handling:
Case -21447022986, 429 section. I kept scrolling through and got error 5941: The requested member of the collection does not exist.
Thank you for your response
zmbd 5,501
Expert Mod 4TB
You did not comment out Line12
Place a single quote ( ' ) before the entry in that line.
Then run again
I kept scrolling through and got error 5941: The requested member of the collection does not exist
This leads me to a mis-named control or field.
Which line errored with that?
I commented out line 12 and the error occurs at:
rst.Fields("FullName") = doc.FormFields("YourName").Result
I took out the txt in front of YourName so it would match the content control exactly.
Should I change FormFields to ContentControl?
zmbd 5,501
Expert Mod 4TB
same error?
Please post the error Title, Number, and text.
zmbd 5,501
Expert Mod 4TB
Which version of Office (or Access and Word) are you using?
If in 2007 or 2010 are you using the "classic" field controls or the newer text control?
I am getting the same error and I am using Access and Word 2010. Also the "classic" field controls.
zmbd 5,501
Expert Mod 4TB
PLEASE
Post THE TITLE
the ERROR NUMBER
and THE TEXT
zmbd 5,501
Expert Mod 4TB
TsNave:
Make a copy of the word document.
In the copy of the word document please insert a standard code module.
Copy and past the following code block into the document.
Run the code
Follow the instructions. - Sub GetCCInfo()
-
Dim cc As ContentControl
-
Dim fc As Field
-
Dim ccinfo As String
-
-
' Iterate through all the content controls in the document
-
Debug.Print ">>>Cut and paste - Start<<<"
-
If ActiveDocument.ContentControls.Count <> 0 Then
-
Debug.Print "Start of controls, Count = " & ActiveDocument.ContentControls.Count & vbCrLf
-
For Each cc In ActiveDocument.ContentControls
-
ccinfo = "<> ID= " & cc.ID & " Title = " & cc.Title & " Tag = " & cc.Tag & " Text = " & cc.Range.Text & vbCrLf
-
Debug.Print ccinfo
-
Next
-
Else
-
Debug.Print "Start of controls, Count = 0"
-
End If
-
If ActiveDocument.FormFields.Count <> 0 Then
-
Debug.Print "Start of Fields, Count = " & ActiveDocument.FormFields.Count & vbCrLf
-
For Each fc In ActiveDocument.Fields
-
ccinfo = "<> Index = " & fc.Index & " result = " & fc.Result.Text & vbCrLf
-
Debug.Print ccinfo
-
Next
-
Else
-
Debug.Print "Start of fields, Count = 0"
-
End If
-
-
Debug.Print ">>>Cut and paste - end<<<"
-
-
MsgBox "Please open the VBA editor and press <ctrl><g>" & vbCrLf & "Please cut and paste the indicated contents to the thread"
-
End Sub
-
That did pull the data from the form. Thank you very much. Now, I just got to put that into Access
>>>Cut and paste - Start<<<
Start of controls, Count = 5
<> ID= 261426431 Title = YourName Tag = YourName Text = Todd Snavely
<> ID= 846142673 Title = YourAddress Tag = YourAddress Text = 1234 Main St.
<> ID= 2918279744 Title = YourPhone Tag = YourPhone Text = 555-5555
<> ID= 1014266676 Title = Male Tag = Male Text = ?
<> ID= 3052641625 Title = Female Tag = Female Text = ?
Start of controls, Count = 0
>>>Cut and paste - end<<<
zmbd 5,501
Expert Mod 4TB
And that explains why you are getting the error that you are receiving - and exactly what I expected to see.
You are not using the "Classic" or "Legacy" form controls that are hold over from Word/Office 2003. You are using the new Content Controls introduced in 2007/2010.
The Rich and Plain text controls are not part of the fields collection as you are attempting to refer to them in the vba
Note that I'm in design mode here so that you can see the tags on the new style controls.
So, you have a few options.
- We can refer to your controls as is via index.
- We can refer to your controls via the Title (the part -that is seen in normal view by the users when they enter the control for data entry.
- We can refer to the controls via the tag property.
all of which require some re-writing of your code.
OR
- You can redo your word document and use the legacy controls. Doing this you should be able to use your code as is, perhaps a tweak or two for syntax.
your call
Thank you for all your help. I am going to have to refer to the title of the content control. This project was given to me by a professor who already has 370+ documents already filled out with this type of controls and need the data imported into Access.
zmbd 5,501
Expert Mod 4TB
The title must be UNIQUE!
Two ways to code this (and I will leave the code writing to you :-) )...
One is the select by title method. It is ok to use, but a but fuddy for my taste and if something changes in the document such as the title actually used, then it makes the coding more complex.
Therefore, in this case let's do the loop method as I've already provided in the code Post#10-lines 10 thru 13. and modify the code you posted in post#1.
-->insert and modify your code so that:
-a- Set up variables to hold your values from the documents and for the references you'll need to use the Post#10-lines 10 thru 13. code in your current code module.
-b- Change the "ActiveDocument" object refered to in Post#10-line 10 to your open document object "doc" object refered to on post#1-line 22
-c- Use a select case statement against the value returned by "cc.title" to then return the value from "cc.Range.Text" and assign it to the correct variable that you set up "-a-" keeping in mind that Uppercase and Lowercase are not the same in the comparisons so use something like the lcase() function to format "cc.title" correctly.
-d- make sure you validate your variables either within step -c- or after, if missing or wrong datatype the correct it now.
-e- now take the assigned variables and use those in the code post#1-Lines 32 thru 36
-f- make sure you have the appropiate error traping setup to handle any errors encountered!
Note that in step -c- if you run into a document where the title of the control is slightly off, then you only need to add that variation to the "Case" for the correct variable assignment.
You might also want to modify the If-Then statment regarding the count so that if you do happen to have a document without any content controls you will not receive an error.
Please post back your completed code, even if it works, so that others in this situation can benifit from our efforts.
Given that you have so many documents to handle, you may also wish to take a look at the File open dialog article in our insights section!!!
(*) Microsoft Access / VBA Insights Sitemap \
(*) Article # 29 Select a File or Folder using the FileDialog Object
Here is my modified code. Works great. I do have 2 checkboxes that I need if they are checked or not. - Sub GetWordData()
-
-
Dim cc As ContentControl
-
Dim fc As Field
-
Dim ccInfo As String
-
Dim YourName As String
-
Dim YourAddress As String
-
Dim YourPhone As String
-
Dim Male As CheckBox
-
Dim Female As CheckBox
-
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:\SampleForm\" & _
-
InputBox("Enter the name of the Word document you want to import:", "Import document")
-
-
-
Set appWord = GetObject(, "Word.Application")
-
appWord.Visible = True
-
-
Set doc = appWord.Documents.Open(strDocName)
-
-
For Each cc In doc.ContentControls
-
ccInfo = "<> ID= " & cc.Id & " Title = " & cc.Title & " Text = " & cc.Range.Text & vbCrLf
-
Debug.Print ccInfo
-
Select Case cc.Title:
-
Case "YourName"
-
YourName = cc.Range.Text
-
Case "YourAddress"
-
YourAddress = cc.Range.Text
-
Case "YourPhone"
-
YourPhone = cc.Range.Text
-
Case "Male"
-
Male = cc.Range.Text
-
Case "Female"
-
Female = cc.Range.Text
-
End Select
-
-
-
Next
-
-
-
Set cnn = CurrentProject.Connection
-
-
rst.Open "FormData", cnn, adOpenKeyset, adLockOptimistic
-
-
With rst
-
.AddNew
-
-
-
rst.Fields("FullName") = YourName
-
rst.Fields("Address") = YourAddress
-
rst.Fields("Phone") = YourPhone
-
rst.Fields("Male") = Male
-
rst.Fields("Female") = Female
-
-
rst.Update
-
.Close
-
End With
-
-
doc.Close
-
-
If blnQuitWord Then appWord.Quit
-
cnn.Close
-
MsgBox "Request Imported"
-
-
Cleanup:
-
Set rst = Nothing
-
Set doc = Nothing
-
Set appWord = Nothing
-
-
Exit Sub
-
ErrorHandling:
-
-
Select Case Err.Number
-
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 5491
-
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
The Male and Female fields are the checkboxes. The other forms that I need to import have around 60 or so checkboxes.
Thank you for all your help. This is my first time with VB and I have learned a lot from all your help.
I got the checkboxes figured out and here is the finished working code: - Sub GetWordData()
-
-
Dim cc As ContentControl
-
Dim fc As Field
-
Dim ccInfo As String
-
Dim YourName As String
-
Dim YourAddress As String
-
Dim YourPhone As String
-
Dim Male As String
-
Dim Female As String
-
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:\SampleForm\" & _
-
InputBox("Enter the name of the Word document you want to import:", "Import document")
-
-
-
Set appWord = GetObject(, "Word.Application")
-
appWord.Visible = True
-
-
Set doc = appWord.Documents.Open(strDocName)
-
-
For Each cc In doc.ContentControls
-
ccInfo = "<> ID= " & cc.Id & " Title = " & cc.Title & " Text = " & cc.Range.Text & vbCrLf
-
Debug.Print ccInfo
-
Select Case cc.Title:
-
Case "YourName"
-
YourName = cc.Range.Text
-
Case "YourAddress"
-
YourAddress = cc.Range.Text
-
Case "YourPhone"
-
YourPhone = cc.Range.Text
-
Case "Male"
-
If cc.Checked Then
-
Male = "Yes"
-
Else: Male = "No"
-
End If
-
Case "Female"
-
If cc.Checked Then
-
Female = "Yes"
-
Else: Female = "No"
-
End If
-
End Select
-
-
-
Next
-
-
-
Set cnn = CurrentProject.Connection
-
-
rst.Open "FormData", cnn, adOpenKeyset, adLockOptimistic
-
-
With rst
-
.AddNew
-
-
-
rst.Fields("FullName") = YourName
-
rst.Fields("Address") = YourAddress
-
rst.Fields("Phone") = YourPhone
-
rst.Fields("Male") = Male
-
rst.Fields("Female") = Female
-
-
rst.Update
-
.Close
-
End With
-
-
doc.Close
-
-
If blnQuitWord Then appWord.Quit
-
cnn.Close
-
MsgBox "Request Imported"
-
-
Cleanup:
-
Set rst = Nothing
-
Set doc = Nothing
-
Set appWord = Nothing
-
-
Exit Sub
-
ErrorHandling:
-
-
Select Case Err.Number
-
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 5491
-
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
I can't thank you enough for all your help.
zmbd 5,501
Expert Mod 4TB
Happy that we have that working.
I should have also pulled the field type with the code in Post#10 - didn't think of it at the time.
Thank you for posting your final code. I'm sure that alot of people will find this usefull! The built in form for getting information via email leaves much to be desired and if one does not have MS-Infopath installed, then one is stuck with it. By using the word document, the form can be user friendly and with a little code in the controls even the data can be validated.
Two things of note:
1) Post#17:Lines 32 and 33 These two lines are there only for debug work; thus, you can either comment them out or remove them.
2) Using two fields to store the "Sex" information may not be the best idea. "Assuming" that an individual is filling this form out for themself and then you shouls only need one field in the database to store the information.
-- Based on the assumption: - Modify the data base to have only one field to hold the sex of the respondant.
- Use the exsiting code to pull the information from the check box controls.
- I would then have a little logic to assign either "M," "F," or "X" to a new variable, I would then use this variable to assign the value to the record field and also pop-up an error message that there is a Mutant running loose :)
- You could also log the file name etc... to an errors table.
- With the "X" you can build a query that pulls all records with a Sex = "X" and attempt to connact the respondant. Then again, the question could be something where checking both boxes is logical... depends on the question; and more than likely still breaks the rules of "normalization"...
...which is something...
You should take a few moments to read about : > Database Normalization and Table Structures.
I'm still looking for a good tutorial site, my favorit went down due to i-frame issues.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: intl04 |
last post by:
Is it possible to create a Word form as the data entry form for an
Access database? I didn't see any reference to this possibility in my
Access books, so I'm sorry if this is a question that is...
|
by: srkartes |
last post by:
Is it possible to have a data entry form in MS Access lookup data for display purposes only. For example, I have error codes associated with specific errors in "Table A" and "Table B" that data is...
|
by: ScardyBob |
last post by:
Hello,
I am having trouble importing data from an Excel Worksheet. When I try
to import the data, everything works except certain columns that
contain dates, where it replaces the date with a...
|
by: mukeshsrivastav |
last post by:
dear sir
i want to move form excel to access.
i have 5 excel file having same formats and fields.now i want to import all data in one access table.
importing one file is easy .but importing and...
|
by: hexagram |
last post by:
hi guys can anybody can teach me how to do this scenario
Transfering Data of a form to a new form
The Scenario is:
I have a Delivery Receipt Form (Hardware) with a fields of the following
...
|
by: Debbiedo |
last post by:
I have a text file that I am importing into an Access table that was
generatred from data exported from a Word file. Several (about 20-30)
fields are from check boxes on the Word form. These fields...
|
by: Rotsey |
last post by:
Hi,
I am loading a tab control on a form.
The code loads textboxes and comboboxes and checkboxes, normal
data entry form that loads a table row of data.
I have a combo on the form above the...
|
by: Luke Bailey |
last post by:
I have a database that I've been building for quite some time. There are a few aspects of the database that I would like to pull over into Excel so that I can do some more advanced pivottable and...
|
by: billa856 |
last post by:
Hi
I am totaly new to MS Access.
I have one worksheet in excel in which I have data of company's inventory.
I want to know can I transfer data from a form made in MS Access to the Excel worksheet?...
|
by: BrokenMachine |
last post by:
Hi there,
I'm using Access 2003 and Word 2003 and I'm trying to import data from the word form to multiple access tables in a one to many relationship, but I can't seem to figure it out.
I have...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |