473,836 Members | 2,150 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Setting Subform RecordSource in VBA

13 New Member

I'm trying to utilize VBA to set a subform's recordsource on a command button click. My form is used to search by a query and display the results in the subform. If I set the subform source in the property sheet it works properly but loads the full query on opening the form. I want the subform to be blank until I click the search button. Here is my button's code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnSearch_Click()
  2. On Error GoTo btnSearch_Click_Err
  4.     Forms![frmQuickSearchMedia]![Media Search subform].Form.RecordSource = "Media Search"
  5.     Forms![frmQuickSearchMedia]![Media Search subform].Form.Requery
  9. btnSearch_Click_Exit:
  10.     Exit Sub
  12. btnSearch_Click_Err:
  13.     MsgBox Error$
  14.     Resume btnSearch_Click_Exit
  16. End Sub
When I click the button I get an error stating:
The expression you entered refers to an object that is closed or doesn't exist.

Is this an error in my button code or a consequence of my form to query interaction?

Thank you

Dec 19 '12 #1
16 30391
8,834 Recognized Expert Expert
You need to Requery the SubForm Control itself, and not the actual Form that is its Record Source. Try:
Expand|Select|Wrap|Line Numbers
  1. With Forms![frmQuickSearchMedia]![Media Search subform]
  2.   .Form.RecordSource = "Media Search"
  3.      .Requery
  4. End With
Dec 19 '12 #2
32,584 Recognized Expert Moderator MVP
ADezii's response is (pretty close to) perfect. It handles the error message you quote as well as explaining why it should work.

Your question has two aspects though. The other is avoiding the recordset loading until actually specified by the Button click. This is easier than it might appear. Simply design (Save) the form with no RecordSource value.

I suspect though, that with more judicious use of existing form properties and calling parameters, you could do this without resorting to such a cumbersome approach. Filter properties can be passed to a form as part of its open call, for instance. I'll leave that thought with you. It might result in a separate question.
Dec 20 '12 #3
13 New Member
Thank you. This worked out. Is there a trick to knowing when it is the control and when it is the form that needs to be requeried? I've just delved into utilizing VBA and Access. Your time and assistance is most appreciated.
Dec 20 '12 #4
13 New Member
Thank you for your reply. You are correct that I was trying to avoid the recordset loading until the button click. Neither the main form or subform have a RecordSource value. I guess I'm not exactly sure I follow what you mean by just removing the RecordSource, But it sounds as though it is what I did.

I'm a beginner with VBA and Access and it shows. I figured that this solution was a bit much. I'll have to see about reworking it. Thanks again, your time and insight are greatly appreciated.
Dec 20 '12 #5
32,584 Recognized Expert Moderator MVP
Thank you for your very polite replies.

I'm curious to understand why you would have a set of data already loaded if the .RecordSource property is already blank. I would expect there to be no data loaded if .RecordSource is blank.
Dec 20 '12 #6
13 New Member
My apologies I misspoke. The mainform's RecordSource is blank, the subform's SourceObject is blank, but the subform RecordSource is not. I had to recheck. Your previous statement about recordSource makes sense now. However, If I remove the RecordSource from the subform then nothing shows when I click the button.

I must have something backwards or I'm still missing something.

I've attached screenshots of the forms' data properties. I do not know if they will be of any use. This is all still quite new concepts to me (VBA to control forms and controls, etc)

I utilized the code from the original answer in the button.

Your time and assistance are greatly appreciated.

