473,406 Members | 2,371 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,406 software developers and data experts.

Problem exporting multivalued field to Word 2007 template using Access 2007

I've made an Access 2007 database to store information about the lessonplans I have to make every schoolyear.
I want to export the data form the table "Lesvoorbereiding" (Lessonplans in English) to Word using a readymade template (NLD-LV1.dotx). All works well except for the multivalued fields 'Lesuur' (Lessonhour) and 'Werkvormen' (Used activities) and the field 'Lesverloop' (Lessonflow?), i.e. a field where you can link to documents, etc. on the harddisk of you pc. I don't know the English term for the property of this field, but in Dutch it has the property 'bijlage' (attachment).
This is the code I use to activate the procedure when clicking the "KnopLesAfdrukken"-button in the form Lesvoorbereiding (Lessonplans):

Expand|Select|Wrap|Line Numbers
  1. Private Sub KnopLesAfdrukken_Click()     'ButtonPrintLesson
  2. 'Export a chosen lessonplan to Word 
  3.     On Error Resume Next
  4.         Dim rstLesvoorbereiding As New ADODB.Recordset
  5.         Dim sSQL As String
  6.         Dim WordObj As New Word.Application
  7.         sSQL = "SELECT * FROM Lesvoorbereiding " & "WHERE LesId = " & Forms!FormLesvoorbereiding![LesId]
  8.         rstLesvoorbereiding.Open sSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
  9.         If rstLesvoorbereiding.EOF Then
  10.             MsgBox "Kan de les niet afdrukken.", vbOKOnly     'Can't print the lesson.
  11.             Exit Sub
  12.         End If
  13.  
  14.         DoCmd.Hourglass True
  15.  
  16.         Set WordObj = GetObject(, "Word.Application")
  17.         If Err.Number <> 0 Then
  18.             Set WordObj = CreateObject("Word.Application")
  19.         End If
  20.  
  21.         WordObj.Documents.Add Template:="C:\Documents and Settings\Ida\Application Data\Microsoft\Sjablonen\NLD-LV1.dotx", NewTemplate:=False
  22.  
  23.         With WordObj.Selection
  24.             .GoTo what:=wdGoToBookmark, Name:="Klas"     'Class
  25.             .TypeText rstLesvoorbereiding![Klas]
  26.  
  27.             .GoTo what:=wdGoToBookmark, Name:="Datum"     'Date
  28.             .TypeText rstLesvoorbereiding![Datum]
  29.  
  30.             Do Until rstLesvoorbereiding.EOF
  31.                 .GoTo what:=wdGoToBookmark, Name:="Lesuur"     'Lessonhour
  32.                 .TypeText rstLesvoorbereiding![Lesuur_Value] & ", "
  33.                 rstLesvoorbereiding.MoveNext
  34.             Loop
  35.  
  36.             .GoTo what:=wdGoToBookmark, Name:="LesId"     'LessonId
  37.             .TypeText rstLesvoorbereiding![LesId]
  38.  
  39.             .GoTo what:=wdGoToBookmark, Name:="Onderwerp"     'Subject
  40.             .TypeText rstLesvoorbereiding![Onderwerp]
  41.  
  42.             .GoTo what:=wdGoToBookmark, Name:="Materiaal"     'Used materials
  43.             .TypeText rstLesvoorbereiding![Materiaal]
  44.  
  45.             Do Until rstLesvoorbereiding.EOF
    .GoTo what:=wdGoToBookmark, Name:="Lesverloop"     'Lessonflow, pupils worksheets, etc.
                If IsNull(rstLesvoorbereiding![FileName]) Then
  46.                 .TypeText " "
  47.             Else
  48.                 .TypeText rstLesvoorbereiding![FileName]
  49.             End If
    Loop
                 Do Until rstLesvoorbereiding.EOF  
  50.             .GoTo what:=wdGoToBookmark, Name:="Werkvormen"     'Activities
  51.             .TypeText rstLesvoorbereiding![Werkvormen_Value]
    Loop
                .GoTo what:=wdGoToBookmark, Name:="Opmerkingen"     'Remarks
  52.             If IsNull(rstLesvoorbereiding![Opmerkingen]) Then
  53.                 .TypeText " "
  54.             Else
  55.                 .TypeText rstLesvoorbereiding![Opmerkingen]
  56.             End If
  57.  
  58.         DoEvents
  59.         WordObj.Activate
  60.         WordObj.Visible = True
  61.     End With
  62.  
  63.     Set WordObj = Nothing
  64.     DoCmd.Hourglass False
  65.  
  66. End Sub
Using this code I can export the value of all records in the table, except for the value of the fields mentioned above.
I've read about every page in the Access helpfile (Dutch and English version), but can't find an answer.
My knowlegde of VBA is very basic and limited. Can anybody tell me what is wrong or missing?
Nov 2 '08 #1
1 3484
Found it myself, eventually, after lots of experimenting:


