Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    173

    Unanswered: Dynamic Report Recordsource Tactics?

    I'm using Access 2000 and I've run up against this problem before and I'm wondering if anyone else has a different tack. I've built a form that uses a mess of simple logic to build up a query from 4 or 5 tables. The query works great, but (there's always a but) now I need to display those results into a report. Typically I've done things like change the report's recordsource in design view and re-show the report, or change a saved query's SQL property to the new query. Is this the best way to do this? Can you build an entirely new report in code adding fields and formatting and such?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Quote Originally Posted by mmbosman
    Typically I've done things like change the report's recordsource in design view and re-show the report, or change a saved query's SQL property to the new query.
    You can do this programmatically of course.

    Quote Originally Posted by mmbosman
    Is this the best way to do this? Can you build an entirely new report in code adding fields and formatting and such?
    I would say yes. My answer to the latter question is "I think so" but frankly I gave up trying after a couple of days of coding. As I recall the real problems were setting the Page Setup (e.g. setting orientation) and there was something to do with the header sections too.

    If you like I can dig up the experimental code and pass you the baton if you promise to post what you build upon it
    Last edited by pootle flump; 07-21-06 at 19:13.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    People try to do things the hard way ... Here's how I did most of my reports (cause I'm lazxy): I build the report on the table layout that I want ... Doing the grouping, sorting, yada yada yada ... Blah blah blah ... OK. Nothing special ... Then in my various forms, I put together the various criteria needed for the report called from that form in a hidden textbox. Then I invoke the report. The report pulls the criteria from the form's hiddeen textbox and then goes out and pulls it's own data matching the criteria putting it into the table then displays it ... Simple.

    Evil. But simple ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Apr 2004
    Posts
    173
    I appreciate both replies, and though I did wish I had the time to expand on any experimental code you could offer Pootle, sadly I don't. Too many irons in the fire currently. At the risk of sounding dense to Owens reply; I'm envisioning a report with a recordsource property set to something like

    forms(myform).Hiddentextbox - is this correct?

    Or is this something along the lines parsing the contents of the textbox? If it is the former I have to say that is devilishly simple. One other question Owen your reply suggests pulling the results of the query to a table and then displaying the results; again at the risk of sounding dense, I don't do this currently. Typically I base my reports on direct queries should I be selecting results into temp tables? The application in question is a split mdb with about 6 users.

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by mmbosman
    I appreciate both replies, and though I did wish I had the time to expand on any experimental code you could offer Pootle, sadly I don't. Too many irons in the fire currently. At the risk of sounding dense to Owens reply; I'm envisioning a report with a recordsource property set to something like

    forms(myform).Hiddentextbox - is this correct?

    Or is this something along the lines parsing the contents of the textbox? If it is the former I have to say that is devilishly simple. One other question Owen your reply suggests pulling the results of the query to a table and then displaying the results; again at the risk of sounding dense, I don't do this currently. Typically I base my reports on direct queries should I be selecting results into temp tables? The application in question is a split mdb with about 6 users.
    Not quite ... The criteria used to BUILD your query resides there ... It'll take longer for me to try to explain what I did to you than for me to give you an example of what can be done WITHIN a report (VBA codewise that is) ...

    Now mind you ... This is ADO code also ...

    Code:
    Option Compare Database
    Option Explicit
    
    Dim SQLString As String
    
    Dim RptTbl As String
    Dim WONumber As String
    
    Dim DBConnectionActive As Boolean
    
    'Dim MyConnect As ADODB.Connection
    Dim MyRecSet As ADODB.Recordset
    
    '
    ' *** Beginning of Functions and Methods
    '
    
    Function RetrieveRequestSubject(ByVal SI As Integer, ByRef SubjectTxt As String) As Integer
        On Error GoTo Err_RRS
        
        Dim TrgRecSet As ADODB.Recordset
        
        Set TrgRecSet = New ADODB.Recordset
        
        TrgRecSet.CursorLocation = adUseClient
        TrgRecSet.CursorType = adOpenForwardOnly
        TrgRecSet.LockType = adLockOptimistic
    
        SQLString = "SELECT [Request Subject] FROM [Service Instructions]"
        SQLString = SQLString & " WHERE ((WorkOrderNumber='" & WONumber & "') AND ([SI #]=" & SI & "));"
        TrgRecSet.Open SQLString, MyConnect
        If TrgRecSet.BOF = False Then
            TrgRecSet.MoveFirst
            SubjectTxt = Format(SI, "000") & " " & TrgRecSet.Fields(0).Value
        End If
        TrgRecSet.Close
        
        Set TrgRecSet = Nothing
            
        RetrieveRequestSubject = 1
    Exit_RRS:
        Exit Function
        
    Err_RRS:
        
        MsgBox Err.Number & ": " & Err.Description
        RetrieveRequestSubject = 0
        Resume Exit_RRS
    End Function
    
    Function RetrieveChgDetail() As Integer
        On Error GoTo Err_RCD
        
        Dim TmpString As String
        Dim TrgRecSet As ADODB.Recordset
        
        Set TrgRecSet = New ADODB.Recordset
        
        TrgRecSet.CursorLocation = adUseClient
        TrgRecSet.CursorType = adOpenDynamic
        TrgRecSet.LockType = adLockOptimistic
        
        ' Delete any existing records ...
        SQLString = "DELETE FROM " & RptTbl & ";"
        CurrentProject.Connection.Execute SQLString, , adCmdText
        
        ' Set the W.O. # and Project Title
        WO_Lbl.Caption = WONumber
        SQLString = "SELECT [Project Title] FROM [Work Orders II] WHERE (WorkOrderNumber='" & WONumber & "');"
        MyRecSet.Open SQLString, MyConnect
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            ProjectLbl.Caption = MyRecSet.Fields(0).Value & ""
        End If
        MyRecSet.Close
        
        ' Retrieve timesheet detail for the selected W.O. #
        SQLString = "SELECT [Clock Card Archive].DepartmentID, [CS Departments].Name, [Clock Card Archive].[Reg Hours], [Clock Card Archive].[OT Hours], [Clock Card Archive].[DT Hours],  [Clock Card Archive].[Payroll Date], Left([CS Employees].[First Name],1) & '. ' & [CS Employees].[Last Name] AS Expr1, [Clock Card Archive].[SI #]"
        SQLString = SQLString & " FROM ([Clock Card Archive] INNER JOIN [CS Departments] ON [Clock Card Archive].DepartmentID = [CS Departments].DepartmentID) INNER JOIN [CS Employees] ON ([Clock Card Archive].[Clock Card #] = [CS Employees].[Clock Card #])"
        SQLString = SQLString & " WHERE ([Clock Card Archive].WorkOrderNumber='" & WONumber & "');"
        MyRecSet.Open SQLString, MyConnect
        If MyRecSet.BOF = False Then
            MyRecSet.MoveFirst
            SQLString = "SELECT * FROM " & RptTbl & " WHERE (1=0);"
            TrgRecSet.Open SQLString, CurrentProject.Connection
            While MyRecSet.EOF = False
                TrgRecSet.AddNew
                ' Department ID
                TrgRecSet.Fields(0).Value = MyRecSet.Fields(0).Value
                ' Department Name
                TrgRecSet.Fields(1).Value = MyRecSet.Fields(1).Value & ""
                ' SI #
                TrgRecSet.Fields(2).Value = MyRecSet.Fields(7).Value
                If MyRecSet.Fields(7).Value <> 0 Then
                    RetrieveRequestSubject MyRecSet.Fields(7).Value, TmpString
                Else
                    TmpString = "000 (Unallocated)"
                End If
                ' Request Subject
                TrgRecSet.Fields(3).Value = TmpString
                ' Regular Hours
                TrgRecSet.Fields(4).Value = MyRecSet.Fields(2).Value
                ' OT Hours
                TrgRecSet.Fields(5).Value = MyRecSet.Fields(3).Value
                ' DT Hours
                TrgRecSet.Fields(6).Value = MyRecSet.Fields(4).Value
                ' Payroll Date
                TrgRecSet.Fields(7).Value = MyRecSet.Fields(5).Value
                ' Employee Name
                TrgRecSet.Fields(8).Value = MyRecSet.Fields(6).Value & ""
                TrgRecSet.Update
                MyRecSet.MoveNext
            Wend
            TrgRecSet.Close
        Else
            SQLString = "SELECT * FROM " & RptTbl & " WHERE (1=0);"
            TrgRecSet.Open SQLString, CurrentProject.Connection
            TrgRecSet.AddNew
            ' Department ID
            TrgRecSet.Fields(0).Value = 0
            ' Department Name
            TrgRecSet.Fields(1).Value = "No Department"
            ' SI #
            TrgRecSet.Fields(2).Value = 0
            ' Request Subject
            TrgRecSet.Fields(3).Value = "No SI"
            ' Regular Hours
            TrgRecSet.Fields(4).Value = 0
            ' OT Hours
            TrgRecSet.Fields(5).Value = 0
            ' DT Hours
            TrgRecSet.Fields(6).Value = 0
            ' Payroll Date
            TrgRecSet.Fields(7).Value = Date
            ' Employee Name
            TrgRecSet.Fields(8).Value = "No Hourly Detail"
            TrgRecSet.Update
            TrgRecSet.Close
        End If
        MyRecSet.Close
        
        Set TrgRecSet = Nothing
        
        LoadingDelay
        
        RetrieveChgDetail = 1
    Exit_RCD:
        Exit Function
        
    Err_RCD:
        
        MsgBox Err.Number & ": " & Err.Description
        RetrieveChgDetail = 0
        Resume Exit_RCD
    End Function
    
    Function EstablishDBConnection() As Integer
        On Error GoTo Err_EDBC
        
    '    Set MyConnect = New ADODB.Connection
        Set MyRecSet = New ADODB.Recordset
        
    '    MyConnect.CursorLocation = adUseClient
    '    MyConnect.Open "DSN=Billing System;PWD=" & Mid(EE_TmpText, CleanUpPos, 10) & ";"
        
        MyRecSet.CursorLocation = adUseClient
        MyRecSet.CursorType = adOpenForwardOnly
        MyRecSet.LockType = adLockOptimistic
        
        EstablishDBConnection = 1
    Exit_EDBC:
        Exit Function
        
    Err_EDBC:
        
        MsgBox Err.Number & ": " & Err.Description
        EstablishDBConnection = 0
        Resume Exit_EDBC
    End Function
    
    Private Sub Report_Open(Cancel As Integer)
        Dim IsOK As Integer, ParmListString() As String
    
        IsOK = EstablishDBConnection
        If IsOK = 0 Then
            Cancel = 1
            Exit Sub
        End If
        DBConnectionActive = True
        
        ' Format:
        '
        ' Labor Charges Detail Report table, W.O. #
        '
        ParmListString = Split(Forms![GMCS Reports].ParameterTxt, ",", -1, vbTextCompare)
    '    ParmListString = Split(Me.OpenArgs, ",", -1, vbTextCompare)
        RptTbl = ParmListString(0) & ""
        WONumber = ParmListString(1) & ""
        
        RetrieveChgDetail
        
        Me.RecordSource = RptTbl
    End Sub
    
    Private Sub Report_Close()
        If DBConnectionActive Then
    '        MyConnect.Close
            Set MyRecSet = Nothing
    '        Set MyConnect = Nothing
        End If
    End Sub
    This is the actual code in the report itself ... The report is dumb in that it is based on a temp table and then resets to a table name provided in the parameters ... Also, evrything needed to retrieve the desired data is provided ... Enjoy! (although you'll probably get a headache or your eyes will roll up after looking at this code ...)
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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