Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating a Report with VBA (Access 2003)

    I'm looking for the right piece of code that will create a report based on an already created query. The already created query is called "UtilProduction" (I've managed code that will create the query, but for the last section I'd like it to turn that query into a report. The code below is for the onClick that generates the query (and hopefully the report).

    Thanks!!!!

    Private Sub cmdRunQuery_Click()
    On Error GoTo Err_cmdRunQuery_Click

    Dim dbs As Database
    Dim qdf As QueryDef
    Dim qdf2 As QueryDef
    Dim strSQL As String
    Dim strSQL2 As String

    Set dbs = CurrentDb

    DoCmd.DeleteObject acQuery, "JobProduction"
    DoCmd.DeleteObject acQuery, "UtilProduction"

    strSQL = "TRANSFORM Sum(Query2.Qty) AS SumOfQty " & _
    "SELECT tblPhCodes" & Combo0.Value & ".PhaseCode, tblPhCodes" & Combo0.Value & ".PhaseCodeDescrip, tblPhCodes" & Combo0.Value & ".BidQty " & _
    "FROM Query2 INNER JOIN tblPhCodes" & Combo0.Value & " ON Query2.PhaseCode = tblPhCodes" & Combo0.Value & ".PhaseCode " & _
    "WHERE (((Query2.JobNum) = " & Combo0.Value & ")) " & _
    "GROUP BY tblPhCodes" & Combo0.Value & ".PhaseCode, tblPhCodes" & Combo0.Value & ".PhaseCodeDescrip, tblPhCodes" & Combo0.Value & ".BidQty " & _
    "PIVOT Format([Date],'Short Date');"

    Set qdf = dbs.CreateQueryDef("JobProduction", strSQL)

    strSQL2 = "SELECT tblPhCodes" & Combo0.Value & ".*, JobProduction.* " & _
    "FROM tblPhCodes" & Combo0.Value & " LEFT JOIN JobProduction ON tblPhCodes" & Combo0.Value & ".PhaseCode = JobProduction.PhaseCode;"

    Set qdf2 = dbs.CreateQueryDef("UtilProduction", strSQL2)

    DoCmd.OpenQuery "UtilProduction"

    Exit_cmdRunQuery_Click:
    Exit Sub

    Err_cmdRunQuery_Click:
    MsgBox Err.Description
    Resume Exit_cmdRunQuery_Click

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating a Report with VBA (Access 2003)

    Although it is possible to create a report from scratch in code, it is difficult. Take a look at the thread starting at <post#=365323>post 365323</post#>. My first reply in that thread contains links to examples, and I posted a demo in a later reply.

  3. #3
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating a Report with VBA (Access 2003)

    Much obliged sir.

    I think I read that by default if you just create a report without specifying any asthetic attributes, it creates a report based on a template that you can adjust somewhere. Is that true?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating a Report with VBA (Access 2003)

    Access remembers the latest AutoFormat you have applied. You can edit existing AutoFormats and create new ones: click Customize... in the Format | AutoFormat dialog.

Posting Permissions

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