Importing Word into Access 2000. Please Help..

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi to all of you. I'm kind of new in programming and I have a question that I
posted in another group but they didn't answer me what I need.

let me explain what I have and what I need.

I have a database with computer repair requests. it has several forms where
now I'm adding records manually.

I have several tables. the most important are:
* employees: with id_employee, name, etc
* CRR (computer repair request): id, computer number, category, urgency, etc
* Urgency: id, description (high, low, average)
* category: id, description (CPU, monnitor, internet, etc)
* equipment: computer_id, id_employee (who has that computer) serial number,
etc.

I have more tables, but it makes no sense to put them all here.

I have then, a word document where people fill out their computer repair
requests. this word doc has "gray spaces". in some of them people just put in
some info and others they have a drop down menu to select from (ex:
urgency:........, and they have a drop down menu and they can select if it's
high, low or average).

- I have a form to add employees (Employees form) and to search for them
with all employee's info
- another form is to add computers (Equipment form). When we buy computers
we inventory them and we add them here.
- my last form is the computer Repair request form (CRR) where I have the
CRRs for all the equipment. If I'm in the computer # 3 for ex, I can press a
button and it takes me to all the CRR that computer has. and I go to the
employee that has that computer assigned too.

what I'm trying to do is to import the word fields in my database.
I'm now doing it manually, and I know I can save the word doc as text file
and I can import the data from there, but then, it brings me the info in the
grays areas in a new table. but I need them to be in my CRR form and like in
the word doc, it says the name for the employee and I need to insert in my
database the id_employee. the same with the urgency. I can get the "high" but
then I have to change that "high" to the number 1 that is the id for high...
am I explaining myself?? hhahaa

