Error 2467 setting subforms recordsource

[fuze]

I currently have a form with a couple text boxes, a button, and an unbound subform. Pressing the button will assemble a SQL string using whatever is in the textboxes and assign the SQL string to the recordsource of the subform.

Unfortunately, doing so results in the run-time error 2467 - "the expression you entered refers to an object that is closed or does not exist."

below is a simple version of my code

Private Sub btn1_Click()
    strSQL = "SELECT * FROM tbl_SECL_List"
    Me!subtest.Form.RecordSource = strSQL ' error occurs here
End Sub

I've tried to do the same thing, but using a recordset but the same error comes up.

Any suggestions of how to get around this? Or any explanation as to why this is happening?

Any help is greatly appreciated

[PianoMan64]

Hello,


My first question is, are you going to be entering any data into the subform?

and second, the syntax for the line that you're referring too is incorrect.

it should be:

     me.subformname.RecordSource = strSQL
 

But you are better off, if you're not going to be entering data, to use a ListControl instead.

Then all you would have to do to refer to it is this:

     me.listcontrolname.rowsource = strSQL
     me.listcontrolname.requery
     me.repaint
 

[fuze]

There will be no need for data entry, as it's strictly inquiry only.

The syntax is correct as far as I'm aware. Using me.subformname.RecordSource = strSQL brings about an error saying the object does not support that property or method.

A list control would work nicely in code, but it does not provide the fuctionality I need. The users of this tool will be taking the output of these highly dynamic queries and copying their output to excel. I really need something that will display as the datasheet view.

[FishVal]

Hi, Fuze.

Your initial syntax is quite ok.

Check that [subtest] is the name of subform control (not subform form object).
I recall the same situation with mainform(datasheet)/subform(any) - Access sometimes performs late load "while the object is not visible it is not loaded". Check that the subform form is loaded (you just see it).
Check that form object is accessible - add

debug.print me!subtest.form.name
 

before faulty string.

[fuze]

Hi, Fuze.

Your initial syntax is quite ok.

Check that [subtest] is the name of subform control (not subform form object).
I recall the same situation with mainform(datasheet)/subform(any) - Access sometimes performs late load "while the object is not visible it is not loaded". Check that the subform form is loaded (you just see it).
Check that form object is accessible - add

debug.print me!subtest.form.name
 

before faulty string.

Thanks FishVal!

I feel a bit stupid now, but I was referencing the subform form object, not the control. I finally "get" how access goes about referring to everything but I'm a bit disappointed now in what it means for my tool.

Initially, I was just seting the subform control source object to a query object, and using the forms textboxes as variables in my query. This proved to be a problem eventually, because these objects are only accepted in the "WHERE" portion of the clause.

Changing my strSQL with different selection criteria doesn't display properly. It essentially means that I need a different subform for each variant of SQL statement...

Does using recordsets fix this problem?

Update: I tried it with recordsets and it does not solve this inconvenience. Does anyone know a convenient way to change the columns of a form in datasheet view?

Just a shot in the dark, I'm assuming it would be like, for every column name in the recordset, add column to form?

[FishVal]

Thanks FishVal!

I feel a bit stupid now, but I was referencing the subform form object, not the control. I finally "get" how access goes about referring to everything but I'm a bit disappointed now in what it means for my tool.

I see nothing weird in Access.Form object model.
Really expression like Me![ControlName] returns a member of "Controls" collection returned by the default "Form" class property - "Form.Controls". (Sometimes (if no control found) clever "Form.Controls" property returns reference to a same-named AccessField object which allows read/write linked recordset field).
So you reference Subform control which is a container of "Form" (or not) object.

Initially, I was just seting the subform control source object to a query object, and using the forms textboxes as variables in my query. This proved to be a problem eventually, because these objects are only accepted in the "WHERE" portion of the clause.

Changing my strSQL with different selection criteria doesn't display properly. It essentially means that I need a different subform for each variant of SQL statement...

Does using recordsets fix this problem?

Update: I tried it with recordsets and it does not solve this inconvenience. Does anyone know a convenient way to change the columns of a form in datasheet view?

Just a shot in the dark, I'm assuming it would be like, for every column name in the recordset, add column to form?

Aha. That causes the fault.

Is that really needed to set query as subform's source object?
Why not to design separate form and set it as source object for the subform?
This way you'll be able to get full Form class functionality including RecordSource property as well as design form event handlers in subform's form.

[fuze]

Why not to design separate form and set it as source object for the subform?

Depending on the input, the columns displayed (and possibly the tables queried) will change.

I'm currently looking at about 10 to 15 different datasheet formats I'd need to set up as subforms. Not too much of a problem, just a bit tedious.

[fuze]

OH EM GEE Holy Crap.

This was just a quick test, and it works beautifully as far as simplicity is concerned.

Dim dbsCurrent As Database
Dim qryDef As QueryDef
 
Public Sub btnSeclAll_Click()
    Set dbsCurrent = CurrentDb
    Set qryDef = dbsCurrent.QueryDefs("qryTest")
    qryDef.SQL = "SELECT * FROM tbl_SECL_ALL;"
    subTest.SourceObject = "Query.qryTest"
End Sub
 
Private Sub btnUser_Click()
    Set dbsCurrent = CurrentDb
    Set qryDef = dbsCurrent.QueryDefs("qryTest")
    qryDef.SQL = "SELECT * FROM tbl_User;"
    subTest.SourceObject = "Query.qryTest"
End Sub

As its written, the button changes the query object that the subform control is bound to, then refreshes it. Based on this I could easily change the query based on the input, and reassign it.

FishVal - Is there any reason why this might be bad? Do you know of an easier or possibly more efficient solution?

Thanks again for all the help - I can't tell you how long I was stuck on that whole control/object mixup...

[FishVal]

FishVal - Is there any reason why this might be bad? Do you know of an easier or possibly more efficient solution?

That is the question of design style. With form set as source object to your subform control your initial code would work smoothly too.
As for me I prefer to use form as it gives more functionality: events handling, more properties are accessible, more design options etc. Even if I'm not planning to use them at some particular stage of development I just "keep an option"

Though I wouldn't like to force you in your decision.

Good luck,
Fish