Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-25-2018, 10:44 AM   #31
cage4000
Newly Registered User
 
Join Date: Oct 2015
Posts: 48
Thanks: 16
Thanked 0 Times in 0 Posts
cage4000 is on a distinguished road
Re: Closing a Recordset from another sub

Hi static, I’m not sure, I thought I was but I must be off. I will take a look at that. Thank you for pointing that out.

ridders, I disagree with you, the proof is black and white on this thread, and I’m done debating this with you. NOW please stop posting on this thread unless you have something constructive to add. If you do keep going on about this than all you are proving is that, you need to have the last word, not that you are right.

cage4000 is offline   Reply With Quote
Old 04-25-2018, 11:24 AM   #32
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,806
Thanks: 79
Thanked 1,430 Times in 1,335 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: Closing a Recordset from another sub

Quote:
Originally Posted by cage4000 View Post
ridders, I disagree with you, the proof is black and white on this thread, and I’m done debating this with you. NOW please stop posting on this thread unless you have something constructive to add. If you do keep going on about this than all you are proving is that, you need to have the last word, not that you are right.
I expect my wife would say there's some truth in your last sentence.

As I evidently haven't learned how to be polite and show humility even after 4500+ posts and over 1100 thanks, I would be extremely grateful if you could give up a little of your valuable time to point out some of the proof that you indicate is in black and white on this thread.
Doing that will I'm sure help me to provide more valuable assistance for other users in the future
No need to refer to posts 16 & 17 again which as both minty and I have explained wasn't intended as a joke at your expense though you interpreted it as that.
I look forward to your helpful advice on this matter if you would be so kind.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

New example databases:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est

Last edited by ridders; 04-25-2018 at 01:00 PM.
ridders is offline   Reply With Quote
Old 04-25-2018, 03:42 PM   #33
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 2,827
Thanks: 69
Thanked 400 Times in 361 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Closing a Recordset from another sub

You know, Minty is an Access expert, Galaxiom is a freaking master with this application, Doc was a goddamn Microsoft MVP (as in the guy who TAUGHT the masters), and ridders not only runs a business developing Access applications for schools and other businesses (and is pretty much a master himself), but spends most of his evenings here helping people just because he can. He's also one of the most even-tempered, NON-condescending people I've seen post here.

You are, quite honestly, completely out of line, and doing nothing but killing your chances of getting effective help either here or on any other Access specific site, since most of the volunteers here are the main volunteers on the other sites, too.

As a rule, when those four tell you somethihng either cannot be done or is otherwise just a bad idea, then you should listen to them. In fact, they are trying to HELP you by teaching you to make your app faster, more responsive, and way, way more stable. You should, perhaps, accept that the approach you're taking is not going to be feasible and try their suggestions.

