The Linux Page

Report Sub-forms & their Recordset

Today I resolved a problem with setting up a sub-form recordset in a report.

In a form, you can do a lot of things, but reports are very much constrained. Most certainly because that way they are more sure that the data won't change while printing the report. But for sure, it is very annoying.

I got this error first:

"This feature is only available in an ADP"

In itself, that error means totally nothing. I have no clue what an ADP is. The solution to that error is available in Microsoft website here:

Notice that the error says "an MDB" not "an ADP". Go figure...

"This feature is only available in an MDB"

This happens when you attempt to modify a parameter of a sub-report from the parent report or a sub-report function other than the Open function. In other words, after the report was opened, it is ready to go and cannot be modified anymore (I guess it is marked Read-only or something like that...)

Now, before finding the correct solution on that Microsoft website, I tried many other things that were mentioned by people on the net. That gave me another error:

"The settings you entered isn't valid for this property"

Again, that error was not too clear... I was trying to setup the Filter of the report and that accepts a string and I was for sure using a string. So?! What's that error???

Again, that's because of that "Read-only" state once the Open function of your sub-Report returned.

On my end, I was writing a report with a lot of macro code because it accesses QuickBooks and it can be dead slow to write complex Queries that access QB and the MS-Access database.

This took me about 3 hours to fix! Darn!

Private Sub MySubForm_Open(Cancel As Integer)
   RecordSource = "SELECT * FROM table WHERE id = 3 ORDER BY name"
End Sub

Note that apparently you can still use the Filter, OrderBy and other fields. I just use the RecordSource with a complete SQL statement which seems simpler to me.