set recordsource of a subform

[Guest]

I have an unbound subform that I am binding at runtime in VBA. I want to change the recordsource of the subform based on criteria selected by the user. Two options are: date range or a number. The user needs to be able to select the last X work orders where X is an integer. I am creating a SQL statement for the second option and want to do one of two things: set the recordsource of the subform to the SQL statement OR create a recordset from this SQL statement and bind the subform to the recordset

I have tried both and get error msgs -- either 2455 (I've made an invalid reference to the form) or the property is not supported. I read Allen Browne's faq re: the name autocorrect problems and have imported all of my objects into a new db but still got the errors

I have tried referencing the subform as follows
me.subform.form.recordsource = strsq
me.subform.form.recordset = r
forms!mainform.subform.form.recordsource = strsq
me.form(subform).recordset = r
None of the above worked

Thanks for any help
Diana

 

[Jonathan Parminter]

-----Original Message-----
I have an unbound subform that I am binding at runtime in

VBA. I want to change the recordsource of the subform
based on criteria selected by the user. Two options are:
date range or a number. The user needs to be able to
select the last X work orders where X is an integer. I am
creating a SQL statement for the second option and want to
do one of two things: set the recordsource of the subform
to the SQL statement OR create a recordset from this SQL
statement and bind the subform to the recordset.

I have tried both and get error msgs -- either 2455 (I've

made an invalid reference to the form) or the property is
not supported. I read Allen Browne's faq re: the name
autocorrect problems and have imported all of my objects
into a new db but still got the errors.

I have tried referencing the subform as follows:
me.subform.form.recordsource = strsql
me.subform.form.recordset = rs
forms!mainform.subform.form.recordsource = strsql
me.form(subform).recordset = rs
None of the above worked.

Thanks for any help!
Diana
.

Hi Diana,
The line...

me.subform.form.recordsource = strsql

....will work so long as 'subform' is the name of the
subform control for the subform object.

Assuming this reference is correct, then I can only
suggest that you print the strsql to the debug window.
Then copy/paste the sql to the sql view of a new query to
confirm that the sql is valid.

Hope this helps

Luck
Jonathan

 

[Guest]

-----Original Message----

I have an unbound subform that I am binding at runtime in

VBA. I want to change the recordsource of the subform
based on criteria selected by the user. Two options are:
date range or a number. The user needs to be able to
select the last X work orders where X is an integer. I am
creating a SQL statement for the second option and want to
do one of two things: set the recordsource of the subform
to the SQL statement OR create a recordset from this SQL
statement and bind the subform to the recordsetmade an invalid reference to the form) or the property is
not supported. I read Allen Browne's faq re: the name
autocorrect problems and have imported all of my objects
into a new db but still got the errors

me.subform.form.recordsource = strsq
me.subform.form.recordset = r
forms!mainform.subform.form.recordsource = strsq
me.form(subform).recordset = r
None of the above worked
Dian

Hi Diana
The line..

me.subform.form.recordsource = strsq

....will work so long as 'subform' is the name of the
subform control for the subform object

Assuming this reference is correct, then I can only
suggest that you print the strsql to the debug window.
Then copy/paste the sql to the sql view of a new query to
confirm that the sql is valid

Hope this helps :-

Luc
Jonatha
-------------------------------
Jonathan

I have gone so far as to change the name of my subform control so that I am absolutely sure I'm referencing the correct subform name and I still get an error 2455: You entered an expression that has an invalid reference to the property Form/Report. I even went ahead and bound both of the subforms to the forms I want to use but removed the recordsource so I could just set it in VBA after the user entered the criteria

Any other suggestion on how to create a parameter query using a "SELECT TOP" SQL statement? If I could create and save the query then what I might do is just create copies of the subforms -- make an A and a B -- so that I can simply set the source object at run-time. That would be easier since I know that will work without giving me an error message

Thanks!
Diana

 

[Jonathan Parminter]

Hi Diana,
first try to isolate the problem to avoid using work-
arounds to fix.

consider the following

dim frm as form
set frm =me.subform.form
frm.recordsource = strsql

using the debug window to step throught your code, which
line causes the error?

If it's the 'set frm...' then
you know it is the reference (look for spelling mistakes)
else
you know it is the strsql
end if

regarding creating a parameter query, one approach is to
create a standard query. then switch to sql view. copy sql
string into vbe. use variables to replace criteria in
string. assign users values to variables.

Luck
Jonathan

Jonathan,

I have gone so far as to change the name of my subform

control so that I am absolutely sure I'm referencing the
correct subform name and I still get an error 2455: You
entered an expression that has an invalid reference to the
property Form/Report. I even went ahead and bound both of
the subforms to the forms I want to use but removed the
recordsource so I could just set it in VBA after the user
entered the criteria.

Any other suggestion on how to create a parameter query

using a "SELECT TOP" SQL statement? If I could create and
save the query then what I might do is just create copies
of the subforms -- make an A and a B -- so that I can
simply set the source object at run-time. That would be
easier since I know that will work without giving me an
error message.