Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2002
    Posts
    7

    Arrow Unanswered: automate reports from a parameter query

    I have more than 100 customer statements I need to print out every few days. These reports are based on a query which need the customer number input (Parameter). I have tried to extract this list of customers into a table and use the recordset loop example I found in this forum but haven't been able to make it work.

    I am using Access XP and am printing the reports to Acrobat Distiller. Can one of you please guide me to getting this automated and attaching the customer number to the acrobat file saved? I am a newbie when it comes to Access VB and recordsets. Thanks in advance for your help.

  2. #2
    Join Date
    Nov 2002
    Posts
    7
    I somehow got to this stage and have most of it working. I hope you cen see what I am trying to do here in the code.

    Code:
    Sub Report1()
    
    
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter
    
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("tbl_cust_bal_for_stmts")
    Set qdf = db.QueryDefs("qry_cust_parameter")
    Set prm = qdf.Parameters("Enter Company ID")
    
    rst.MoveFirst
    
    Do While Not rst.EOF
    
    prm = rst.Fields("cust_id")
    
    qdf.Execute dbFailOnError
    
    DoCmd.RunMacro "Statement1"
    
    Set prm = Nothing
    
    rst.MoveNext
    
    Loop
    
    End Sub
    I am getting a dbFailOnError = 128 when executing this. Can someone help me resolve this?

  3. #3
    Join Date
    Nov 2002
    Posts
    7
    Can someone please help me? IT'S VERY URGENT.

  4. #4
    Join Date
    Nov 2002
    Posts
    7
    Come on guys I desperately need this to work. PLEASE HELP!

  5. #5
    Join Date
    Aug 2002
    Posts
    29
    What line does it error on?... I'm assuming the Execute method.

    What is the exact error message?

    What is the query that is run?
    Last edited by GodMadeIncubus; 11-18-02 at 12:32.

  6. #6
    Join Date
    Nov 2002
    Posts
    7
    Originally posted by GodMadeIncubus
    What line does it error on?... I'm assuming the Execute method.

    What is the exact error message?

    What is the query that is run?
    You are right, its the Execute method. I get a dbfailerror=128.

    I am running 'qry_cust_parameter' which needs the customer number input through the execute method.

  7. #7
    Join Date
    Aug 2002
    Posts
    29

    Cool

    Originally posted by ashwen
    You are right, its the Execute method. I get a dbfailerror=128.

    I am running 'qry_cust_parameter' which needs the customer number input through the execute method.
    "dbfailerror=128" is the exact error message? (send a screenshot)
    What is the SQL behind the 'qry_cust_parameter' query?

  8. #8
    Join Date
    Nov 2002
    Posts
    7
    I am such a dumbo. I was looking in the wrong place for the error message. My parameter qry is a make-table query and hence the execute method failed as the table existed already. I just had to delete the table for every repitition. Thanks so much GodMadeIncubus for asking the right questions. You've been a great help.

Posting Permissions

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