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

    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"

    .MoveNext
    Loop
    .Close
    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
    Posts
    2
    If you want to have a look, I uploaded my work to www.mlaroche.com/scorecards/scorecards.zip

    The button is located on Formulaire1

    Thanks,
    Maxime

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,478
    Provided Answers: 11
    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 17:30.
    hope this help

    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-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

Posting Permissions

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