door de gebruiker gedefinieerd gegevenstype is niet gedefinieerd

[remcop1989]

Voor een mailmerge met Word (2003) heb ik de volgende code:

Private Sub SetQuery(strQueryName As String, strSQL As String)
 On Error GoTo ErrorHandler
        'set the query from which the merge document will pull its info
        Dim qdfNewQueryDef As QueryDef
        Set qdfNewQueryDef = CurrentDb.QueryDefs(strQueryName)
        qdfNewQueryDef.SQL = strSQL
        qdfNewQueryDef.Close
        RefreshDatabaseWindow
Exit Sub
ErrorHandler:
    MsgBox "Error #" & Err.Number & " occurred. " & Err.Description, vbOKOnly, "Error"
    Exit Sub
End Sub

Private Sub offertemaken_Click()
'creates an SQL statement to be used in the query def
On Error GoTo ErrorHandler
' user enters a zip code in a text box on the form;
' the query's criteria is set to pull records for
'that zip code

Dim strOffertenummer As String
strOffertenummer = Offertenummer.Value
Dim strSQL As String
'replace the SQL statement below with the SQL statement
'from your query. This sample shows how to use single quotes
'to incorporate string values from the form's fields
'into the SQL statement. For dates, use # instead of the
'single quotes
strSQL = "SELECT Klanten.Bedrijfsnaam, Offertes.[Voornaam klant], Offertes.[Achternaam klant], Klanten.Postadres, Klanten.Postcode, Klanten.PostPlaats, Klanten.Land, Offertes.[Telefoonnummer klant], Offertes.[E-mailadres klant], Offertes.Betreft, Offertes.[Datum van opstellen], Offertes.[Plaats van opstellen], Offertes.Offertenummer, Offertes.[Datum transport bezorgen], Offertes.[Datum transport ophalen], Offertes.Huurperiode, Offertes.Afleveradres
FROM Klanten INNER JOIN Offertes ON Klanten.KlantID=Offertes.KlantID;"

Dim strDocumentName As String  'name of the Word template document
strDocumentName = "\offerte sjabloon huur nederlands.doc"
'use your template document name above

Call SetQuery("Query1", strSQL)
'use your query name above
Dim strNewName As String  'name to use when saving
'the merged document
'this next line of code makes the document name pattern
'like this: Custom Labels January 11, 2005.doc
strNewName = "Off- " & Offertenummer)
'use your file name pattern above
Call OpenMergedDoc(strDocumentName, strSQL, strNewName)
Exit Sub
ErrorHandler:
    MsgBox "Error #" & Err.Number & " occurred. " & Err.Description, vbOKOnly, "Error"
    Exit Sub
End Sub


Private Sub OpenMergedDoc(strDocName As String, strSQL As String, strMergedDocName As String)
On Error GoTo WordError
    'opens an instance of Word, opens a merge template which has its data source
    'already linked to a query in the database,
    'optional code merges the template to a new document,
    'saves the merged file with a descriptive name, 
    'then closes the merge template
    
    'Set the directory for any labels generated 
    Const strDir As String = "S: \Contacts" 
'use your directory and folder name above
   Dim objWord As New Word.Application
   Dim objDoc As Word.Document
   objWord.Application.Visible = True
   Set objDoc = objWord.Documents.Open(strDir & strDocName)
   ' Make Word visible so that if any errors occur,
' you can close the instance of Word manually 
   objWord.Application.Visible = True
  
'*optional code to merge to a new document, save the merged document, and close the template goes here*

    'release the variables 
    Set objWord = Nothing
    Set objDoc = Nothing
       
Exit Sub
WordError:
        MsgBox "Err #" & Err.Number & "  occurred." & Err.Description, vbOKOnly, "Word Error"
        objWord.Quit
End Sub

Deze code heb ik van deze website: http://www.tek-tips.com/faqs.cfm?fid=3237 en uiteraard zelf aangepast naar mijn database.

Ik krijg nu de volgende foutmelding: "Een door de gebruiker gedefinieerd gegevenstype is niet gedefinieerd"
Deze krijg ik op de volgende regels:

   Dim objWord As New Word.Application
   Dim objDoc As Word.Document

Wat is er mis? Ik kom er niet achter.

 

[OctaFish]

Heb je de Word bibliotheek wel geactiveerd? Je werkt nu met Early binding, en dan moet de bibliotheek geladen zijn.

 

[remcop1989]

Door die Word bibliotheek aan te vinken voorkom ik de foutmelding, maar krijg nu een nieuwe foutmelding (zie screenshot in bijlagen).

Hoe kan dit?

Ik heb nu deze code:

Private Sub SetQuery(strQueryName As String, strSQL As String)
 On Error GoTo ErrorHandler
        'set the query from which the merge document will pull its info
        Dim qdfNewQueryDef As QueryDef
        Set qdfNewQueryDef = CurrentDb.QueryDefs(strQueryName)
        qdfNewQueryDef.SQL = strSQL
        qdfNewQueryDef.Close
        RefreshDatabaseWindow
Exit Sub
ErrorHandler:
    MsgBox "Error #" & Err.Number & " occurred. " & Err.Description, vbOKOnly, "Error"
    Exit Sub
End Sub

Private Sub offertemaken_Click()
'creates an SQL statement to be used in the query def
On Error GoTo ErrorHandler
' user enters a zip code in a text box on the form;
' the query's criteria is set to pull records for
'that zip code

Dim strOffertenummer As String
strOffertenummer = Offertenummer.Value
Dim strSQL As String
'replace the SQL statement below with the SQL statement
'from your query. This sample shows how to use single quotes
'to incorporate string values from the form's fields
'into the SQL statement. For dates, use # instead of the
'single quotes
strSQL = "SELECT Klanten.Bedrijfsnaam, Offertes.[Voornaam klant], Offertes.[Achternaam klant], Klanten.Postadres, Klanten.Postcode, Klanten.PostPlaats, Klanten.Land, Offertes.[Telefoonnummer klant], Offertes.[E-mailadres klant], Offertes.Betreft, Offertes.[Datum van opstellen], Offertes.[Plaats van opstellen], Offertes.Offertenummer, Offertes.[Datum transport bezorgen], Offertes.[Datum transport ophalen], Offertes.Huurperiode, Offertes.Afleveradres FROM Klanten INNER JOIN Offertes ON Klanten.KlantID=Offertes.KlantID;"

Dim strDocumentName As String  'name of the Word template document
strDocumentName = "R:\offerte sjabloon huur nederlands.doc"
'use your template document name above

Call SetQuery("Query1", strSQL)
'use your query name above
Dim strNewName As String  'name to use when saving
'the merged document
'this next line of code makes the document name pattern
'like this: Custom Labels January 11, 2005.doc
strNewName = ("Off- " & [Offertenummer])
'use your file name pattern above
Call OpenMergedDoc(strDocumentName, strSQL, strNewName)
Exit Sub
ErrorHandler:
    MsgBox "Error #" & Err.Number & " occurred. " & Err.Description, vbOKOnly, "Error"
    Exit Sub
End Sub

Private Sub OpenMergedDoc(strDocName As String, strSQL As String, strMergedDocName As String)
On Error GoTo WordError
    'opens an instance of Word, opens a merge template which has its data source
    'already linked to a query in the database,
    'optional code merges the template to a new document,
    'saves the merged file with a descriptive name,
    'then closes the merge template
    
    'Set the directory for any labels generated
    Const strDir As String = "C:\Documents and Settings\rpeters\Bureaublad\access programma\offertes"
'use your directory and folder name above
   Dim objWord As New Word.Application
   Dim objDoc As Word.Document
   objWord.Application.Visible = True
   Set objDoc = objWord.Documents.Open(strDir & strDocName)
   ' Make Word visible so that if any errors occur,
' you can close the instance of Word manually
   objWord.Application.Visible = True
  
'*optional code to merge to a new document, save the merged document, and close the template goes here*

    'release the variables
    Set objWord = Nothing
    Set objDoc = Nothing
       
Exit Sub
WordError:
        MsgBox "Err #" & Err.Number & "  occurred." & Err.Description, vbOKOnly, "Word Error"
        objWord.Quit
End Sub

Het mailmergedocument heet: offerte sjabloon huur nederlands.doc en het staat op de R: schijf.

 

[OctaFish]

D'r zit in ieder geval een foutje in je constante strDir; daar ontbreekt de laatste backslash.

Const strDir As String = "C:\Documents and Settings\rpeters\Bureaublad\access programma\offertes[B][COLOR="#FF0000"]\[/COLOR][/B]"

En die gaf dus hier een probleem:

    Set objDoc = objWord.Documents.Open(strDir & strDocName)

Andere oplossing, waarbij je de constante kunt laten voor wat-ie is:

    Set objDoc = objWord.Documents.Open(strDir & [B][COLOR="#0000CD"]"\" & [/COLOR][/B]strDocName)

 

[remcop1989]

Oké, duidelijk. Ik heb nu de volgende code:

Private Sub SetQuery(strQueryName As String, strSQL As String)
 On Error GoTo ErrorHandler
        'set the query from which the merge document will pull its info
        Dim qdfNewQueryDef As QueryDef
        Set qdfNewQueryDef = CurrentDb.QueryDefs(strQueryName)
        qdfNewQueryDef.SQL = strSQL
        qdfNewQueryDef.Close
        RefreshDatabaseWindow
Exit Sub
ErrorHandler:
    MsgBox "Error #" & Err.Number & " occurred. " & Err.Description, vbOKOnly, "Error"
    Exit Sub
End Sub


Private Sub Knop61_Click()
'creates an SQL statement to be used in the query def
On Error GoTo ErrorHandler
' user enters a zip code in a text box on the form;
' the query's criteria is set to pull records for
'that zip code

Dim strOffertenummer As String
strOffertenummer = Offertenummer.Value
Dim strSQL As String
'replace the SQL statement below with the SQL statement
'from your query. This sample shows how to use single quotes
'to incorporate string values from the form's fields
'into the SQL statement. For dates, use # instead of the
'single quotes
strSQL = "SELECT Klanten.Bedrijfsnaam, Offertes.[Voornaam klant], Offertes.[Achternaam klant], Klanten.Postadres, Klanten.Postcode, Klanten.PostPlaats, Klanten.Land, Offertes.[Telefoonnummer klant], Offertes.[E-mailadres klant], Offertes.Betreft, Offertes.[Datum van opstellen], Offertes.[Plaats van opstellen], Offertes.Offertenummer, Offertes.[Datum transport bezorgen], Offertes.[Datum transport ophalen], Offertes.Huurperiode, Offertes.Afleveradres FROM Klanten INNER JOIN Offertes ON Klanten.KlantID=Offertes.KlantID;"

Dim strDocumentName As String  'name of the Word template document
strDocumentName = "R:\2007-2008-2009-2010\Remco Peters\access programma\offerte sjabloon huur nederlands.doc"
'use your template document name above

Call SetQuery("Query1", strSQL)
'use your query name above
Dim strNewName As String  'name to use when saving
'the merged document
'this next line of code makes the document name pattern
'like this: Custom Labels January 11, 2005.doc
strNewName = ("Off- " & [Offertenummer])
'use your file name pattern above
Call OpenMergedDoc(strDocumentName, strSQL, strNewName)
Exit Sub
ErrorHandler:
    MsgBox "Error #" & Err.Number & " occurred. " & Err.Description, vbOKOnly, "Error"
    Exit Sub
End Sub

Private Sub OpenMergedDoc(strDocumentName As String, strSQL As String, strMergedDocName As String)
On Error GoTo WordError
    'opens an instance of Word, opens a merge template which has its data source
    'already linked to a query in the database,
    'optional code merges the template to a new document,
    'saves the merged file with a descriptive name,
    'then closes the merge template
    
    'Set the directory for any labels generated
    Const strDir As String = "R:\2007-2008-2009-2010\Remco Peters\access programma\offertes"
'use your directory and folder name above
   Dim objWord As New Word.Application
   Dim objDoc As Word.Document
   objWord.Application.Visible = True
   Set objDoc = objWord.Documents.Open(strDir & "\" & strDocName)
   ' Make Word visible so that if any errors occur,
' you can close the instance of Word manually
   objWord.Application.Visible = True
  
'*optional code to merge to a new document, save the merged document, and close the template goes here*
'*paste this optional code in the subroutine to
'merge the data with the template to a new document,
'save the merged document, and close the template.
'Otherwise, the above code will open the template (which
'already has the query set
'as its recordsource) and the user will need to merge the
'template to a new document using the toolbar in Word
 
 'Merge to a new document
 'if you are not sure of the SQLStatement to use in your
'OpenDataSource string, uncomment the following four
'lines to have the current SQLstatement print in the
'immediate window. You can then copy the returned string 'into your code
  'Debug.Print objWord.Application.ActiveDocument.MailMerge.DataSource.QueryString
'objWord.quit
'set objWord = nothing
'exit sub

'replace the file path  and query name below with the path
'to your database and your query name
  objDoc.MailMerge.OpenDataSource _
        Name:="R:\2007-2008-2009-2010\Remco Peters\access programma\Kopie van offerteprogramma DEZE GEBRUIKEN.mdb", _
        LinkToSource:=True, AddToRecentFiles:=False, _
        Connection:="QUERY Query1", _
        SQLStatement:="SELECT * FROM [Query1]"
'notice that the SQLStatement above is not the SQL
'statement that makes up the QueryDef of the query.  It is
'the SQL statement that tells Word whether or not to use all the records returned by the Query

   objDoc.MailMerge.Destination = wdSendToNewDocument
   objDoc.MailMerge.Execute
    'save the merged document with a descriptive name
' you can delete this next line if you don't want to save
' the merged document
' it will leave the document with the default name
'"Labels 1"  or "Letters 1"
    objWord.Application.Documents(1).SaveAs (strDir & "\" & strMergedDocName & ".doc")
'close the merge template without saving
    objWord.Application.Documents(2).Close wdDoNotSaveChanges

    'release the variables
    Set objWord = Nothing
    Set objDoc = Nothing
       
Exit Sub
WordError:
        MsgBox "Err #" & Err.Number & "  occurred." & Err.Description, vbOKOnly, "Word Error"
        objWord.Quit

End Sub

Ik krijg nu de melding in de screenshot in de bijlagen. daarbij opent Word zich wel met de meldingen in de bijlagen.

Ik kan de fout niet ontdekken..........

Heeft iemand hier wellicht een andere methode om een mailmerge te maken naar Word 2003 vanuit Access? Ik heb namelijk een format dat met bepaalde informatie uit Access gevuld moet worden om te komen tot een offerte.

 

[OctaFish]

Vertel eerst eens waarom het niet vanuit Word kan.

 

[remcop1989]

Omdat dat veel te veel tijd kost en de men wil het geautomatiseerd en gekoppeld aan het huidige bestaande systeem zodat van een offerte meteen contracten etc. Gemaakt kunnen worden. Bovendien staan in het systeem klanten en producten al.