[AccessD] Setting Sub Report Recordsource via VBA

jeffrey.demulling at usbank.com jeffrey.demulling at usbank.com
Tue Jun 1 15:09:57 CDT 2004





Here is the code and it is in the on open event of the report:

Option Compare Database
Option Explicit

Private Sub Report_Close()
    Forms![frmReports-Standard].Visible = True
End Sub

Private Sub Report_Open(Cancel As Integer)
    Dim cmdtext As String
    Dim selecttext As String
    Dim fromtext As String
    Dim wheretext As String
    Dim orderbytext As String
    Dim finalsqltext As String

    'Link in tables for query
    Call linkdatatable("tblAccount")
    Call linkdatatable("tblEmployee")
    Call linkdatatable("tblTimeCardHour")
    Call linkdatatable("tblAdminTimeDescripion")

    selecttext = ""
    fromtext = ""
    wheretext = ""
    orderbytext = ""
    finalsqltext = ""

    'Now create the correct recordsource for the main report
    'Create the SELECT portion of the SQL Statement
    selecttext = "SELECT"
    selecttext = selecttext & " tblAccount.AccountNumberID,"
    selecttext = selecttext & " tblAccount.AccountNumber,"
    selecttext = selecttext & " tblAccount.AccountName,"
    selecttext = selecttext & " tblEmployee!LastName & ', ' &
tblEmployee!FirstName AS AccountAdmin"

    'Createt the FROM portion
    fromtext = fromtext & " FROM"

    If Forms![frmReports-OpenAccountItems].cmbFinalSort.ListCount = 0 Then
'No items in select box
        fromtext = fromtext & " tblAccount"
        fromtext = fromtext & " LEFT JOIN tblEmployee"
        fromtext = fromtext & " ON tblAccount.EmployeeID =
tblEmployee.EmployeeID"
    Else 'Items in select box so base linking on item type selected
        Select Case Forms![frmReports-OpenAccountItems].cmbSelectBy
            Case "Account Administrator"
                fromtext = fromtext & " TempSort1"
                fromtext = fromtext & " RIGHT JOIN (tblAccount"
                fromtext = fromtext & " LEFT JOIN tblEmployee"
                fromtext = fromtext & " ON tblAccount.EmployeeID =
tblEmployee.EmployeeID)"
                fromtext = fromtext & " ON TempSort1.ItemId =
tblAccount.EmployeeID"
            Case "Account Name"
                fromtext = fromtext & " (tblAccount"
                fromtext = fromtext & " LEFT JOIN tblEmployee"
                fromtext = fromtext & " ON tblAccount.EmployeeID =
tblEmployee.EmployeeID)"
                fromtext = fromtext & " LEFT JOIN TempSort1"
                fromtext = fromtext & " ON tblAccount.AccountName =
TempSort1.Item"
            Case "Account Number"
                fromtext = fromtext & " (tblAccount"
                fromtext = fromtext & " LEFT JOIN tblEmployee"
                fromtext = fromtext & " ON tblAccount.EmployeeID =
tblEmployee.EmployeeID)"
                fromtext = fromtext & " LEFT JOIN TempSort1"
                fromtext = fromtext & " ON tblAccount.AccountNumberID =
TempSort1.ItemId"
        End Select
    End If

    'Now work on creating the WHERE portion
    If Forms![frmReports-OpenAccountItems].cmbFinalSort.ListCount = 0 Then
'No items in select box
        'Do Nothing because do not need to limit the output based on
selected list type
    Else 'Items in select box so base linking on item type selected
        Select Case Forms![frmReports-OpenAccountItems].cmbSelectBy
            Case "Account Administrator"
                wheretext = wheretext & " AND (((TempSort1.Item1) Is Not
Null))"
            Case "Account Name"
                wheretext = wheretext & " AND (((TempSort1.Item) Is Not
Null))"
            Case "Account Number"
                wheretext = wheretext & " AND (((TempSort1.Item1) Is Not
Null))"
        End Select

        'Now that the where clause is almost complete strip out the first 5
characters " AND "
        'and then add " WHERE " to the beging of the claus
        wheretext = Right(wheretext, ((Len(wheretext)) - 5))
        wheretext = " WHERE " & wheretext
    End If

    'Combine the text strings to complete finalsql string
    finalsqltext = selecttext & fromtext & wheretext & orderbytext & ";"

    'Set the recordsource
    Me.RecordSource = finalsqltext

    'Now do the same steps above for the subreports
