Word Form to Access Tables Data Transfer

BrokenMachine

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 used the following example to create a word form that transfers data to a single access table

http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx

This seems to work fine. My problem is that I want to record several addresses on the form rather than just one. So I have set up a second table to record the addresses called tblAddresses and moved the fields Address, City, State and Zip to this table. I have linked the two tables in a one-to-many relationship with a field called ContractID.

On the Word form itself I have set up bookmarks for the first address as fldAddress1, fldState1, fldState1, fldZip1 and Zip2.
And for the second address fldAddress2, fldState2, fldfldZip3 and fldZip4

I'm just not sure how to change the code of the module so it puts the address data from the word form into tbladdress then adds another record for the second address and keeps it all linked to the record in tblContracts.

Does anyone have any ideas?

Thanks

blad3runn69

one question jumps to mind, why would you even attempt to import loose text file into a database?

p.s use access forms or @ the v.least excel... ;)

BrokenMachine

I've manipulated the example for my own personal use but I'm using this example as its easier than trying to explain my own database and form.

Basically other people will be filling in lots of these Word forms and rather than having to retype all this information my self, as the other people don't have access to the database, i can just import the data straight into the database.

NeoPa

one question jumps to mind, why would you even attempt to import loose text file into a database?

p.s use access forms or @ the v.least excel... ;)

As you're clearly new you can be forgiven for making wild assumptions that you understand the OP's question better than they do.
Please remember in future that not everything is as obvious as you seem to assume. Many questions are phrased in a particular way simply to express the problem as simply and clearly as possible.

I suppose another way of expresssing this is "If you haven't anything helpful to add - consider wisdom is best expressed in fewer words."

NeoPa

I've manipulated the example for my own personal use but I'm using this example as its easier than trying to explain my own database and form.

Basically other people will be filling in lots of these Word forms and rather than having to retype all this information my self, as the other people don't have access to the database, i can just import the data straight into the database.

I'm sorry I can't be more help. I VERY rarely work in MS Word.
The best I can do that may help, is point you to an example of processing through datasets. I may have entirely missed the point, but just in case it helps - (Basic DAO recordset loop using two recordsets).

ADezii

Just subscribing, will have a lok and see wehat I can do.

ADezii

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 used the following example to create a word form that transfers data to a single access table

http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx

This seems to work fine. My problem is that I want to record several addresses on the form rather than just one. So I have set up a second table to record the addresses called tblAddresses and moved the fields Address, City, State and Zip to this table. I have linked the two tables in a one-to-many relationship with a field called ContractID.

On the Word form itself I have set up bookmarks for the first address as fldAddress1, fldState1, fldState1, fldZip1 and Zip2.
And for the second address fldAddress2, fldState2, fldfldZip3 and fldZip4

I'm just not sure how to change the code of the module so it puts the address data from the word form into tbladdress then adds another record for the second address and keeps it all linked to the record in tblContracts.

Does anyone have any ideas?

Thanks

I've created a sort of logical code Template for you, but I must honestly say that I'm not sure if it will even work, and since I don't have the time to create the necessary Components, I cannot test it. In theory, it should work. I've intentionally shortened the code, and omitted Error Checking which you can easily incorporate later. Before I post the code, a few assumptions:

tblContracts
1. [ContractID] - AutoNumber/Primary Key
2. [FirstName]
3. [LastName]
4. [Company]
5. [Phone]
tblAddress
1. [AddressID] - AutoNumber/PrimaryKey
2. [ContractID] - LONG/Links to tblContracts.[ContractID] {MANY to 1}
3. [Address]
4. [State]
5. [Zip1]
6. [Zip2]
Keep in touch and let me know how you make out, I'm curious.

Expand|Select|Wrap|Line Numbers

 
Dim appWord As Word.Application, doc As Word.Document

Dim cnn As New ADODB.Connection, rst As New ADODB.Recordset