Attached Images
File Type: jpg ScreenHunter_01 Dec. 20 11.12.jpg (23.3 KB, 17705 views)
File Type: jpg ScreenHunter_02 Dec. 20 11.12.jpg (22.8 KB, 17655 views)
File Type: jpg ScreenHunter_03 Dec. 20 11.13.jpg (24.1 KB, 17594 views)
Dec 20 '12 #7
2,322 Recognized Expert Moderator Top Contributor
What I often do if I don't want the subform to load data during open is to set the recordsource to:
Expand|Select|Wrap|Line Numbers
This will load no records at startup, and then you can simply change the recordsource when you are ready.
Dec 20 '12 #8
32,584 Recognized Expert Moderator MVP
I'm afraid there's nothing there we can read. The resolution is too low. I can see that one of them has Record Source set to blank, but no names and little else at all really :-( It's really better to post words where possible. Pictures are rarely much use for anything but pictures. Even when discernable they cannot be copied easily so generally little use. I appreciate the attempt mind - just making the point from experience.

Let me try to understand you better here. Are you saying that when you save the sub form without a Record Source then it doesn't work even after running your code behind the Command Button? The code includes a Requery call, so I find that strange.
Dec 20 '12 #9
32,584 Recognized Expert Moderator MVP
This will load no records at startup, and then you can simply change the recordsource when you are ready.
Are you saying that you've had similar experiences trying to work with a subform that has no Record Source? Is this behaviour version dependent possibly?
Dec 20 '12 #10

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

Similar topics

by: Lauren Quantrell | last post by:
I'm using SQL Server backend on an Access 2K front end. I populate a subform: Forms!myForm.myChild.Form.RecordSource = "myStoredProcedureName" On that form is a control where the controlsource is "= Count()" that shows the user the number of records. What I'm doing now is populating the subform, using the RecordsetClone.RecordCount of the subform to determine if any records were returned, and if not, opening a message box telling the...
by: John M | last post by:
Hi, I want to set the recordsource of a report on the basis of the selection from a combo box. If the report is not yet open, I am told it is not open or not found. If it is open I'm told that I can't alter the recordsource it the report is 'already printing'!! I'm lost ... but then I'm not a programmer ..... Thanks in advance
by: Peter Bailey | last post by:
I have a subform that I dont want to have a recordsource initially as it is locking the table that is created dynamically. I want to connect to the table after the data has been written by another routine. I had hoped I could do mytable.recordsource = myquery
by: robert.waters | last post by:
Hello, Say that there is a database for client information; there is a main form that displays the client's name, and this form has one or more subforms that display different types of information related to that client. The main Form has RecordSource="SELECT clientid, clientname FROM client_table;", and each subform has both Link Child and Master Fields="clientid".
by: Widge | last post by:
I'm having a nightmare. I've used this technique before and can't understand why I'm getting an error using it now. Basically I have a form, on that is a sub form datasheet. What I am eventually aiming to do is have a button set the Record Source of the subform. Now the code is as follows: Private Sub Command6_Click() SetFilter
by: fuze | last post by:
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() ...
by: tnjarrett | last post by:
Hello, I have a MS Access continuous subform that was using a query as the recordsource. I changed it to use a recordset instead because when the query was used, the changes to the subform values directly changed the tables under the query. I wanted the changes to be 'in-memory' so that I could check them before applying them to the tables. When I changed the subform to use the recordset, it is now 'read-only' and I can't change anything...
by: stonward | last post by:
I'm trying to change the recordsource of a subform using a simple button click: by default, the subform's source is one query, when a button is clicked it changes to another query (the same query, but this time also showing stock items with less than 1 in stock...). Having tried all manner of methods, then having read previous posts, i see the problem may be related to link criteria? Trouble is, i have no link criteria...my subform is not...
by: thelonelyghost | last post by:
Just searched google and this website but I couldn't come up with an answer to this. Basic Information Software: Microsoft Access 2000 OS: Windows XP Professional SP3 Problem: Error 3008 (locked table, ) when trying to modify recordsource of subform. Private Sub btnSearch_Click() 'Changes recordsource of subform. subform name: frmSubform
by: reginaldmerritt | last post by:
Has anyone had this problem before, my search on-line would suggest it not common but I'm sure you fine folk will be able to help. This is the code I have used to change the recordsource on a subform. I have similar code to order the data using different queries. Private Sub OrderByStartDate_Click() Forms.FRMClaims.ClaimsDisplaySubForm.Form.RecordSource = "QYClaimsByStartDate" End Sub
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:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
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: 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.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.