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
13 4630
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... ;)
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 32,341
Expert Mod 16PB
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 32,341
Expert Mod 16PB
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).
Just subscribing, will have a lok and see wehat I can do.
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
- [ContractID] - AutoNumber/Primary Key
- [FirstName]
- [LastName]
- [Company]
- [Phone]
- tblAddress
- [AddressID] - AutoNumber/PrimaryKey
- [ContractID] - LONG/Links to tblContracts.[ContractID] {MANY to 1}
- [Address]
- [State]
- [Zip1]
- [Zip2]
- 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
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
- [ContractID] - AutoNumber/Primary Key
- [FirstName]
- [LastName]
- [Company]
- [Phone]
- tblAddress
- [AddressID] - AutoNumber/PrimaryKey
- [ContractID] - LONG/Links to tblContracts.[ContractID] {MANY to 1}
- [Address]
- [State]
- [Zip1]
- [Zip2]
- 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 32,341
Expert Mod 16PB
ADezii got involved. I guess that means you're a lucky poster ;)
Thanks guys I'll give this a try and let you know how I get on :)
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.
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: - 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:
-
'Beginning of code block
-
Dim lngLastContractID
-
lngLastContractID = DLast("[ContractID]", "tblContracts")
-
.lngContractID = lngLastContractID + 1 'maintain same position in code
I tried the rst.Requery and it all works perfectly.
Thanks again for your time and effort. You've been a great help. :)
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.
Post your reply Sign in to post your reply or Sign up for a free account.
Similar topics
5 posts
views
Thread by Jerry Hull |
last post: by
|
1 post
views
Thread by svdh |
last post: by
|
4 posts
views
Thread by svdh2 |
last post: by
|
2 posts
views
Thread by BerkshireGuy |
last post: by
| |
4 posts
views
Thread by etuncer |
last post: by
|
7 posts
views
Thread by giladp1 |
last post: by
| | | | | | | | | | | | |