?
Solved

Pulling Data from an Access Database to a Word Document (from all the records for a given field in a table)

Posted on 2010-04-10
10
Medium Priority
?
361 Views
Last Modified: 2013-12-20
I wish to be able to pull data at the current cursor location in my word document from a specific field (ThirdField) from a particular table (Messages) in my access database (MyDatabase) in response to the click event of a button (Button1) on my Visual Basic For Applications (VBA) Form.

This table (Messages) has four fields: note_id, date, client_id, ThirdField.
I would like to pull Data from the ThirdField from All the Records in the Table where client_id is 222 in a chronological order (ascending or descending based on the date they were saved).

That is I would like to pull data from ALL the records from ThirdField for Client 222 from the “Messages” table in a chronological order.

The name and path of my source database are as follows:
Source=G:\ My Folder\MyDatabase.mdb


This is question is related in theme to a previous question I asked on this Forum.

My original question on this theme and its solution are posted here:

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_25798875.html#a30292286

Many thanks for your help.
0
Comment
Question by:FaheemAhmadGul
  • 5
  • 3
  • 2
10 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 30348197
Sub Button3_Click()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strConn As String
    Dim strSQL As String
   
    Dim MyText As String
    Dim MyRange As Object
    Set MyRange = Selection.Range

    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Folder\MyDataBase.mdb;User Id=admin;Password=;"
    cn.Open strConn
    strSQL = "SELECT ThirdField FROM Messages WHERE client_id = 222 order by [date]"
    rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic, adCmdText
    While Not rs.EOF Then
        MyText = rs.Fields("ThirdField").Value
        Selection.Fields.Add Range:=Selection.Range, _
            Type:=wdFieldQuote, Text:=MyText
        rs.MoveNext
    Loop
    rs.Close
    cn.Close
End Sub

AW
0
 
LVL 1

Author Comment

by:FaheemAhmadGul
ID: 30391687
Many thanks for your response to my question.
The code provided very kindly by you perhaps needs some editing.

When I pasted the code at it is the following line got highlighted as red:

While Not rs.EOF Then

Assuming this may be a typing error I changed this line to:

Do While Not rs.EOF

and then it did not come up as red. However, when I run the code after making the above change I get the following error message:

Run-time error ‘-2147217904(80040e10)’:
No value given for one or more required parameters

With the following line highlighted when I click the debug button

rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic, adCmdText

0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 30402465
I was under the assumption that you were using the same database( and code) as was used in your previous question, which you referred to.  That was intended to only pull a single record.  If you simply change THAT code as I suggested, you should then be able to pull ALL of the records for client_id = 222.  

What are the values of the variables in the line which causes the error?

AW
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 1

Author Comment

by:FaheemAhmadGul
ID: 30422703
I am using the code on exactly the same database and the table, the only difference is that I have changed the names of the table (t_history from Messages) and the name of the field I want pull data from to Bed02 from (ThirdField). I include the code I attaching the code as I am using it now.
I changed the names of the table, and field in the code provided by to correspond with the new names in my database and after checking everything very carefully, the code provided by you is actually working now, but with a few problems.

It is pulling data from different records from the field Bed02 (previous ThirdField) but it types them in the word document without any space between characters of a word and without any space between data from different records. It is also not able to pull any number data and replaces it with a strange character.

This is how it pulled and presented data from two records for the field (Bed02) from the table.  

ThiswastheFirstEntryforPatientÞThisistheLatestEntryforPatientNumbe

The actual data (text) in the first and last record for this patient were two sentences and I would have liked it to show in the word document as below:

This was the First Entry for Patient 222
This is the Latest Entry for Patient Number 222

Thanks again for your continued support.

Private Sub Button1_Click()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strConn As String
    Dim strSQL As String
    
    Dim MyText As String
    Dim MyRange As Object
    Set MyRange = Selection.Range

    
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\Documents\Writer\QuickDatabase.mdb;User Id=admin;Password=;"
    cn.Open strConn
    strSQL = "SELECT Bed02 FROM t_history WHERE patient_id = 222"
    rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic, adCmdText
   
    
    Do While Not rs.EOF

       MyText = rs.Fields("Bed02").Value
       Selection.Fields.Add Range:=Selection.Range, _
           Type:=wdFieldQuote, Text:=MyText
       rs.MoveNext
   Loop
   rs.Close
   cn.Close


End Sub

Open in new window

0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 1500 total points
ID: 30453288
Private
 
Sub
 
Button1_Click
()

    
Dim
 cn 
As
 
New
 ADODB
.
Connection

    
Dim
 rs 
As
 
New
 ADODB