As to your questions on this thread (assuming you've read past the above):

Any recordset, whether built into the form or created and attached during the Form_Open event, is closed when that form is closed unless you have given it global scope. It's possible that assigning it to a static variable will keep it open as well, but I've never felt the need to do something that silly.

Where I work, I have seen precisely one application that does what you're trying to do. It relies on a 5000+ line module that creates, updates, and maintains asynchronous ADO connections to DB2 databases, and it was poorly written, completely undocumented, and damned near beyond *MY* ability to figure it out (and while I'm not in the same weight class as the other four posters, I'm no slouch). It created links as database objects and held them in memory when the database was opened, and just generally made the application far less efficient, since it was mostly just doing things Access already does. And as I work with tables with hundreds of fields and billions of records (don't look at me like that Galaxiom, I didn't build them), it just KILLED performance. You should NEVER use that kind of approach if there's any possible way to avoid it.

Why precisely aren't you allowed to create links to the server in your app? You claim you don't have admin rights, but you don't need them just to create a link. If your concern is maintaining a DSN on each machine, we can teach you both how to set up DSN-less connections, and methods to have the application set them up itself so you don't have to do a thing.

Once you've done that, you can just put the necessary sql into each form's RecordSource property (or use saved queries), and let Access handle the opening and closing of each recordset. (Alternately, we can show you better ways to attach a recordset to a form when it opens - I have a few forms with no permanent recordset assigned and instead assign one as part of Form_Open, but it's due to very specific business requirements.)

Basically, stop being so defensive and work with us. Just because you're being told that what you want isn't feasible, it doesn't follow that a better solution can't be found.

__________________
This is a test.
This is only a test.
If this were a real emergency, you would NOT have been informed, and we would have fled screaming in terror.

Last edited by Frothingslosh; 04-25-2018 at 04:11 PM.
Frothingslosh is offline   Reply With Quote
Old 04-25-2018, 05:41 PM   #34
cage4000
Newly Registered User
 
Join Date: Oct 2015
Posts: 48
Thanks: 16
Thanked 0 Times in 0 Posts
cage4000 is on a distinguished road
Re: Closing a Recordset from another sub

Thank you Frothingslosh for the explanation and a appreciate all you and all of the time your co posters put into this problem. you have given me a lot to think about and i will look at this as a viable solution.

i guess the only thing i don't understand is that if a recordset closes when you close out the form why does my database keep telling me that i can not save design changes and that i have to be in exclusive mode after i close out of the form. according to every response i have received on this thread, it should not do that. i don't have any other forms open, no code running but it keeps locking up after i open the form. all i want it to do is for it to stop locking up on me and making me have to close the program all tougher just to be able to edit the forms or code again.
cage4000 is offline   Reply With Quote
Old 04-26-2018, 03:48 AM   #35
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 2,827
Thanks: 69
Thanked 400 Times in 361 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Closing a Recordset from another sub

Design changes can be caused by ANY modification, including, say, changing the width of a column if you use datasheet view. The easiest way around that particular message box is to simply disable the close button in each form's properties, and then provide a command button that does the same thing. The code attached to the button is either this:
Code:
DoCmd.Close , , acSaveNo
or, if you want to put in all the optional parameters, this:
Code:
DoCmd.Close acForm, Me.Name, acSaveNo
The recordsets have nothing to do with whether or not you can make design changes. In fact, unless you've attached the recordset to a variable that remains in-scope with the form closed, it's not even open when you have the form in design mode!

(On a side note, if you create any object and assign it to a variable, it's good practice to clean them up when you're done with them. Access does a good job of cleaning up when you close forms, but it's not always perfect. It is SUPPOSED to close recordsets assigned to variables and set all object references to Nothing automatically when a form closes, but in certain situations (especially when errors get thrown), it fails to do that. As a rule of thumb and just to play it safe, anything that gets opened should be closed when you're done with it, and any assigned object variable should be set to nothing. Some people (*cough*IncludingSomeModsHere*cough*) will argue that there's no need to close recordsets created during the running procedure, but I personally prefer to play it safe - it takes just a second or so to type out 'rs.Close', after all.)

As to the 'exclusive' thing, is anyone else using it at the same time? If anyone else at all is in the database, then you cannot save changes to it. If that's not the issue, then our normal next recommendation is to post a stripped down version of the database (just a form where you have the issue, any necessary code the form requires to work, and dummy data for it) so that people can take a look at it.
__________________
This is a test.
This is only a test.
If this were a real emergency, you would NOT have been informed, and we would have fled screaming in terror.
Frothingslosh is offline   Reply With Quote
The Following User Says Thank You to Frothingslosh For This Useful Post:
cage4000 (04-26-2018)
Old 04-26-2018, 05:53 AM   #36
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 11,990
Thanks: 56
Thanked 1,093 Times in 999 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Closing a Recordset from another sub

Quote:
why does my database keep telling me that i can not save design changes and that i have to be in exclusive mode after i close out of the form
I said I would stay out of this because I had nothing else to offer, but that turns out to be wrong. I can take a shot at that specific question because the way you framed it clarifies something that I apparently missed earlier.

When you directly assign a new recordset to an open form, it is an action that carries some baggage with it. I think that baggage is the source of your problem.

Your .Recordset change implies subsequent changes to the .RecordSource, .RecordType, and a couple of other properties of the form that would normally be changed on the form's Properties (design-mode) window. The action you are performing with that overlay of Me.Recordset therefore IS a design change. The problem is that you have the form open and actively running code in Form View mode, not in Design mode, and therefore you have some repercussions to address.

In that context, Access is telling you that you can't make that change AND SAVE IT unless you are in Design mode (which you are not) and have the database open in Exclusive mode (again, apparently not the case). The complaint stems from the fact that a "default" Close operation implies the SAVE option. For Microsoft, think about this: Every other Office program (Word, Powerpoint, Excel, etc) saves a document when you close it because that is the default for them. Why would Access be different?

You can change the .ControlSource or .RowSource properties on a form dynamically, but when I last did that, I had to take the approach described by Frothy. I built a CLOSE command button that included an acSaveNo as part of its closure command so that Access would quietly discard all of those source-record changes and revert to my "default" sources. I trapped ALL closures of the form using the Form_Unload event (which has a Cancel option) and disallowed closure unless you used the CLOSE command button to do it. The CLOSE command button "click" event set a flag and then issued the form closure via DoCmd. The other methods of form closure, because they didn't set the flag, got cancelled. FYI, it was adequate to put that flag in the Form's Declaration area at the top of the form's class module.

Frothy's comments about "close what you open" are not 100% popular because there is some truth to the idea that many objects automatically close when their base variables go out of scope. Therefore, your recordset variable in that subroutine would go out of scope the moment you hit the End Sub.

You are playing with a tricky situation - adding multiple references to an open recordset. By that I mean that temporarily, your recordset variable and your form's recordset property are BOTH pointing to the same recordset structure (since you didn't declare a NEW in any part of the process). So a "reference" counter gets ticked for each entity that has that same recordset open. I would as a matter of principle just close the variable-based recordset before I exited the routine. But then again, I am (probably even rightly) accused at times of being somewhat anal-retentive about that. But I happen to second Frothy's suggestion to close what you open.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
cage4000 (04-26-2018)
Old 04-26-2018, 07:16 AM   #37
cage4000
Newly Registered User
 
Join Date: Oct 2015
Posts: 48
Thanks: 16
Thanked 0 Times in 0 Posts
cage4000 is on a distinguished road
Re: Closing a Recordset from another sub

Thank you for both the insight and clarification of what my database is doing behind the scenes. This fixed my issue and I am able to move forward. Being self-taught, I find these insights extremely valuable and I will not forget them. In addition, on my next project I will follow all your advice on building a DSN-less connection so that my architecture is not as messy and clunky. Thank you both again for your time and for effort on this issue.


cage4000 is offline   Reply With Quote
Reply

Tags
recordset

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
closing this recordset icemonster Modules & VBA 4 04-07-2012 08:29 AM
Closing two Recordset issue Thinh Modules & VBA 23 12-29-2006 12:13 PM
Unwanted additional recordset after closing a form bokib Forms 4 11-15-2006 05:10 AM
Opening and closing the recordset, is there an easier way??? King_kamikaze Modules & VBA 6 02-10-2006 05:35 AM
Keep recordset open from Db start-up until Db closing jayke Modules & VBA 2 03-09-2005 07:17 PM




All times are GMT -8. The time now is 03:30 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World