This fails as well:
Code:strSQL = "SELECT TDate, Description, Credit FROM tblRegister WHERE (((TDate)>=#" & Me.OpenArgs & _ "#) AND ((Credit)>0)) ORDER BY TDate;" Me.ChildReceipts.Report.RecordSource = strSQL
Been a long time since I did anything with OpenArgs.
Does/is the SQL string correctly formed?
Maybe add a message box after strSQL or add Debug.Print strSQL and a breakpoint
orCode:strSQL = "SELECT TDate, Description, Credit FROM tblRegister WHERE (((TDate)>=#" & Me.OpenArgs & _ "#) AND ((Credit)>0)) ORDER BY TDate;" Debug.Print strSQL Me.ChildReceipts.Report.RecordSource = strSQL
Are the report master fields and sub report child fields linked? Curious if the linking field is in the SQL.Code:strSQL = "SELECT TDate, Description, Credit FROM tblRegister WHERE (((TDate)>=#" & Me.OpenArgs & _ "#) AND ((Credit)>0)) ORDER BY TDate;" Msgbox strSQL Me.ChildReceipts.Report.RecordSource = strSQL
Maybe this will help? http://access.mvps.org/access/forms/frm0031.htm
Here's the formed string:
The assignment statement:
And the error produced:
The use of sub-reports is new to me, so I'm not at all understanding what might be going on. When I created the sub-report control, I specified its SourceObject as QReceipts, the name of a query. What shows as I look at the property sheet for the control is Query.QReceipts. I tried setting the strSQL string to include the qualifier "Query.Select.........." but get the same error. There isn't any RecordSource for the main report, so the Child and Master Links are empty.
Don't know how to proceed here unless there's a way to actually update the query itself before the report is opened?
See this for info on subreport sourceobject - poster had an issue and it has a solution.
I think the strSQL is being interpreted as the name of the subreport.
If you put a breakpoint on the line where the error occurs, and in the vbe do View Locals, and run to the breakpoint; then you can query the values of controls and variables.
You might be able to trace Me.ChildReceipts. ???? and see what Access has thinks you mean..
Hardly how I expected to resolve the issue, but time is of the essence.
The popup form that obtains the date of interest modifies the query def before opening the main report. Once the user enters the date into text box "tbBegDate" and clicks "OK" the command event updates the SourceObject queries for the two sub-reports contained in rptTresReport before opening that report.
The "ModifyQuery" sub just updates the queries to their revised SQL.
Code:Private Sub cmdOK_Click() Dim strQReceipts As String Dim strQExpences As String 'Trying to Change the SourceObject of the sub-reports in rptTresReport became futile, so the query defs are modified here strQReceipts = "SELECT TDate, Description, Credit FROM tblRegister WHERE (((TDate) >= #" & Me.tbBegDate & "#)" & _ " And ((Credit) > 0)) ORDER BY TDate;" Call ModifyQuery("QReceipts", strQReceipts) strQExpences = "SELECT TDate, Description, Debit FROM tblRegister WHERE (((TDate) >= #" & Me.tbBegDate & "#)" & _ " And ((Debit) > 0)) ORDER BY TDate;" Call ModifyQuery("QExpences", strQExpences) DoCmd.OpenReport "rptTresReport", acViewPreview End Sub
Orange, there was never a problem with the forming of the SQL string, only with the attempt to set a new SourceObject to use the modified SELECT.
Bill,
That was my point. I understand your SQL. I think Access thinks your strSQL is the name of your report or SourceObject.
I think Access is confused, whether because of syntax or whatever, and is not seeing the SQL as you intended.
My untested approach was to use a breakpoint, stop there and investigate some controls and properties. If you get a look at a few controls and values, you may see something and adjust.
I am interested in gaining more knowledge about what is happening, so I'll investigate further in a day or two. As I said earlier, time is of the essence so I had to move on for the time being.
Also, it turns out that I need to know in the OnOpen event of the report if there are no records returned in the sub-reports, so a DCount in the updated SourceObject queries has become handy.
Please note that "Description" is a reserved word in Access.Should have brackets around it or change to "Desc" or "EntryDesc"...