(rptOpenItems-SubReport-TimeDetail,
    '          rptOpenItems-SubReport-InvoiceDetail,
rptOpenItems-SubReport-OtherDetail)

    'rptOpenItems-SubReport-TimeDetail

    selecttext = ""
    fromtext = ""
    wheretext = ""
    orderbytext = ""
    finalsqltext = ""

    'SELECT
    selecttext = "SELECT"
    selecttext = selecttext & " tblTimeCardHour.AccountNumber,"
    selecttext = selecttext & " tblEmployee!LastName & ', ' &
tblEmployee!FirstName AS Employee,"
    selecttext = selecttext & " tblTimeCardHour.DateWorked,"
    selecttext = selecttext & " [TimeInMinutes]/60 AS HoursWorked,"
    selecttext = selecttext & "
tblAdminTimeDescripion.AdminTimeDescription,"
    selecttext = selecttext & " tblTimeCardHour.DateBilled,"
    selecttext = selecttext & " tblTimeCardHour.DatePaymentReceived,"
    selecttext = selecttext & " tblTimeCardHour.TimeDescription"

    'FROM
    fromtext = fromtext & " FROM"
    fromtext = fromtext & " (tblTimeCardHour"
    fromtext = fromtext & " LEFT JOIN tblEmployee"
    fromtext = fromtext & " ON tblTimeCardHour.EmployeeID =
tblEmployee.EmployeeID)"
    fromtext = fromtext & " LEFT JOIN tblAdminTimeDescripion"
    fromtext = fromtext & " ON tblTimeCardHour.AdminTimeID =
tblAdminTimeDescripion.AdminTimeID"

    'WHERE
    'This depends on the check box selected
    If Forms![frmReports-OpenAccountItems].chkBilled = 0 And
Forms![frmReports-OpenAccountItems].chkPaymentReceived = 0 Then
        'No items selected so do nothing
    ElseIf Forms![frmReports-OpenAccountItems].chkBilled <> 0 And
Forms![frmReports-OpenAccountItems].chkPaymentReceived <> 0 Then
        wheretext = wheretext & " AND tblTimeCardHour.Billed <> 0"
        wheretext = wheretext & " AND tblTimeCardHour.PaymentReceived <> 0"
    ElseIf Forms![frmReports-OpenAccountItems].chkBilled <> 0 Then
        wheretext = wheretext & " AND tblTimeCardHour.Billed <> 0"
    ElseIf Forms![frmReports-OpenAccountItems].chkPaymentReceived <> 0 Then
        wheretext = wheretext & " AND tblTimeCardHour.PaymentReceived <> 0"
    End If

    'Now that the where clause is almost complete strip out the first 5
characters " AND "
    'and then add " WHERE " to the beging of the claus
    If Len(wheretext) <> 0 Then
        wheretext = Right(wheretext, ((Len(wheretext)) - 5))
        wheretext = " WHERE " & wheretext
    End If

    'Combine the text strings to complete finalsql string
    finalsqltext = selecttext & fromtext & wheretext & orderbytext & ";"

    'Set the recordsource

'Reports![rptOpenItems-AccountManager]![rptOpenItems-SubReport-TimeDetail].RecordSource
 = finalsqltext
    Reports![rptOpenItems-SubReport-TimeDetail].RecordSource = finalsqltext

    'Maximize the Report
    DoCmd.Maximize
End Sub


                                                                                                                                               
                      "Charlotte Foust"                                                                                                        
                      <cfoust at infostatsystems.c        To:       "Access Developers discussion and problem solving"                            
                      om>                               <accessd at databaseadvisors.com>                                                         
                      Sent by:                         cc:                                                                                     
                      accessd-bounces at databasea        Subject:  RE: [AccessD] Setting Sub Report Recordsource via VBA                         
                      dvisors.com                                                                                                              
                                                                                                                                               
                                                                                                                                               
                      06/01/2004 02:55 PM                                                                                                      
                      Please respond to "Access                                                                                                
                      Developers discussion and                                                                                                
                      problem solving"                                                                                                         
                                                                                                                                               
                                                                                                                                               




What precisely are you trying to accomplish and *when* are you trying to
set the recordsource?

Charlotte Foust

-----Original Message-----
From: jeffrey.demulling at usbank.com [mailto:jeffrey.demulling at usbank.com]

Sent: Tuesday, June 01, 2004 11:51 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Setting Sub Report Recordsource via VBA






I am trying to set a subreport's recordsource via a VBA and cannot seem
to get it correct.

I have tried:

me.<<subreportname>>.Recordsource = finalsqltext

Reports!<<parentreportname>>!<<subreportname>>.Recordsource =
finalsqltext

and neither one seems to be working.

Can anyone help????


--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com








More information about the AccessD mailing list