Charlotte Foust
cfoust at infostatsystems.com
Tue Jun 1 18:36:25 CDT 2004
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
"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
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com