Run-Time Error '2467'

[gavin31]

I have read loads of the threads on this subject but can find no solution to my particular problem.

I have a report rptJobHours that contains a subreport rptExpenseItems.

I wish to update, on the activate event of the main report, the recordsource of the subreport dependant on the values of 2 text boxes on the main report (FromDate and ToDate)

Here is the code of the function


Private Sub Report_Activate()

Dim varOpenArgs As Variant
If (Not IsNull(Me.OpenArgs)) Then

varOpenArgs = Split(Me.OpenArgs, ";;")
Me.FromDate = Format(varOpenArgs(0), "Short Date")
Me.ToDate = Format(varOpenArgs(1), "Short Date")

Dim frmSubReport As New Report
Set frmSubReport = Reports!rptJobHours!rptExpenseItems.Form
frmSubReport.RecordSource = "SELECT * FROM tblExpenseItems INNER JOIN tblExpenses " & _
"ON tblExpenseItems.claimNumber = tblExpenses.claimNumber " & _
"WHERE (((tblExpenseItems.jobNumber)=[Reports]![rptJobHours].[jobNumber]) AND " & _
"((tblExpenseItems.date>" & Me.FromDate & ") AND " & _
"(tblExpenseItems.date>" & Me.ToDate & "));"
frmSubReport.Requery


End If

End Sub

The line I get the Run-Time Error 2467 on is:

Set frmSubReport = Reports!rptJobHours!rptExpenseItems.Form

which I think is perculiar as the subreport is opened and does exist.

If anyone can shed any light it would be much appreciated.

Gavin

 

[Remou]

Perhaps:
Set frmSubReport = Reports!rptJobHours!rptExpenseItems.[blue]Report[/blue]

 

[PHV]

I guess you wanted this:
Set frmSubReport = Reports!rptJobHours!rptExpenseItems.[!]Report[/!]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gavin31]

Ooops...

Damn! Makes no difference, still getting same error...

 

[Remou]

I do not think this will work as you cannot change the record source of the subreport in the Activate event of the main report, as far as I know. I think you will need to set the subform recordsource in the subform On Open event, or else use a query for the subreport that picks up the dates from a form.

 

[gavin31]

Remou

Thanks for the advice.

I will try to use a query in the subreport, that picks up the dates from the parent report.

I don't think in my parent report setting the recordsource for the subreport will work in the OnOpen event as the FromDate and ToDate do not get loaded/set until the Activate event of the main report.

Will post again once I have tried your suggestions.

Many thanks

Gavin

 

[Remou]

No, you cannot set the subreport recordsource in the On Open event of the parent form, regardless of any data, but you can in the On Open event of the subreport.

 

[gavin31]

OK

Cheated slightly.....but got it working

In the RecordSource of the subReport I now have the SQL statement as follows:

SELECT *
FROM tblExpenseItems INNER JOIN tblExpenses ON tblExpenseItems.claimNumber = tblExpenses.claimNumber
WHERE (((tblExpenseItems.jobNumber)=[Reports]![rptJobHours].[jobNumber]) AND ((tblExpenseItems.date)>Format([Forms]![frmJobHours].[startTime],"Short Date") And (tblExpenseItems.date)<Format([Forms]![frmJobHours].[endTime],"Short Date")));

So it takes the dates from the original form that opens the report, and not from the report itself.

Now just need to cover scenario when startTime and endTime are left blank (but that's another problem for another day)
If I manage to do this, for the sake of completeness I will post again.

Thanks for the suggestions, y'all

Gavin

 

[gavin31]

Remou

Sorry, just seen your last post about the On Open event of the subreport after writing my previous reply.

Will try that tomorrow (It's 17:15 my time and time to go home for the day...)

Cheers

Gavin

 

[Remou]

I think your current solution is a better idea.