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): - Private Sub KnopLesAfdrukken_Click() 'ButtonPrintLesson
-
'Export a chosen lessonplan to Word
-
On Error Resume Next
-
Dim rstLesvoorbereiding As New ADODB.Recordset
-
Dim sSQL As String
-
Dim WordObj As New Word.Application
-
sSQL = "SELECT * FROM Lesvoorbereiding " & "WHERE LesId = " & Forms!FormLesvoorbereiding![LesId]
-
rstLesvoorbereiding.Open sSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
-
If rstLesvoorbereiding.EOF Then
-
MsgBox "Kan de les niet afdrukken.", vbOKOnly 'Can't print the lesson.
-
Exit Sub
-
End If
-
-
DoCmd.Hourglass True
-
-
Set WordObj = GetObject(, "Word.Application")
-
If Err.Number <> 0 Then
-
Set WordObj = CreateObject("Word.Application")
-
End If
-
-
WordObj.Documents.Add Template:="C:\Documents and Settings\Ida\Application Data\Microsoft\Sjablonen\NLD-LV1.dotx", NewTemplate:=False
-
-
With WordObj.Selection
-
.GoTo what:=wdGoToBookmark, Name:="Klas" 'Class
-
.TypeText rstLesvoorbereiding![Klas]
-
-
.GoTo what:=wdGoToBookmark, Name:="Datum" 'Date
-
.TypeText rstLesvoorbereiding![Datum]
-
-
Do Until rstLesvoorbereiding.EOF
-
.GoTo what:=wdGoToBookmark, Name:="Lesuur" 'Lessonhour
-
.TypeText rstLesvoorbereiding![Lesuur_Value] & ", "
-
rstLesvoorbereiding.MoveNext
-
Loop
-
-
.GoTo what:=wdGoToBookmark, Name:="LesId" 'LessonId
-
.TypeText rstLesvoorbereiding![LesId]
-
-
.GoTo what:=wdGoToBookmark, Name:="Onderwerp" 'Subject
-
.TypeText rstLesvoorbereiding![Onderwerp]
-
-
.GoTo what:=wdGoToBookmark, Name:="Materiaal" 'Used materials
-
.TypeText rstLesvoorbereiding![Materiaal]
-
-
Do Until rstLesvoorbereiding.EOF
.GoTo what:=wdGoToBookmark, Name:="Lesverloop" 'Lessonflow, pupils worksheets, etc. If IsNull(rstLesvoorbereiding![FileName]) Then -
.TypeText " "
-
Else
-
.TypeText rstLesvoorbereiding![FileName]
-
End If
Loop Do Until rstLesvoorbereiding.EOF -
.GoTo what:=wdGoToBookmark, Name:="Werkvormen" 'Activities
-
.TypeText rstLesvoorbereiding![Werkvormen_Value]
Loop .GoTo what:=wdGoToBookmark, Name:="Opmerkingen" 'Remarks -
If IsNull(rstLesvoorbereiding![Opmerkingen]) Then
-
.TypeText " "
-
Else
-
.TypeText rstLesvoorbereiding![Opmerkingen]
-
End If
-
-
DoEvents
-
WordObj.Activate
-
WordObj.Visible = True
-
End With
-
-
Set WordObj = Nothing
-
DoCmd.Hourglass False
-
-
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?
1 3484
Found it myself, eventually, after lots of experimenting: - Private Sub KnopLesAfdrukken_Click()
-
'Een lesvoorbereiding automatisch laten afdrukken in Word
-
On Error Resume Next
-
Dim rstLesvoorbereiding As New ADODB.Recordset
-
Dim childrst As New ADODB.Recordset
-
Dim sSQL As String, sSQL1 As String
-
Dim WordObj As New Word.Application
-
sSQL = "SELECT * FROM Lesvoorbereiding " & "WHERE LesId = " & Forms!FormLesvoorbereiding![LesId]
-
rstLesvoorbereiding.Open sSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
-
-
If rstLesvoorbereiding.EOF Then
-
MsgBox "Kan de les niet afdrukken, want er zijn nog geen lesgegevens voor deze les.", vbOKOnly
-
Exit Sub
-
End If
-
-
DoCmd.Hourglass True
-
-
'Kijken of Word actief is
-
Set WordObj = GetObject(, "Word.Application")
-
If Err.Number <> 0 Then
-
'Een Error wordt opgeroepen als Word niet actief is, dus moet Word eerst geactiveerd worden.
-
Set WordObj = CreateObject("Word.Application")
-
End If
-
-
'Waarschuwing: geef het correcte pad op naar de gebruikte sjabloon.
-
WordObj.Documents.Add Template:="C:\Documents and Settings\Ida\Application Data\Microsoft\Sjablonen\NLD-LV1.dotx", NewTemplate:=False
-
-
With WordObj.Selection
-
-
.GoTo what:=wdGoToBookmark, Name:="Klas"
-
.TypeText rstLesvoorbereiding![Klas]
-
-
.GoTo what:=wdGoToBookmark, Name:="Datum"
-
.TypeText rstLesvoorbereiding![Datum]
-
-
'Een recordset openen voor het multivalued veld / Open a recordset for the multivalued field
-
Set childrst = rstLesvoorbereiding!Lesuur.Value
-
childrst.MoveFirst
-
Do Until childrst.EOF
-
If IsNull(childrst!Value.Value) Then
-
.TypeText " "
-
Else
-
.GoTo what:=wdGoToBookmark, Name:="Lesuur"
-
.TypeText childrst!Value.Value & ", "
-
End If
-
childrst.MoveNext
-
Loop
-
-
.GoTo what:=wdGoToBookmark, Name:="LesId"
-
.TypeText rstLesvoorbereiding![LesId]
-
-
.GoTo what:=wdGoToBookmark, Name:="Onderwerp"
-
.TypeText rstLesvoorbereiding![Onderwerp]
-
-
Set childrst = rstLesvoorbereiding!Lesverloop.Value
-
childrst.MoveFirst
-
Do Until childrst.EOF
-
.GoTo what:=wdGoToBookmark, Name:="Lesverloop"
-
If IsNull(childrst!FileName.Value) Then
-
.TypeText " "
-
Else
-
.TypeText childrst!FileName.Value & ", "
-
End If
-
childrst.MoveNext
-
Loop
-
-
Set childrst = rstLesvoorbereiding!Werkvormen.Value
-
childrst.MoveFirst
-
Do Until childrst.EOF
-
.GoTo what:=wdGoToBookmark, Name:="Werkvormen"
-
.TypeText childrst!Value.Value & ", "
-
childrst.MoveNext
-
Loop
-
-
Set childrst = rstLesvoorbereiding!Opmerkingen.Value
-
childrst.MoveFirst
-
Do Until childrst.EOF
-
.GoTo what:=wdGoToBookmark, Name:="Opmerkingen"
-
If IsNull(rstLesvoorbereiding![Opmerkingen]) Then
-
.TypeText "Er zijn geen opmerkingen."
-
Else
-
.TypeText rstLesvoorbereiding![Opmerkingen]
-
End If
-
childrst.MoveNext
-
Loop
-
-
DoEvents
-
WordObj.Activate
-
WordObj.Visible = True
-
End With
-
-
'Het geheugen leeg maken.
-
Set WordObj = Nothing
-
DoCmd.Hourglass False
-
DoCmd.Close acQuery, LesgegevensQuery, acSaveNo
-
-
End Sub
Hope this may help others.
Ida
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
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
|
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...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
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...
|
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,...
|
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,...
|
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,...
|
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...
| |