cannot assign query to a Subform

[Mangesh]

Hi,

I created a form (Form1), and a subform within (Child85) i.e. its an
unbound subform. I then created a button on the form and put the
following code for the button:

sql1 = "Select * from MY_Table"
Form_Form1.Child85.Form.RecordSource = sql1
Form_Form1.Child85.Requery

However, when I click the button, I get the following error:

Run time error 2467
The expression you entered refers to an object that is colsed or
doesn't exist

What am I doing wrong? I checked a lot of threads but am not able to
get a solution for my problem.

- Mangesh

 

[John W. Vinson]

Hi,

I created a form (Form1), and a subform within (Child85) i.e. its an
unbound subform. I then created a button on the form and put the
following code for the button:

sql1 = "Select * from MY_Table"
Form_Form1.Child85.Form.RecordSource = sql1
Form_Form1.Child85.Requery

However, when I click the button, I get the following error:

Run time error 2467
The expression you entered refers to an object that is colsed or
doesn't exist

What am I doing wrong? I checked a lot of threads but am not able to
get a solution for my problem.

- Mangesh

Try

Forms!Form_Form1.Child85.Form.RecordSource = sql1
Forms!Form_Form1.Child85.Requery

explicitly referencing the Forms collection.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Ron2006]

Since (assuming) that the button is sitting on Form1 as is child85 I
would write the code as:

me.Child85.recordsource = sql1
me.Child85.requery

Ron

 

[Mangesh]

Thanks John, but now I get a new error:

Run time error 2450
Microsoft Office Access can't find the form Form_Form1referred to
in a macro expressionor visual basic code.

 

[Mangesh]

Thanks Ron,

But
me.Child85.recordsource = sql1
gives the error
Compile error: Method or data member not found

So changing it to
Me.Child85.Form.RecordSource = sql1
gives the earlier error
Run time error 2467 The expression you entered refers to an object
that is colsed or doesn't exist

And chaning it to
Me.Child85.SourceObject = sql1
gives the error
Runtime error 3011
The microsoft Jet database engine could not find the object
'~sq_cForm1~sq_cChild85'. Make sure the object exists and that you
spell its name and the path name correctly.

Stumped...!

 

[Mangesh]

Also, please note that

MsgBox Me.Child85.Name

prints the name of the form. However

Me.Child85.SourceObject = sql1

erros out as mentioned in the previous post.

 

[John W. Vinson]

Thanks John, but now I get a new error:

Run time error 2450
Microsoft Office Access can't find the form Form_Form1referred to
in a macro expressionor visual basic code.

Well, I can't see your screen, and you haven't posted the name of your form,
whether it is itself a subform, or the name of the subform control. The syntax
needs to be correct, and *I* don't know the names of the objects to put in the
expression! Could you give me some help?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[David W. Fenton]

Try

Forms!Form_Form1.Child85.Form.RecordSource = sql1
Forms!Form_Form1.Child85.Requery

explicitly referencing the Forms collection.

There is no need at all for a requery after changing the
recordsource, as the data returned will be exactly the same
(assuming, of course, that no data is added/deleted/edited between
assigning the recordsource and requerying it).

 

[David W. Fenton]

:

sql1 = "Select * from MY_Table"
Form_Form1.Child85.Form.RecordSource = sql1
Form_Form1.Child85.Requery

What about:

sql1 = "Select * from MY_Table"
Me!Child85.Form.RecordSource = sql1

Does that work?

(the requery is entirely redundant, as changing the recordsource
reloads the requested data already)

 

[Access Developer]

Also, please note that

MsgBox Me.Child85.Name

prints the name of the form. However

Me.Child85.SourceObject = sql1

erros out as mentioned in the previous post.

A query or SQL statement is not a valid SourceObject for a Subform Control.

Create a text box, txtSeeIt, in the main form and try Me!txtSeeIt =
Me.Child85.SourceObject.

 

[Mangesh]

Thanks John / David / Larry for your responses.

Larry,
Your note gave me a clue and finally I got my subform working. Now I
simply write the new query to a pre-saved query, which is assigned to
the subform as
Form_Form1.SubForm.SourceObject = "Query.Query1"
Although, I am still not able to use the recordsource property, but my
job is done.

''' Working code
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("Query1")
qdf.SQL = "Select * from table2"
Set qdf = Nothing
Me.Child0.SourceObject = "Query.Query1"


John,
I kept the default names in the example I posted, so the form was
Form1, subform was child85, etc.

David,
sql1 = "Select * from MY_Table"
Me!Child85.Form.RecordSource = sql1
still does not work. If I keep the subform unbound, I get an error:
2467, expression refers to a closed object. And if I bound the form to
some object, then I get the error #Name? in the fields.
All the same, thanks as I am finally using the code on top which works
great.

Thanks to all of you for the help.

 

[David W. Fenton]

A query or SQL statement is not a valid SourceObject for a Subform
Control.

A SQL statement isn't, but a saved QueryDef is. I have an app where
the customer needs a form that displays a crosstab query of sales,
and I use an unbound form with a subform whose sourceobject is the
saved crosstab query. It works great.

 

[David W. Fenton]

:

sql1 = "Select * from MY_Table"
Me!Child85.Form.RecordSource = sql1
still does not work. If I keep the subform unbound, I get an
error: 2467, expression refers to a closed object. And if I bound
the form to some object, then I get the error #Name? in the
fields. All the same, thanks as I am finally using the code on top
which works great.

It sounds like you don't have a subform embedded in your subform
control. Hence, Me!Child85.Form has nothing to return.

I'm glad you solved your problem by just assigning a saved QueryDef
as the source object of the subform control. My only comment on that
would be wondering why you believe you need to rewrite it every time
you use it. You should be able to filter it on the fly, seems to me,
though you'll probably want to hide the subform control until you've
set the filter on it.