MS Access closing a subforn form with a navigation form

I am trying to write vba code in MS Access 2016 to close a subform within a navigation form
Startrac98Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertFabrice LambertCommented:
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

0
Dale FyeOwner, Developing Solutions LLCCommented:
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
0
Startrac98Author 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"]
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PatHartmanCommented:
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
0
Startrac98Author Commented:
ARDetail actually ON the  form on the navigation form.
0
Dale FyeOwner, Developing Solutions LLCCommented:
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 = ""
0
Fabrice LambertFabrice LambertCommented:
Lots of confusion here.
Can you upload your database ?
0
PatHartmanCommented:
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.
0
Startrac98Author Commented:
ok, I figured it out, thanks for your help

DoCmd.Close acForm, "[Forms]![ARDetail]![NavigationSubform]", acSaveYes
0
PatHartmanCommented:
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.
0
Startrac98Author Commented:
Thank you for that bit of information; but to prevent the record from being saved can I use undo?
0
PatHartmanCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Startrac98Author Commented:
Pat, I do appreciate the information, Thanks
0
Startrac98Author Commented:
I want to Thank all of you for your advice and comments.  I will be back.
0
PatHartmanCommented:
You're welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.