writing to access from word form. (problem)

[dasher108]

I have spent some time searching for an answer here but with no luck. So now I have to be a pest and ask.

I have created a word form with various controls. On the bottom of the form is a submit button. When this button is pressed by the user, the word doc is mailed as a attachment, (that works) but I am having troube getting the word form to write the data to the access tables.

Please someone point me in the right direction, and I am sure I could figure it out from there.

Thanks
Lee

[tca_VB]

I was working on something similar and changed directions, but I found this site that may help you complete your code...

http://gregmaxey.mvps.org/Extract_Form_Data.htm

Good Luck

[Demosthine]

Good Evening.

That link provides a pretty good example of way to do it. Depending on the amount of fields in the Word Document, hard-coding that much information may be very time consuming. I would make life much easier and use a For Each ... Next statement based on the FormField's name.

To do this, start by making sure that all of the Textbox Fields in Word are exactly the same as the Field Name in Access. Then, the simple code below, you can insert the data in one fell swoop.


Dim fldCurrentField as FormField

...

For Each fldCurrentField in ThisDocument.FormFields
With fldCurrentfield
If .Result <> "" Then _
vRecordset(.Name) = .Result
End With
Next For


Now, to add a Control, all you have to do is add the FormField to your Document, name it correctly, and then add a Field to your Database with the EXACT same name as your field. No tampering with your code with each update. Removing a Control is even easier. You don't even have to tamper with the database.

Good Luck.
Scott

[fumei]

Good advice.

[dasher108]

This is my microsoft word form, I just cant figure out how to add the data entered to more then one table in thedatabase. The fields in the database have the exact same name as the controls on the form.


Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim blnQuitWord As Boolean
On Error GoTo ErrorHandling
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=G:\Rene Saade\" & _
"Product Approval.mdb;"
rst.Open "contact_tbl", cnn, _
adOpenKeyset, adLockOptimistic
With rst
.AddNew
'!contact_id = doc.FormFields("contact_id").Result
!txtoriginator = txtoriginator.Text
!txtdepartment = txtdepartment.Text
!txtphone = txtphone.Text
!txtdatesubmitted = txtdatesubmitted.Text
!txtdaterequired = txtdaterequired.Text
.Update
.Close
End With
cnn.Close
MsgBox "Request added to Database"
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 Subhttp://i55.photobucket.com/albums/g150/dasher108/example.jpg

[Demosthine]

Good Evening There.

I'm glad to hear you've taken the advise of keeping the Field Names and the Control Names the same. It truly does make life easier.

Now, in regards to adding the data to multiple tables, you'll need to re-execute your rst.Open method and reference your next Table. Based on the Form you attached, it looks like you will be having a Table 'Products' for example.

In your code, once you execute your .Update command on Line 18, you turn around and execute a rst.Open "Product_tbl"... and process the data for the subsequent data. Keeping re-executing your rst,Open method until you have accessed and stored all of your data.

Any other problems, I'm here.
Scott

[DubaiDave]

Scott,

I have a word form that imports ok into Access. I want to now import some text from a field but want to process the text (modify some special characters eg remove tabs but add spaces to give same look in access). At what point should I do this? Before, during or after import to Access?

Regards

David

[RandRob]

Dim fldCurrentField as FormField

...

For Each fldCurrentField in ThisDocument.FormFields
With fldCurrentfield
If .Result <> "" Then _
vRecordset(.Name) = .Result
End With
Next For



I am working on a database very similiar to this i used the code from the above link and it worked very nicely
But i am hoping to insert the code you placed in it
i am rather new at all this
could you show me what i would need to remove from the linked code and where i would put what you have given here

Thanks and it is much apreciated