Dim rstAddress As New ADODB.Recordset, strDocName As String

Dim blnQuitWord As Boolean, Msg As String, lngContractID As Long
 
Msg = "Enter the Name of the Word Contract you want to Import:"
 
'Reaaaaaally a bad idea, use a File Dialog instead and Filter File Types

strDocName = "C:\Contracts\" & InputBox(Msg, "Import Contract")
 
Set appWord = GetObject(, "Word.Application")

Set doc = appWord.Documents.Open(strDocName)
 
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = C:\My Documents\" & _

         "Healthcare Contracts.mdb;"
 
rst.Open "tblContracts", cnn, adOpenKeyset, adLockOptimistic

rstAddress.Open "tblAddress", cnn, adOpenKeyset, adLockOptimistic
 
'Add a Record to tblContracts, [ContractID] will auto increment on its own

With rst

  .AddNew

    ![FirstName] = doc.FormFields("fldFirstName").Result

    ![LastName] = doc.FormFields("fldLastName").Result

    ![Company] = doc.FormFields("fldCompany").Result

    ![Phone] = doc.FormFields("fldPhone").Result

  .Update

End With

rst.Close: Set rst = Nothing
 
'**************************************************************************
 
'We need the last entered [ContractID], so we can add related Addresses

lngContractID = DLast("[ContractID]", "tblContracts")
 
With rstAddress

  .AddNew

    ![Address] = doc.FormFields("fldAddress1").Result

    ![State] = doc.FormFields("fldState1").Result

    ![Zip1] = doc.FormFields("fldZip1").Result

    ![Zip2] = doc.FormFields("fldZip2").Result

  .Update
 
  .AddNew

    ![Address] = doc.FormFields("fldAddress2").Result

    ![State] = doc.FormFields("fldState2").Result

    ![Zip1] = doc.FormFields("fldZip3").Result

    ![Zip2] = doc.FormFields("fldZip4").Result

  .Update

End With

rstAddress.Close: Set rstAddress = Nothing
 
'**************************************************************************
 
doc.Close: Set doc = Nothing

cnn.Close: Set cnn = Nothing

appWord.Quit: Set appWord = Nothing

ADezii

Forgot 2 Critical lines of code, namely lines 38 and 46:
I've created a sort of logical code Template for you, but I must honestly say that I'm not sure if it will even work, and since I don't have the time to create the necessary Components, I cannot test it. In theory, it should work. I've intentionally shortened the code, and omitted Error Checking which you can easily incorporate later. Before I post the code, a few assumptions:

tblContracts
1. [ContractID] - AutoNumber/Primary Key
2. [FirstName]
3. [LastName]
4. [Company]
5. [Phone]
tblAddress
1. [AddressID] - AutoNumber/PrimaryKey
2. [ContractID] - LONG/Links to tblContracts.[ContractID] {MANY to 1}
3. [Address]
4. [State]
5. [Zip1]
6. [Zip2]
Keep in touch and let me know how you make out, I'm curious.

Expand|Select|Wrap|Line Numbers

 
Dim appWord As Word.Application, doc As Word.Document

Dim cnn As New ADODB.Connection, rst As New ADODB.Recordset

Dim rstAddress As New ADODB.Recordset, strDocName As String

Dim blnQuitWord As Boolean, Msg As String, lngContractID As Long
 
Msg = "Enter the Name of the Word Contract you want to Import:"
 
'Reaaaaaally a bad idea, use a File Dialog instead and Filter File Types

strDocName = "C:\Contracts\" & InputBox(Msg, "Import Contract")
 
Set appWord = GetObject(, "Word.Application")

Set doc = appWord.Documents.Open(strDocName)
 
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = C:\My Documents\" & _

         "Healthcare Contracts.mdb;"
 
rst.Open "tblContracts", cnn, adOpenKeyset, adLockOptimistic

rstAddress.Open "tblAddress", cnn, adOpenKeyset, adLockOptimistic
 
'Add a Record to tblContracts, [ContractID] will auto increment on its own

With rst

  .AddNew

    ![FirstName] = doc.FormFields("fldFirstName").Result

    ![LastName] = doc.FormFields("fldLastName").Result

    ![Company] = doc.FormFields("fldCompany").Result

    ![Phone] = doc.FormFields("fldPhone").Result

  .Update

End With

rst.Close: Set rst = Nothing
 
'**************************************************************************
 
'We need the last entered [ContractID], so we can add related Addresses

lngContractID = DLast("[ContractID]", "tblContracts")
 
With rstAddress

  .AddNew

    ![ContractID] = lngContractID        'Critical step that allows adding Child Records

    ![Address] = doc.FormFields("fldAddress1").Result

    ![State] = doc.FormFields("fldState1").Result

    ![Zip1] = doc.FormFields("fldZip1").Result

    ![Zip2] = doc.FormFields("fldZip2").Result

  .Update
 
  .AddNew

    ![ContractID] = lngContractID        'Critical step that allows adding Child Records

    ![Address] = doc.FormFields("fldAddress2").Result

    ![State] = doc.FormFields("fldState2").Result

    ![Zip1] = doc.FormFields("fldZip3").Result

    ![Zip2] = doc.FormFields("fldZip4").Result

  .Update

End With

rstAddress.Close: Set rstAddress = Nothing
 
'**************************************************************************
 
doc.Close: Set doc = Nothing

cnn.Close: Set cnn = Nothing

appWord.Quit: Set appWord = Nothing

NeoPa

ADezii got involved. I guess that means you're a lucky poster ;)

BrokenMachine

Thanks guys I'll give this a try and let you know how I get on :)

BrokenMachine

The code almost works there's just a slight problem. When I run the module it runs until it reaches the code:

Expand|Select|Wrap|Line Numbers

.lngContractID = DLast("[ContractID]", "tblContracts")

Then stops. The data is entered in tblContracts but doesn't add the addresses to tblAddress. If I then run the module again the module runs all the way through but I then end up with 2 records in tblContracts. The first record has the addresses and the second does not.

I'm guessing the first time the module runs it can't find the record from tblContracts but the second time the module runs the record is already there and so adds the addresses but at the same time adds another record to tblContracts.

Is there a way to fix this?

I really appreciate your help guys. Thanks again.

ADezii

The code almost works there's just a slight problem. When I run the module it runs until it reaches the code:

Expand|Select|Wrap|Line Numbers

.lngContractID = DLast("[ContractID]", "tblContracts")

Then stops. The data is entered in tblContracts but doesn't add the addresses to tblAddress. If I then run the module again the module runs all the way through but I then end up with 2 records in tblContracts. The first record has the addresses and the second does not.

I'm guessing the first time the module runs it can't find the record from tblContracts but the second time the module runs the record is already there and so adds the addresses but at the same time adds another record to tblContracts.

Is there a way to fix this?

I really appreciate your help guys. Thanks again.

Try one of two things:

rst.Requery between Lines 28 and 29
OR
Pick up trhe value of the last [ContractID] at the beginning of the code segment, assign it to a Variable, then increment it by 1 in the code line, something like:
Expand|Select|Wrap|Line Numbers
1. 'Beginning of code block
2. Dim lngLastContractID
3. lngLastContractID = DLast("[ContractID]", "tblContracts")
4. .lngContractID = lngLastContractID + 1 'maintain same position in code

BrokenMachine

I tried the rst.Requery and it all works perfectly.

Thanks again for your time and effort. You've been a great help. :)

ADezii

I tried the rst.Requery and it all works perfectly.

Thanks again for your time and effort. You've been a great help. :)

You are quite welcome.

Word Form to Access Tables Data Transfer

Post your reply

Similar topics

Question stats

	Ask Question
Home Questions Articles Browse Topics Latest Top Members FAQ