Connecting Tech Pros Worldwide Help | Site Map
bytes > topic > access > answers

Problem exporting multivalued field to Word 2007 template using Access 2007

Newbie
  
Join Date: Nov 2008
Location: Belgium
Posts: 2
#1: Nov 2 '08
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?
Newbie
  
Join Date: Nov 2008
Location: Belgium
Posts: 2
#2: Nov 6 '08

re: Problem exporting multivalued field to Word 2007 template using Access 2007

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
Reply

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,223 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved