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
8 23980
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
-
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 2,653
Recognized Expert Specialist
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(datash eet)/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.
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(datash eet)/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 2,653
Recognized Expert Specialist
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.Contr ols" 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.
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.
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 2,653
Recognized Expert Specialist
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
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Annelies |
last post by:
Hi,
I want to set the recordsource of a subform to a query with this
syntax :
Forms!Form_patient!sub75.form.recordsource="MyQueryName"
the problem is after typing sub75. I get a list of possilities, but
after form. I don't.. I know nothing about access, so can anybody help
me?
Greetzzz
|
by: deko |
last post by:
RE: "Object Invalid or No Longer Set" errors
I've had an angelic visitation and the revelation is that my problem, in
essence, is due to the fact that I have multiple forms open at the same time
whose recordsources draw data from the same table.
I use a tabbed interface: frmMain has 4 tabs. On each tab there is a form
and at least one subform (datasheet). So we're talking at least 5 subform
datasheets, on different tabs, all open at...
|
by: Gianluca L. Monteleone |
last post by:
Hi Folks!
I have this strange error that happens after conversion from A97 to
AXP (2000-Format). Looks like a bug to me - I've lost 3 days with no
result.
I use a form with 2 subforms on it, one to set criterias and one that
reflects data upon selected criterias. This works perfectly. But
sometimes I need to get started with an already "restricted" dataset.
So what I do is to change at run-time the form.subform.recordset
|
by: Lyn |
last post by:
Hi, this has been driving me nuts. I'm on Office 2003 SP1, Win XP SP1.
I am opening a form with a number of subforms based on various tables. The
subforms are populated via the main form's Current event. I am using
similar code to open each of the subforms successfully -- except for this
one case which gives the above error.
I have simplified the SQL to its most basic level. It runs just fine as a
query. I just can't make it work...
|
by: Jimart |
last post by:
Hi
I Have A Main Form this form has a Subform The SourceObject for the
Subform is another subform.
I have mastered changing the SourceObject, so that I can have
different Subforms appearing when I need them BUT what I need to be
able to do is to change the RecordSource of the SourceObject Subform
from the code behind the Main Form, so I can reuse the SourceObject
Subforms
| |
by: Bex |
last post by:
Hi All.
I'm attempting at the moment to set the recordsource of an unbound
subform when i open up it's main form.
My view is to have one intial form - called "Search" which has a number
of unbound textboxes. When a user enters in a value into a field and
hits the search button this opens up a results form called "people3"
and sets a subform in this "people3" form - called "results" to show
certain records matching the entered search...
|
by: (PeteCresswell) |
last post by:
I've got a parent form and three subforms.
Two of them work A-OK.
For the third one, in the Immediate window:
-----------------------------------------
?Me.subDeal.Name
subDeal
?Me.subDeal.SourceObject
|
by: Ecohouse |
last post by:
I have a main form with two subforms. The first subform has the child
link to the main form identity key.
subform1 - Master Field: SK
Child Field: TrainingMasterSK
The second subform has a master-child link to the first subform.
subform2 - Master Field: subTrainingModule.Form!TrainingModuleTopicSK
Child Field: TrainingModuleTopicSK
|
by: qianiqan |
last post by:
We have three forms: Main form, SearchForm and its subform SubFormS.
Click the button cmdA in MainForm will open the SearchForm. The Search
form has a subform SubFormS.
The Access database has two version db04 and db09. In the old version
db04 when I click the command cmdA,
it goes first to Form_open of subformS, then goes to the form_open of
the SearchForm. Everything is fine.
In the new version db09 when I click the command cmdA, it...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |