By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 2,318 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Error 2467 setting subforms recordsource

P: 6
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub btn1_Click()
  2.     strSQL = "SELECT * FROM tbl_SECL_List"
  3.     Me!subtest.Form.RecordSource = strSQL ' error occurs here
  4. 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
Jan 7 '08 #1
Share this Question
Share on Google+
8 Replies


Expert 100+
P: 374
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:

Expand|Select|Wrap|Line Numbers
  1.      me.subformname.RecordSource = strSQL
  2.  
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:

Expand|Select|Wrap|Line Numbers
  1.      me.listcontrolname.rowsource = strSQL
  2.      me.listcontrolname.requery
  3.      me.repaint
  4.  
Jan 7 '08 #2

P: 6
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.
Jan 7 '08 #3

FishVal
Expert 2.5K+
P: 2,653
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
Expand|Select|Wrap|Line Numbers
  1. debug.print me!subtest.form.name
  2.  
before faulty string.
Jan 7 '08 #4

P: 6
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
Expand|Select|Wrap|Line Numbers
  1. debug.print me!subtest.form.name
  2.  
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?
Jan 7 '08 #5

FishVal
Expert 2.5K+
P: 2,653
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.
Jan 7 '08 #6

P: 6
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.
Jan 7 '08 #7

P: 6
OH EM GEE Holy Crap.

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

Expand|Select|Wrap|Line Numbers
  1. Dim dbsCurrent As Database
  2. Dim qryDef As QueryDef
  3.  
  4. Public Sub btnSeclAll_Click()
  5.     Set dbsCurrent = CurrentDb
  6.     Set qryDef = dbsCurrent.QueryDefs("qryTest")
  7.     qryDef.SQL = "SELECT * FROM tbl_SECL_ALL;"
  8.     subTest.SourceObject = "Query.qryTest"
  9. End Sub
  10.  
  11. Private Sub btnUser_Click()
  12.     Set dbsCurrent = CurrentDb
  13.     Set qryDef = dbsCurrent.QueryDefs("qryTest")
  14.     qryDef.SQL = "SELECT * FROM tbl_User;"
  15.     subTest.SourceObject = "Query.qryTest"
  16. 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...
Jan 7 '08 #8

FishVal
Expert 2.5K+
P: 2,653
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
Jan 7 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.