Expand|Select|Wrap|Line Numbers
  1. Private Sub KnopLesAfdrukken_Click()
  2. 'Een lesvoorbereiding automatisch laten afdrukken in Word
  3.     On Error Resume Next
  4.         Dim rstLesvoorbereiding As New ADODB.Recordset
  5.         Dim childrst As New ADODB.Recordset
  6.         Dim sSQL As String, sSQL1 As String
  7.         Dim WordObj As New Word.Application
  8.         sSQL = "SELECT * FROM Lesvoorbereiding " & "WHERE LesId = " & Forms!FormLesvoorbereiding![LesId]
  9.         rstLesvoorbereiding.Open sSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
  10.  
  11.         If rstLesvoorbereiding.EOF Then
  12.             MsgBox "Kan de les niet afdrukken, want er zijn nog geen lesgegevens voor deze les.", vbOKOnly
  13.             Exit Sub
  14.         End If
  15.  
  16.         DoCmd.Hourglass True
  17.  
  18. 'Kijken of Word actief is
  19.         Set WordObj = GetObject(, "Word.Application")
  20.         If Err.Number <> 0 Then
  21. 'Een Error wordt opgeroepen als Word niet actief is, dus moet Word eerst geactiveerd worden.
  22.             Set WordObj = CreateObject("Word.Application")
  23.         End If
  24.  
  25. 'Waarschuwing: geef het correcte pad op naar de gebruikte sjabloon.
  26.         WordObj.Documents.Add Template:="C:\Documents and Settings\Ida\Application Data\Microsoft\Sjablonen\NLD-LV1.dotx", NewTemplate:=False
  27.  
  28.         With WordObj.Selection
  29.  
  30.             .GoTo what:=wdGoToBookmark, Name:="Klas"
  31.             .TypeText rstLesvoorbereiding![Klas]
  32.  
  33.             .GoTo what:=wdGoToBookmark, Name:="Datum"
  34.             .TypeText rstLesvoorbereiding![Datum]
  35.  
  36. 'Een recordset openen voor het multivalued veld / Open a recordset for the multivalued field
  37.         Set childrst = rstLesvoorbereiding!Lesuur.Value
  38.             childrst.MoveFirst
  39.                 Do Until childrst.EOF
  40.                     If IsNull(childrst!Value.Value) Then
  41.                         .TypeText " "
  42.                     Else
  43.                         .GoTo what:=wdGoToBookmark, Name:="Lesuur"
  44.                         .TypeText childrst!Value.Value & ", "
  45.                     End If
  46.                     childrst.MoveNext
  47.                 Loop
  48.  
  49.             .GoTo what:=wdGoToBookmark, Name:="LesId"
  50.             .TypeText rstLesvoorbereiding![LesId]
  51.  
  52.             .GoTo what:=wdGoToBookmark, Name:="Onderwerp"
  53.             .TypeText rstLesvoorbereiding![Onderwerp]
  54.  
  55.         Set childrst = rstLesvoorbereiding!Lesverloop.Value
  56.             childrst.MoveFirst
  57.                 Do Until childrst.EOF
  58.                     .GoTo what:=wdGoToBookmark, Name:="Lesverloop"
  59.                     If IsNull(childrst!FileName.Value) Then
  60.                         .TypeText " "
  61.                     Else
  62.                         .TypeText childrst!FileName.Value & ", "
  63.                     End If
  64.                     childrst.MoveNext
  65.                 Loop
  66.  
  67.         Set childrst = rstLesvoorbereiding!Werkvormen.Value
  68.             childrst.MoveFirst
  69.                 Do Until childrst.EOF
  70.                     .GoTo what:=wdGoToBookmark, Name:="Werkvormen"
  71.                     .TypeText childrst!Value.Value & ", "
  72.                     childrst.MoveNext
  73.                 Loop
  74.  
  75.         Set childrst = rstLesvoorbereiding!Opmerkingen.Value
  76.             childrst.MoveFirst
  77.                 Do Until childrst.EOF
  78.                 .GoTo what:=wdGoToBookmark, Name:="Opmerkingen"
  79.                     If IsNull(rstLesvoorbereiding![Opmerkingen]) Then
  80.                         .TypeText "Er zijn geen opmerkingen."
  81.                     Else
  82.                         .TypeText rstLesvoorbereiding![Opmerkingen]
  83.                     End If
  84.                     childrst.MoveNext
  85.                 Loop
  86.  
  87.         DoEvents
  88.         WordObj.Activate
  89.         WordObj.Visible = True
  90.     End With
  91.  
  92. 'Het geheugen leeg maken.
  93.     Set WordObj = Nothing
  94.     DoCmd.Hourglass False
  95.     DoCmd.Close acQuery, LesgegevensQuery, acSaveNo
  96.  
  97. End Sub
Hope this may help others.
Ida
Nov 6 '08 #2

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

Similar topics

5
by: Jayjay | last post by:
Is it possible to export a report to word and have it include the graphics that are in the report? I'd like to take some of our reports and export them to word so we can email those to the...
4
by: Lisa | last post by:
Hello, I have an ole object in my database that contains mostly pictures. I have this field in a report that I need to export to MS Word. When I export the report, I get everything accept the...
0
by: Otis Hunter | last post by:
I have been fighting with this for days and your expert help is needed! Below is the code I am executing which results with "Object doesn't support this property or method". The error is occuring...
8
by: Squirrel | last post by:
Hi everyone, I've created a mail merge Word doc. (using Office XP) , the data source is an Access query. Functionality I'm attempting to set up is: User sets a boolean field to true for...
5
by: mik18 | last post by:
I'm having trouble with exporting reports to Word in the rtf format and I'm hoping someone has a solution. The reports are losing their formats. Not all the formating is lost but some is and of...
7
by: Andy Davis | last post by:
I have a table of data in Access 2002 which is used as the source table for a mail merge document using Word 2002 on my clients PC. The data is transferred OK but I've noticed that any dates which...
2
by: Access Newbie with a headache via AccessMonster.co | last post by:
I am looking to export certain variables from ms access to ms word, Does anyone have any idea how this is done. So far my function collects data from two separate tables, and can open the word...
1
by: Paul | last post by:
Hi, I have an entry, called UserIDs. It is a multivalue entry (the new type from Access 2007). The entries look like 1; 5; 332; 28 3; 1; 557; 2; 47 However when I try to retrieve it using...
5
by: Dave | last post by:
Hello, I have a possible problem exporting a text field that happens to contain dates (but is not a date field) when using TransferText in MS Access 2000. I am exporting a query to a text...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.