ok, so I need something (macro, query, don't know what) so I can press a
button and it imports the fields from the word doc into my database...can
anyone understand what I need??

I would be very greatful as I'm very new with this!
thank you a lot in advance for your help and sorry if I posted this question
in another group but I couldn't get the answer and I thought that this group
could help me better.

thank you again.
 
If the Word document is set up with formfields for user input, and
protected so that other parts of it can't be changed, you can extract
the values from the individual formfields using syntax like this air
code:

Dim oDoc As Object 'Word.Document
Dim strUrgency As String
Dim lngUrgency As Long
Dim strEmployeeName As String
Dim lngEmployeeID As Long
...

'Open the document in an instance of Word
Set oDoc = GetObject("D:\Folder\Filename.doc")

'Get the contents of the formfields
strEmployeeName = oDoc.FormFields("Employee").Range.Text
strUrgency = oDoc.FormFields("Urgency").Range.Text
...

'Close the document without saving
oDoc.Close False

'Get EmployeeID from Employee Name by looking it up
lngEmployeeName = DLookup( ... )

'Get from text version of urgency to the equivalent numeric value
...

'And so on, finally appending the new record to the table.
...

See http://word.mvps.org for more information on programming Word.
 
hi john.... thank you for your help... I'll give it a try and tomorrow I will
tell you how it went... it's going to take me a little bit as I'm new in
programming anything (yesterday I couldn't even program my coffee maker.
haha) but I'll try and I will tell you what happened.

thank you very much for your help. you have no idea how much I appreciate
it!!!
 
good morning john... before I start with your solution, I need you to answer
me a very stupid question.... where do I write that "air code"?? in a macro,
in a query, in a button source?? hahaa... I have no idea. As I told you,
I'm very new in programming anything. sorry for such a basic question...

and another one:
you have me write this:

how do I set up the or how does it know which field is the "urgency" field
in my .doc file?? do you know what i mean??? how do I set up that range so it
knows that urgency is urgency and not the employee one??? I hope I'm clear
enough on what I need... jajaja...

ok, thank you again!
 
by the way.... the gray areas that I have are like bookmarks....I'm trying to
put the code you gave to me in a button source and I'm having some errors
that I'm trying to figure them out, but when I debug the code, the odoc is my
doc (so that's ok) and then the strUrgency it shows a square (???) so it's
not selecting the proper word from the drop down menu or my bookmark... any
ideas on what I have to do about it????

thank you for your help!
 
thanks, I'm reading it now and I'll give it a try. I really appreciate your
help john! thank you again
 
john.... that's great!! it was just what I needed. I have one more question
for you.

I did exactly what the link that you sent to me said. not that long, just
with 2 or 3 form fields. I created the module and I saved it as it said I
should. when I tried to run it, it brings up the debugger and tells me:
"Compile error: user-defined type not defined" in the first line after the
Sub GetWordData(), that is: dim appWord as word.application. that is shown
like with black background and the Sub GetWordData() in yellow.....

do you know what am I doing wrong?????

once this works, I'll give it a try on my database..

thank you. you are the best!
 
You need to set a reference to the Word object library. In the VBA
Editor, go to Tools|References and check the box next to "Word X.X
Application Library" (where X.X is the version of Word you are using,
e.g. l0.0 for Word 2002). This will let Access recognise
Word.Application and so on.
 
hi john. thanks again for yourhelp... I'm running office 2000 and when I set
the reference to the word object I only have that Word 10.0 that you said is
only for Word 2002. I set it anyway, and I have now a error number:
2147417851. Automation error: the server threw an exception.... is it because
of that??

sorry if it's a basic question but I'm far from being an expert or even a
beginer.. jaja

ok, thank you again.
 
ok john... I'm messing my db up!!! I really need help. I don't know what I am
doing wrong or where to write the code down or what is going on here....
jajjaja.

I'm getting a lot of errors and I don't know how to fix them....

where do I have to write the code that you sent me before?? in a module? can
I write it on a button??
if it's possible I could send you my tables structures and my word doc and
you tell me where to write what.... is that ok??? can we do that???

ok, thank you in advance!!!!!
 
What line of code produces that error?

hi john. thanks again for yourhelp... I'm running office 2000 and when I set
the reference to the word object I only have that Word 10.0 that you said is
only for Word 2002. I set it anyway, and I have now a error number:
2147417851. Automation error: the server threw an exception.... is it because
of that??

sorry if it's a basic question but I'm far from being an expert or even a
beginer.. jaja

ok, thank you again.
Click to expand...
 
john... that error about the number 2147417851 is in the database that I copy
from the link you gave to me:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnimo01/html/imo1200.asp

when I run the module, it tells me that error and the cursor goes to the
first line. but it just tells me that and I'm not able to debug it. I'm
trying to make this one works and once it does, I can do the proper changes
so it will work in my real database.

thank you again for being so helpful!
here is the code.

Option Compare Database
Sub GetWordData()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDocName As String
Dim blnQuitWord As Boolean

On Error GoTo ErrorHandling
strDocName = "C:\Documents and Settings\luva\My Documents\contracts\" & _
InputBox("Enter the name of the Word contract " & _
"you want to import:", "Import Contract")

Set appWord = GetObject(, "Word.Application")
Set doc = appWord.Documents.Open(strDocName)

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\luva\My Documents\contracts\" & _
"Healthcare Contracts.mdb;"
rst.Open "tblContracts", cnn, _
adOpenKeyset, adLockOptimistic

With rst
.AddNew
!FirstName = doc.FormFields("fldfirst").Result
!LastName = doc.FormFields("fldlast").Result
!Gender = doc.FormFields("fldGender").Result
!BirthDate = doc.FormFields("flddate").Result
!AdditionalCoverage = _
doc.FormFields("fldAdditional").Result
.Update
.Close
End With
doc.Close
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "Contract Imported!"

Cleanup:
Set rst = Nothing
Set cnn = Nothing
Set doc = Nothing
Set appWord = Nothing
Exit Sub
ErrorHandling:
Select Case Err
Case -2147022986, 429
Set appWord = CreateObject("Word.Application")
blnQuitWord = True
Resume Next
Case 5121, 5174
MsgBox "You must select a valid Word document. " _
& "No data imported.", vbOKOnly, _
"Document Not Found"
Case 5941
MsgBox "The document you selected does not " _
& "contain the required form fields. " _
& "No data imported.", vbOKOnly, _
"Fields Not Found"
Case Else
MsgBox Err & ": " & Err.Description
End Select
GoTo Cleanup
End Sub
 
Back
Top