MS Access closing a subforn form with a navigation form

Startrac98
Startrac98 used Ask the Experts™
on
I am trying to write vba code in MS Access 2016 to close a subform within a navigation form
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
Hi,

Not sure to understand why you want to do that.

Well, just set the subform's sourceObject property to an empty string.
Sample code (according subForm is the name of the sub form control):
subForm.sourceObject = vbNullString

Open in new window

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Well, the Navigation form (I really hate these) has a subform control, usually named "NavigationSubform".

When you click on a tab in the Navigation form (design view) and display the properties (data tab), you will see the name of the form that is used as the SourceObject (although it is called the "Navigation Target Name".

I'm not sure if that helps

Author

Commented:
Sorry, about my typos.
MS Access closing a subforn form with a navigation form s/b
========================================================================================
I am looking for syntax to close a subform that is within a Navigation form.

The Navigation Form name is ARMain;  the subform name is  ARDetail

This will close the form if I open the subform by itself and it works ok.
DoCmd.Close acForm, Forms![ARMain]!["ARDetail"]
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Distinguished Expert 2017

Commented:
I guess I don't understand the point.  Every time you click on a new tab, Access unloads the current subform and loads a new one.  Do you really want to stay on this form and close the subform or are you just trying to be neat?

Is ARDetail actually ON the navigation form or is it on a form on the navigation form?  If it is on a form that is on the form, you are missing a level of parentage in your close expression.

Otherwise, you are referencing the current form from "outside" and might have better success if you simply used
DoCmd.Close acForm, Me

Author

Commented:
ARDetail actually ON the  form on the navigation form.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Do you want to keep the navigation form open?

Would you like to simply change the focus to another tab in the navigation form, or do you want to continue to display the tab and simply hide of close the subform?

The subform on the navigation form [ARDetail] will display the form associated with whichever tab is selected on the navigation form.

If you put a button in the navigation forms header, you might try something like:

me.ArDetail.NavigationTargetName = ""
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
Lots of confusion here.
Can you upload your database ?
Distinguished Expert 2017

Commented:
ARDetail actually ON the  form on the navigation form.
Then you are missing a level in the reference hierarchy.

When you use Forms!  to reference a form/subform/control, you MUST include the COMPLETE hierarchy.  When you use Me.   You are running code inside some form/report that is refering to an object on THAT form/report or subform/subreport and so you only include the hierarchy from this form down (usually you would only be referencing one level down.

The navigation form is itself a form so any form you place on it becomes a subform.  If that subform has a subform, then it is a subsubform.  I think current versions of access support 5 levels (but check).  Older versions only supported 3.

Author

Commented:
ok, I figured it out, thanks for your help

DoCmd.Close acForm, "[Forms]![ARDetail]![NavigationSubform]", acSaveYes
Distinguished Expert 2017

Commented:
You do realize that acSaveYes is referring to the form object and not the data, correct?  Typically you would never want to save user changes to a form so it would be better to use the option that says save no prompt (look up the syntax).   Access always automatically saves data when you close the form unless you write code to prevent it.

Author

Commented:
Thank you for that bit of information; but to prevent the record from being saved can I use undo?
Distinguished Expert 2017
Commented:
Your original question has morphed into something else.  It had nothing to do with saving and you said you had the solution.  I only pointed out the bit about saving because many people don't understand the difference between saving design changes to the form and saving modified data.

To have absolute control over whether or not a record gets saved requires code in the form's BeforeUpdate event.

You can ask the user if he wants to save and if he says no, you can undo the modifications.  You should also already have validation code in this event to prevent the record from being saved if there are any errors.

Typically the code could be:
Dim MsgResp as Long
MsgResp = Msgbox("Press Yes to save the record,   Press Cancel to cancel the save and return to the form to continue editing.  Press No to undo all your changes.", vbYesNoCancel)
Select Case MsgResp
    Case vbYes
    Case vbNo
        Cancel = True
        Exit Sub
    Case vbCancel
        Cancel = True
        Me.Undo
        Exit Sub
End Select

...... other validation code

Open in new window

Author

Commented:
Pat, I do appreciate the information, Thanks

Author

Commented:
I want to Thank all of you for your advice and comments.  I will be back.
Distinguished Expert 2017

Commented:
You're welcome.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial