Solved Subform Record Source - Method or data member not found (1 Viewer)

stardustvega

Member
Local time
Today, 09:13
Joined
Feb 4, 2022
Messages
36
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:

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

Code:
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

Banishment Pending
Local time
Today, 09:13
Joined
May 11, 2011
Messages
11,611
What is GetDailies() returning?
 

stardustvega

Member
Local time
Today, 09:13
Joined
Feb 4, 2022
Messages
36
A SQL query in the form of a string. The full function is as follows, and it's being run from a module:

Code:
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:

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

Member
Local time
Today, 09:13
Joined
Feb 4, 2022
Messages
36
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

Banishment Pending
Local time
Today, 09:13
Joined
May 11, 2011
Messages
11,611
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

Immoderate Moderator
Staff member
Local time
Today, 09:13
Joined
Feb 28, 2001
Messages
26,999
I'm inclined to say that there must be something off about this line:

Code:
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

Super Moderator
Staff member
Local time
Today, 10:13
Joined
Feb 19, 2002
Messages
42,970
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

Super Moderator
Staff member
Local time
Today, 14:13
Joined
Feb 19, 2013
Messages
16,553
I think this line is wrong
S_RecurSub.RecordSource = dailyQ
Think it should be


S_RecurSub.report.RecordSource = dailyQ
 

stardustvega

Member
Local time
Today, 09:13
Joined
Feb 4, 2022
Messages
36
@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!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:13
Joined
Feb 19, 2013
Messages
16,553
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'
 

Users who are viewing this thread

Top Bottom