Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2009
    Posts
    15

    Unanswered: How to Close a subForm in a main form

    I have several subforms in a mainform. I use Visible to switching betwwen subform operation. On the subform itself, I can close using a programmed button, but it does not work once it is running in the mainform. I guess that because the subform cannot unload since it was build as residence on the mainform, but I don't know how to render it "Visible" after Add/edit operations is done. Can somebody shed some lights of work around or better ideas how to resolve this. Thanks

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    you can not make an object not visible if it has the focus. beyond that point it should work...double check your naming or syntax or something...but you should be able to be in the main form and make a subform not visible without a problem....

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Just remove the sourceobject property of the subform (in your main form code - not sure but I believe it also works from a subform.) You can then re-populate it as needed:

    me.MySubformName.sourceobject = ""
    me.MySubformName.sourceobject = "MySubformName"

    or from a subform:
    Forms!MyMainFormName!MySubformName.sourceobject = ""
    Forms!MyMainFormName!MySubformName.sourceobject = "MySubformName"
    Last edited by pkstormy; 11-17-09 at 23:03.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I would definitely use Visible to hide and unhide. The user will notice if you close and open a subform since the data needs to be regathered. And I agree with NTC, the subform probably still has the focus. You can move the focus using the SetFocus method of the main form.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by DCKunkle View Post
    I would definitely use Visible to hide and unhide. The user will notice if you close and open a subform since the data needs to be regathered. And I agree with NTC, the subform probably still has the focus. You can move the focus using the SetFocus method of the main form.
    An invisible subform (which has records in it), serves no purpose for the user other than tying up processing time behind the scenes. Hiding a subform still means it must process that subform. When you have large recordsets, this becomes critical. You get much better performance when you 'eliminate' (not hide) subforms which are not used.

    There are rare occasions where I have hid a subform versus removing the sourceobject but cspectra's needs are not one of those rare occasions.

    If there are multiple subforms within a main form as cspectra mentioned, that's several recordsets it needs to load! If there's no criteria in the subform's recordsource, that means even more processing. That's why it's also best to use criteria in the subform's so only the linked records are returned.

    I have to ask why you think the visible=true/false works better? Maybe there's something I'm overlooking in this situation.
    Last edited by pkstormy; 11-18-09 at 20:29.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Oct 2009
    Posts
    15
    I did use hide/unhide but I know they are running in the background.

    To Pkstormy
    Can you explain what actually happens when remove the sourceObject. I don't quite understand even after reading your advice and on VBA help.

    By the way, may I ask how can you set/change the code of the mainFrom variable value , not control box or property, when you are in a subForm code?

    Thanks

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Removing the sourceobject essentially removes the subform from the main form (and thus, removing any code, recordsets cycling, or anything else associated with the subform.)

    You can use the visible to hide/unhide the subform but as you mentioned, the form is still running in the background. This can have affect on any code you run which opens the subform's same recordset and updates it (if the subform is invisible). If the subform is not loaded, your code to update that same recordset is less problematic. I usually prefer to use the sourceobject to prevent problems/delays with having the subform loaded or other coding conflicts. There are pro's/con's to both techniques but again, to me, an invisible subform on a main form serves no real purpose other than to keep the recordset loaded so that if you do make it visible, there's no delay (which would be minimal) in re-loading it.

    To change a value on a main form (within a subform), you can reference the field on the mainform directly (ie. Forms!MyMainForm!SomeField = "Some Value")
    If you need to execute code on a MainForm, it get's a little trickier. You have to make sure the function or sub on the main form is declared as public and then referencing that function/sub procedure is called with something similar to:
    Forms!MyMainForm.MyProcedureName_AfterUpdate()

    But keep in mind that this is where you need to decide if perhaps that sub procedure should be created as a Function in a module. Creating a function in a module means you can call it from anywhere (ie. Call MyFunctionX). I will often create a function in a module (and that function has code to reference the form/subform directly). Then, if I need to re-use that function for another form, I could also pass the formname as a parameter to the function or in the function code, test to see what forms are loaded.
    Last edited by pkstormy; 11-20-09 at 12:15.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Oct 2009
    Posts
    15
    Thank pkstormy.
    I will try and let you know.

  9. #9
    Join Date
    Oct 2009
    Posts
    15
    It did not work for the subform; the error was Caanot find the "subform". I check and the subform name and syntax is exact what you told me.
    The other hints work well. Thanks

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I usually edit the name of the subform, copy it, and then paste it into the code. If you have spaces or other odd characters, you may need the [ and ]. It should work though (I've never had problems doing this) and the error you indicated seems to be the subform name is spelled wrong in the code or it can't parse the name for some reason (I assume you changed the "MySubformName" to your actual subform's name.)
    Last edited by pkstormy; 11-21-09 at 23:20.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •