Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2006

    Unanswered: Problem with module to export several files to .rtf documents

    Here is my function :

    Private Sub Commande4_Click()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim BaseSQL As String
    Dim strSQL As String
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT DISTINCT [Dealer/Distributor Number] FROM Query_Active_Dealer_List_Update")
    Set qdf = dbs.QueryDefs("Query_Active_Dealer_List_Update")
    BaseSQL = qdf.SQL
    With rst
    Do Until .EOF
    strSQL = Left(BaseSQL, Len(BaseSQL) - 3) & " WHERE [Dealer/Distributor Number] =" & ![Dealer/Distributor Number]
    qdf.SQL = strSQL
    DoCmd.OutputTo acOutputReport, "Main_Report", "RichTextFormat", "C:\Documents and Settings\larocma\Dealer_Scorecards\" & ![Dealer/Distributor Number] & ".doc"

    End With
    qdf.SQL = BaseSQL
    Set qdf = Nothing
    Set rst = Nothing
    Set dbs = Nothing

    End Sub

    The problem is in the line containing : strSQL = Left(BaseSQL, Len(BaseSQL) - 3) & " WHERE [Dealer/Distributor Number] =" & ![Dealer/Distributor Number]

    If I let ![Dealer/Distributor Number] this way I get an error and no report generated. But if I put "[Dealer/Distributor Number]" in a parameter, and I enter the dealer numbers by hand, I get good working reports. Dealer/Distributor Number are not nul. Main_Report is a report containing many sub_reports grouped by Dealer/Distributor Number. The query I linked to the function is the query I use on the main report to get the information about each dealership.

  2. #2
    Join Date
    Sep 2006
    If you want to have a look, I uploaded my work to

    The button is located on Formulaire1


  3. #3
    Join Date
    Feb 2004
    New Zealand
    Provided Answers: 10
    Well First question Why all the Execl files That will slow down MSACCESS HEAPS

    but have a @ the "Query_Active_Dealer_List_Update"

    it say
    WHERE [Dealer/Distributor Number] =[Dealer/Distributor Numb WHERE [Dealer/Distributor Number] = '690002';

    And if I'm reading you code right

    strSQL = Left(BaseSQL, Len(BaseSQL) - 3) & " WHERE [Dealer/Distributor Number] =" & ![Dealer/Distributor Number]

    is trying to add and other where to the End of the "Query_Active_Dealer_List_Update" query

    qdf.SQL = strSQL is just addin the where ... to the End each time it is run

    I would Hard code the BaseSQL with Everything Before the Where

    so that Each time It run run you are only add the BaseSQL and the Where

    I do like your idea
    change the Query

    I would of
    change the report recordsource on the fly

    it look like the
    Last edited by myle; 09-21-06 at 18:30.
    hope this help

    See clear as mud

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts