By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,531 Members | 2,219 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,531 IT Pros & Developers. It's quick & easy.

Word Form to Access Tables Data Transfer

P: 6
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
Feb 21 '08 #1
Share this Question
Share on Google+
13 Replies


P: 59
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... ;)
Feb 21 '08 #2

P: 6
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.
Feb 21 '08 #3

NeoPa
Expert Mod 15k+
P: 31,494
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."
Feb 22 '08 #4

NeoPa
Expert Mod 15k+
P: 31,494
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).
Feb 22 '08 #5

ADezii
Expert 5K+
P: 8,638
Just subscribing, will have a lok and see wehat I can do.
Feb 22 '08 #6

ADezii
Expert 5K+
P: 8,638
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.
Expand|Select|Wrap|Line Numbers
  1. Dim appWord As Word.Application, doc As Word.Document
  2. Dim cnn As New ADODB.Connection, rst As New ADODB.Recordset
  3. Dim rstAddress As New ADODB.Recordset, strDocName As String
  4. Dim blnQuitWord As Boolean, Msg As String, lngContractID As Long
  5.  
  6. Msg = "Enter the Name of the Word Contract you want to Import:"
  7.  
  8. 'Reaaaaaally a bad idea, use a File Dialog instead and Filter File Types
  9. strDocName = "C:\Contracts\" & InputBox(Msg, "Import Contract")
  10.  
  11. Set appWord = GetObject(, "Word.Application")
  12. Set doc = appWord.Documents.Open(strDocName)
  13.  
  14. cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = C:\My Documents\" & _
  15.          "Healthcare Contracts.mdb;"
  16.  
  17. rst.Open "tblContracts", cnn, adOpenKeyset, adLockOptimistic
  18. rstAddress.Open "tblAddress", cnn, adOpenKeyset, adLockOptimistic
  19.  
  20. 'Add a Record to tblContracts, [ContractID] will auto increment on its own
  21. With rst
  22.   .AddNew
  23.     ![FirstName] = doc.FormFields("fldFirstName").Result
  24.     ![LastName] = doc.FormFields("fldLastName").Result
  25.     ![Company] = doc.FormFields("fldCompany").Result
  26.     ![Phone] = doc.FormFields("fldPhone").Result
  27.   .Update
  28. End With
  29. rst.Close: Set rst = Nothing
  30.  
  31. '**************************************************************************
  32.  
  33. 'We need the last entered [ContractID], so we can add related Addresses
  34. lngContractID = DLast("[ContractID]", "tblContracts")
  35.  
  36. With rstAddress
  37.   .AddNew
  38.     ![Address] = doc.FormFields("fldAddress1").Result
  39.     ![State] = doc.FormFields("fldState1").Result
  40.     ![Zip1] = doc.FormFields("fldZip1").Result
  41.     ![Zip2] = doc.FormFields("fldZip2").Result
  42.   .Update
  43.  
  44.   .AddNew
  45.     ![Address] = doc.FormFields("fldAddress2").Result
  46.     ![State] = doc.FormFields("fldState2").Result
  47.     ![Zip1] = doc.FormFields("fldZip3").Result
  48.     ![Zip2] = doc.FormFields("fldZip4").Result
  49.   .Update
  50. End With
  51. rstAddress.Close: Set rstAddress = Nothing
  52.  
  53. '**************************************************************************
  54.  
  55. doc.Close: Set doc = Nothing
  56. cnn.Close: Set cnn = Nothing
  57. appWord.Quit: Set appWord = Nothing
Feb 23 '08 #7

ADezii
Expert 5K+
P: 8,638
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.
Expand|Select|Wrap|Line Numbers
  1. Dim appWord As Word.Application, doc As Word.Document
  2. Dim cnn As New ADODB.Connection, rst As New ADODB.Recordset
  3. Dim rstAddress As New ADODB.Recordset, strDocName As String
  4. Dim blnQuitWord As Boolean, Msg As String, lngContractID As Long
  5.  
  6. Msg = "Enter the Name of the Word Contract you want to Import:"
  7.  
  8. 'Reaaaaaally a bad idea, use a File Dialog instead and Filter File Types
  9. strDocName = "C:\Contracts\" & InputBox(Msg, "Import Contract")
  10.  
  11. Set appWord = GetObject(, "Word.Application")
  12. Set doc = appWord.Documents.Open(strDocName)
  13.  
  14. cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = C:\My Documents\" & _
  15.          "Healthcare Contracts.mdb;"
  16.  
  17. rst.Open "tblContracts", cnn, adOpenKeyset, adLockOptimistic
  18. rstAddress.Open "tblAddress", cnn, adOpenKeyset, adLockOptimistic
  19.  
  20. 'Add a Record to tblContracts, [ContractID] will auto increment on its own
  21. With rst
  22.   .AddNew
  23.     ![FirstName] = doc.FormFields("fldFirstName").Result
  24.     ![LastName] = doc.FormFields("fldLastName").Result
  25.     ![Company] = doc.FormFields("fldCompany").Result
  26.     ![Phone] = doc.FormFields("fldPhone").Result
  27.   .Update
  28. End With
  29. rst.Close: Set rst = Nothing
  30.  
  31. '**************************************************************************
  32.  
  33. 'We need the last entered [ContractID], so we can add related Addresses
  34. lngContractID = DLast("[ContractID]", "tblContracts")
  35.  
  36. With rstAddress
  37.   .AddNew
  38.     ![ContractID] = lngContractID        'Critical step that allows adding Child Records
  39.     ![Address] = doc.FormFields("fldAddress1").Result
  40.     ![State] = doc.FormFields("fldState1").Result
  41.     ![Zip1] = doc.FormFields("fldZip1").Result
  42.     ![Zip2] = doc.FormFields("fldZip2").Result
  43.   .Update
  44.  
  45.   .AddNew
  46.     ![ContractID] = lngContractID        'Critical step that allows adding Child Records
  47.     ![Address] = doc.FormFields("fldAddress2").Result
  48.     ![State] = doc.FormFields("fldState2").Result
  49.     ![Zip1] = doc.FormFields("fldZip3").Result
  50.     ![Zip2] = doc.FormFields("fldZip4").Result
  51.   .Update
  52. End With
  53. rstAddress.Close: Set rstAddress = Nothing
  54.  
  55. '**************************************************************************
  56.  
  57. doc.Close: Set doc = Nothing
  58. cnn.Close: Set cnn = Nothing
  59. appWord.Quit: Set appWord = Nothing
Feb 23 '08 #8

NeoPa
Expert Mod 15k+
P: 31,494
ADezii got involved. I guess that means you're a lucky poster ;)
Feb 23 '08 #9

P: 6
Thanks guys I'll give this a try and let you know how I get on :)
Feb 25 '08 #10

P: 6
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
  1. .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.
Feb 25 '08 #11

ADezii
Expert 5K+
P: 8,638
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
  1. .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:
    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
Feb 25 '08 #12

P: 6
I tried the rst.Requery and it all works perfectly.

Thanks again for your time and effort. You've been a great help. :)
Feb 25 '08 #13

ADezii
Expert 5K+
P: 8,638
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.
Feb 25 '08 #14

Post your reply

Sign in to post your reply or Sign up for a free account.