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:

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

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:

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

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:

.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:

.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:

1. rst.Requery between Lines 28 and 29
2. OR
3. 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:
   

   'Beginning of code block
   Dim lngLastContractID
   lngLastContractID = DLast("[ContractID]", "tblContracts")
   .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.