.
Recordset

    
Dim
 strConn 
As
 
String

    
Dim
 strSQL 
As
 
String

    
    
Dim
 
MyText
 
As
 
String

    
Dim
 
MyRange
 
As
 
Object

    
Set
 
MyRange
 
=
 
Selection
.
Range


    
    strConn 
=
 
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\Documents\Writer\QuickDatabase.mdb;User Id=admin;Password=;"

    cn
.
Open
 strConn
    strSQL 
=
 
"SELECT Bed02 FROM t_history WHERE patient_id = 222"

    rs
.
Open
 strSQL
,
 cn
,
 adOpenDynamic
,
 adLockOptimistic
,
 adCmdText
    MyText = "This is the first entry for Patient 222" & vbCrLf
    Selection.Fields.Add Range:=Selection.Range, _
           Type:=wdFieldQuote, Text:=MyText



     
Do
 
While
 
Not
 rs
.
EOF

       
MyText
 
=
 rs
.
Fields
(
"Bed02"
).
Value
 & vbCRLF
       
Selection
.
Fields
.
Add
 
Range
:=
Selection
.
Range
,
 _
           
Type
:=
wdFieldQuote
,
 
Text
:=
MyText

       rs
.
MoveNext

   
Loop

   rs
.
Close

   cn
.
Close

   MyText = "This is the last entry for Patient 222"
    Selection.Fields.Add Range:=Selection.Range, _
           Type:=wdFieldQuote, Text:=MyText




End
 
Sub

AW

Open in new window

0
 
LVL 1

Author Comment

by:FaheemAhmadGul
ID: 30549187
Thank you for modified solution. Unfortunately it has not worked either.
The result from using the code was follows:

ThisisthefirstentryforPatientÞThiswastheFirstEntryforPatientÞThisistheLatestEntryforPatientNumberÞThisisthelastentryforPatientÞ

I think that perhaps I have not been able to explain the problem well in my previous comment.
What I meant to say was that in my database there is a table name t_history. This table has two records for Patient_id 222 at present.
The first of these two records has in has the following sentence the field named Bed02:
This is the first entry for Patient 222.

The second of these two records has the following sentence in the field named Bed02:
This is the 2nd entry for Patient 222.

I am hoping that the code for Button1 Click event will pull both these records for the Field Bed02 and get them typed on the word document such that first record gets typed first and the second record gets typed on the new line.
So the end result of pulling these records through Button1 Click will look like this:

This is the first entry for Patient 222.
This is the Latest entry for Patient 222.

I am attaching the database file that I am using for this code in case that helps.
I am a beginner and I asked posted this question only to understand a concept.
If I could write pseudo code for this problem:
I think it will
Pull the first record for the patient_id 222 from the Field named Bed02.
Move to the next record (2nd record) for  patient_id 222 from the Field named Bed02.
Then Moved to the next (third record) for patient_id 222 from the Field named Bed02.
Keep on doing this until you reach the last record for patient_id 222 in this table and stop after pulling it into the word.
QuickDatabase.mdb
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 30567478
Try this:
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 30568278
Missed the code snippet:
Private Sub Button1_Click()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strConn As String
    Dim strSQL As String
    
    Dim MyText As String
    Dim MyRange As Object
    Set MyRange = Selection.Range

    MyText = ""

    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\Documents\Writer\QuickDatabase.mdb;User Id=admin;Password=;"
    cn.Open strConn
    strSQL = "SELECT Bed02 FROM t_history WHERE patient_id = 222"
    rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic, adCmdText
   
    
    Do While Not rs.EOF
      If MyText = "" Then
             MyText = rs.Fields("Bed02").Value
      Else
             MyText = MyText & vbCRLF & rs.Fields("Bed02").Value
      End if
      rs.MoveNext
   Loop
   rs.Close
   cn.Close
           Selection.Fields.Add Range:=Selection.Range, _
           Type:=wdFieldQuote, Text:=MyText


End Sub

Open in new window

0
 
LVL 1

Author Comment

by:FaheemAhmadGul
ID: 30591716
Thank you for your solution. However, the output from this code was almost the same as from the previous solution. In the word document it looked like this:

ThiswastheFirstEntryforPatientÞThisistheSecondEntryforPatientNumbe

As you will note, the code has pulled the contents of the Field Bed02 from the table but without any space between the letters of the sentences stored in the relevant table field and without inserting any Line Break between the contents of Bed02 from the first and second record in the table.

0
 
LVL 1

Author Closing Comment

by:FaheemAhmadGul
ID: 31761004
The solution did not solve the problem completely, but because it got quite close to solving it I am accepting it as a solution.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

594 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question