Solved Subform Record Source - Method or data member not found

[stardustvega]

I have a dynamic query which is generated by a function (it updates every day). When my parent report loads, I want it to pull in the subreport using this query.

Currently, I'm using this code:

Private Sub Report_Load()

Dim dailyQ As String
dailyQ = GetDailies()

S_RecurSub.SourceObject = "Report.S_Repeat"
S_RecurSub.RecordSource = dailyQ

End Sub

I've verified that the query is correct.

When I try to run this code, I get "Compile error: Method or data member not found".

I also found this thread: Similar issue, and based on that, I tried changing the last line to

Me.S_RecurSub.Form.RecordSource = dailyQ

That generates an error of: "Run-time error '2467': The expression you entered refers to an object that is closed or doesn't exist."

I have verified that the subreport is indeed named S_RecurSub.

 

[plog]

What is GetDailies() returning?

 

[stardustvega]

A SQL query in the form of a string. The full function is as follows, and it's being run from a module:

Function GetDailies() As String

Dim dayNum As Integer
Dim wkDay, wkNum, mth As String

dayNum = day(Date)
wkDay = Format(Date, "ddd")
mth = Format(Date, "mmm")

Select Case dayNum
    Case 1 To 7
        wkNum = "Wk1"
    Case 8 To 14
        wkNum = "Wk2"
    Case 15 To 21
        wkNum = "Wk3"
    Case 22 To 31
        wkNum = "Wk4"
    Case Else
        msg = MsgBox("Something has gone wrong.  Day of month is out of bounds.", vbCritical, "Error")
        Exit Function
End Select

GetDailies = "" & _
    "SELECT T_Tasks.TaskName, T_Tasks.RecFreq, L_ChiCat.ChiCatName, T_Tasks.EnDemand, StrConv([FreqName],3) AS Freq, " & _
    "T_Tasks.RecFreq, T_Recurrance." & wkDay & ", T_Recurrance." & wkNum & ", T_Recurrance." & mth & ", T_Tasks.IDParCat " & _
    "FROM ((L_ChiCat INNER JOIN T_Tasks ON L_ChiCat.IDChiCat = T_Tasks.IDChiCat) INNER JOIN L_Freq " & _
    "ON T_Tasks.RecFreq = L_Freq.FreqID) LEFT JOIN T_Recurrance ON T_Tasks.TaskID = T_Recurrance.TaskID " & _
    "WHERE (((T_Tasks.RecFreq)=1) AND ((T_Tasks.IDParCat)=3)) OR (((T_Recurrance." & wkDay & ")=True) AND ((T_Tasks.IDParCat)=3)) " & _
    "OR (((T_Recurrance." & wkNum & ")=True) AND ((T_Tasks.IDParCat)=3)) OR (((T_Recurrance." & mth & ")=True) AND ((T_Tasks.IDParCat)=3));"


End Function

I know the query works because if I load the subreport proper, it has the following code:

Private Sub Report_Load()

dailyQ = GetDailies()
Me.RecordSource = dailyQ

End Sub

And that works fine. Unfortunately, it doesn't appear to apply when it's loaded from the parent form.

 

[stardustvega]

I'm almost wondering if this is a scope-related issue but I can't work out what it would be since I've used MsgBox to verify the query is coming in correctly.

 

[plog]

Forms/reports aren't my specialty, perhaps someone else will chime in. But my shot in the dark is that you are not correctly referencing the subreport from the main report:

S_RecurSub.RecordSource = dailyQ

I think you have to start from the current report (or Me!) and then go down to the subreport:

Me.SubformName.RecordSource = dailyQ


Oh and your query is killing me. Does T_Recurrance have 44+ fields? 31 days + 12 months + TaskID?

 

[The_Doc_Man]

I'm inclined to say that there must be something off about this line:

S_RecurSub.SourceObject = "Report.S_Repeat"

I suspect that should be perhaps Reports!S_Repeat IF the S_Repeat report is open at the time. The property name Report.S_Repeat doesn't seem quite right there because I'm pretty sure that the current main report doesn't have .S_Repeat as a report property name or a control name. (And if it DID have that as a control name, there is the further issue that it would have to be text-oriented and contain the actual name you wanted. Perhaps you really need to use the AllReports.xxx collection reference rather than using Report.xxx - and if that report is closed at the time, also rather than Reports.xxx, because Reports. is all OPEN reports.

 

[Pat Hartman]

Put a stop in the code on the line after you set the value. Copy the string to the QBE and run it. Does it work?

 

[CJ_London]

I think this line is wrong

S_RecurSub.RecordSource = dailyQ

Think it should be


S_RecurSub.report.RecordSource = dailyQ

 

[stardustvega]

@plog : Nope, I still get "Compile error: Method or data member not found".

Regarding the query, yeah that's why I didn't post it in my top line post. Essentially, it filters recurring tasks so that I get 1) any tasks that get done every day, 2) any tasks that recur every [whatever day it is today], 3) any tasks that recur every [week of the month it is now], and 4 any tasks that recur every [month that it is now]. But since I use this exact query (generated from the same function) in a subreport, I'm confident the SQL is correct.

@CJ_London : AHA! Yes, that is correct!

What I don't fully get is why it's correct? Why do I need to put .Report there and not in the .Source Object line? Is it due to some ambiguity?

@The_Doc_Man : I actually have a parent report open (R_Tasks) and was trying to set the record source for the subreport when the parent report loads.

Now that I'm thinking of it, I probably shouldn't be using subreports at all, I should probably be using a query as a source for the parent report and then using sorts and groups to achieve what I want. However, CJ_London's solution worked for me.

Thank you all!

 

[arnelgp]

What I don't fully get is why it's correct

because it is a SubReport, same as SubForm:

Forms!yourForm.SubForm.Form.RecordSource =...

 

[CJ_London]

What I don't fully get is why it's correct?

because
S_RecurSub

is the subreport control on the main report

you then need to 'go deeper' to get to the actual report/form it contains

.Report

and finally to one of its objects/properties

.RecordSource

with a text box or other control it is the same principle, but you don't normally need to 'go deeper'