Jürgen Welz
jwelz at hotmail.com
Tue Jun 1 20:21:13 CDT 2004
I like to set the subreport recordsource to a saved querydef and set the SQL
property of the querydef in the procedure that opens the report.
Ciao
Jürgen Welz
Edmonton, Alberta
jwelz at hotmail.com
>From: "Charlotte Foust" <cfoust at infostatsystems.com>
>The problem is that subreports, like subforms, open before their
>parents, which means it's too late to set their recordsource in the
>parent report's open event. You would be better advised to filter the
>parent report recordset to filter the subreport or else populate an
>unbound control on the parent and let the subreport do its own filtering
>on that. Still another method is to have multiple subreports and only
>show the one that is appropriate.
>
>Charlotte Foust
>
>-----Original Message-----
>From: jeffrey.demulling at usbank.com [mailto:jeffrey.demulling at usbank.com]
>
>Sent: Tuesday, June 01, 2004 12:10 PM
>To: Access Developers discussion and problem solving
>Subject: RE: [AccessD] Setting Sub Report Recordsource via VBA
>
>
>
>
>
>
>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-TimeDetai
>l].RecordSource
> = finalsqltext
> Reports![rptOpenItems-SubReport-TimeDetail].RecordSource =
>finalsqltext
>
> 'Maximize the Report
> DoCmd.Maximize
>End Sub
_________________________________________________________________
MSN Premium with Virus Guard and Firewall* from McAfee® Security : 2 months
FREE*
http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines