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 4827
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,568
Recognized Expert Moderator MVP
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,568
Recognized Expert Moderator MVP
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 8,834
Recognized Expert Expert
Just subscribing, will have a lok and see wehat I can do.
ADezii 8,834
Recognized Expert Expert
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
ADezii 8,834
Recognized Expert Expert 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,568
Recognized Expert Moderator MVP
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 :)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Jerry Hull |
last post by:
I'm working with a database developed by an untrained person over
several years - and on a network that has recently been upgraded with
a new server installed and MS office upgraded from 2K (I think - it
might have been XP) to 2003. The database is impressive, both in what
it does and the obtuse and inconsistent ways it works. There are
several hundred queries, for example, with no indication of where they
are used or if they are in fact...
|
by: svdh |
last post by:
I have posed a question last saturday and have advanced alot in the
meantime. But I am still not there
Problem is that I try to merging various fields from various tables in
one document in Word
1. Query..I want to keep the fields seperatred. I do not want to sent
on field with all accumulated languages from one person to Word. Each
language should appear in the document in a separate cell
Cross tables are not delivering the result I...
|
by: svdh2 |
last post by:
Dear All,
I have lately strugled more and more with Access, what started as a
simple database has brought me to the fundaments of Access.
I need to transfer fields from various tables to a word-document to
make a decent report (transferring a report from access to word means a
lot of loss on lay out, which I find a clear showcase of microsoft's
socalled software quality).
|
by: BerkshireGuy |
last post by:
I want to open a word document from an Access form and transfer data
from the Access form to the Word Document.
For instance, if a user clicks an option box on my access form, I want
to pass certain data to the next new line of a opened word doc.
Here is what I tried, but doesnt seem to work:
Dim objWord As Object
Dim NewRow As Row
|
by: southoz |
last post by:
Good ay all ,
I'm fairly new to access(a little over 5 weeks now). Since I'v started I have picked up a lot of useful information from forums such as this and in doing so will share that information with others. I have seen many request for information on mail merging an how to send data to word documents this is something I have put together with the answers given by others and I hope this will help a few newbies , I know there are easier ways...
| |
by: etuncer |
last post by:
Hello All,
I have Access 2003, and am trying to build a database for my small
company. I want to be able to create a word document based on the data
entered through a form. the real question is this: can Access create
the document and place it as an OLE object to the relevant table?
Any help is greatly appreciated.
Ricky
|
by: giladp1 |
last post by:
I found Albert Kallal's great "Super easy Word Merge" code in his site
at:
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html
Thanks Albert so much for sharing this.
I am looking for any comments about the use of the docmd.transfertext
method instead of the code Albert used for creating the text file.
Also, perhaps some ideas for coding the Subject Line of each email so
|
by: Robertf987 |
last post by:
Well, I think I've described what I want to do in the title here.
In the database, I have two main tables that contain the main data for the database. One for group expenditures, another for application details. I have a query for each of them to copy all the contents of the table into a new table, then delete all the data from the main table. I need to do this becasue once a year the data needs to be removed, ready for putting in new data. I...
|
by: sycryc |
last post by:
Here's my Q. I have a form in which users fill in text boxes and select checkboxes. After the form is completed, the user clicks the submit button which then transfers all to a word for editing, saving or emailing. My problem is trying get the checkboxes input to transfer to word and have the corresponding checkbox in word checked as well.
Can anyone suggest a solution?
Here is how I have been able to successfully transfer the text from...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |