Closing all forms in Access

I am updating one of my apps and I want to make one process very simple.

I have many forms.  And they don't all open at once.  But I am so tired of typing the same code for closing the form.  Since they all do the same thing.

I want to be able to write a function that I can refer to in the form for closing that form and opening the main form.

This is what all my close buttons code looks like

DoCmd.Close
DoCmd.Openform "frm-MainSwitchboard", acNormal.

I type this so many times and if the main form changes I have to go back in and update all those modules.

I just want one function that does the same except it is generic and will close the opened form that has the focus..  I do have three forms that open but are hidden.  They need to stay that way.

Any thoughts?
John
John SheehySecurity AnalystAsked:
Who is Participating?
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.

Rey Obrero (Capricorn1)Commented:
place this code in a regular module

Sub CloseForm(vForm As Form)
DoCmd.Close acForm, vForm.Name
DoCmd.OpenForm  "frm-MainSwitchboard", acNormal
End Sub

in the close button click event place this

CloseForm Me
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
Dale FyeCommented:
Well, you could create a public subroutine:
Public Sub CloseCurrentForm(frm as form)

    docmd.close frm.name
    is currentproject.AllForms("frm-MainSwitchboard").isloaded then
        forms("frm-MainSwitchboard").visible = true
        forms("frm-MainSwitchboard").setfocus
    Else
        DoCmd.Openform "frm-MainSwitchboard", acNormal
    end if

End Sub

Open in new window

Then, the code behind your close buttons would be simply:

CloseCurrentForm me

Open in new window

0
PatHartmanCommented:
The following code is a few common routines I use in all my apps.  The last set of code shows how they are called.  Notice the (Me) argument.  That passes the calling form object to the common code so the code can reference whatever form I am working with.

Public Sub CommonClose(frm As Form)
On Error GoTo ErrProc
    If bForceClose = True Then
        Exit Sub        'do not run close code
    End If
    If frm.Dirty Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    If IsNull(frm.OpenArgs) Then
        DoCmd.OpenForm "Switchboard"
    Else
        DoCmd.OpenForm frm.OpenArgs
    End If
    
    bForceClose = False     'reset variable
ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case 2102   'bad open args form name
            Resume ExitProc
        Case 2455   'happens for A2007 when unbound form references the Dirty property
            Resume Next
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume ExitProc
    End Select
End Sub

Public Sub CommonReturn(frm As Form)
On Error GoTo ErrProc
    If frm.Dirty Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    DoCmd.Close acForm, frm.name
ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case 3709, 3021, 2501  'caused when save is cancelled
            Resume ExitProc
        Case 2455   'happens for A2007 when unbound form references the Dirty property
            Resume Next
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume ExitProc
    End Select
End Sub

Public Sub CommonSave(frm As Form)
On Error GoTo ErrProc
    If frm.Dirty Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case 3709, 3021, 2501, 3071  'caused when save is cancelled
            Resume ExitProc
        Case 2455   'happens for A2007 when unbound form references the Dirty property
            Resume Next
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume ExitProc
            Resume Next
    End Select
End Sub

Open in new window

Private Sub Form_Close()
    Call CommonClose(Me)
End Sub

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Nick67Commented:
Anything like that is easy enough to abstract out.
I'm not sure that you'll save a lot of keystrokes but in a code module you can have

Open in new window

Public Sub CustomClose(ObjectName As String, theObjectType As AcObjectType)
DoCmd.Close theObjectType, ObjectName, acSavePrompt
If Application.CurrentProject.AllForms("frm-MainSwitchboard").IsLoaded = True Then
    DoCmd.SelectObject acForm, "frm-MainSwitchboard"
    DoCmd.Restore
Else
    DoCmd.OpenForm "frm-MainSwitchboard", acNormal
End If

End Sub
You can then close any object by passing in the name and type
Call CustomClose(me.Name, acForm) for a form
or
Call CustomClose(me.Name, acReport) for a report
etc.
0
Dale FyeCommented:
The only real difference between Rey's code and mine is that I test to see whether "frm_MainSwitchboard" is already open, and possibly hidden.  if it is open, and hidden, the docmd.Openform method will still make it visible and give it the focus.  I don't know whether there is any benefit of doing it the way I recommended, but that is the way I have always done it.
0
Nick67Commented:
Like @Dale Fye, I check for isLoaded.
I am not into hiding objects, so I didn't check for that.
One of the arguments for DoCmd.Close is what to do with design changes.
The default is to prompt, but you can force save or discard as appropriate.
I don't pass the whole form object since the name is all that's required for a simple close

@PatHartman has a lot on the go in there.
You could, too.

You can put
Select Case ObjectName
    Case Form1
        'do specialized stuff for this form
    Case Form2
       'do specialized stuff for this one
    Case else
       'general routines
End Select


How many keystokes you save is an open question.
0
PatHartmanCommented:
I should point out that the common close code that I posted includes a test that checks if the form is dirty and forces the record to be saved BEFORE initiating the close action.  This is quite important because if you simply "close" a dirty form and there are errors, you really want Access to stay on the form so you can fix the errors or consciously cancel the update.  You want the close action to be cancelled and it won't be.  Access will give you a warning message regarding the error but it will discard the change and close the form anyway.  So, to get Access to behave as you want it to, you have to force the record to be saved and then close the form rather than letting the form close event force the save.
0
PatHartmanCommented:
Nick,
I have to disagree on putting specialized form coding in a common module.  Anything specific to a form should be done before calling the common code.
0
Nick67Commented:
I'd agree that I like to have functionality packaged tightly together so that I know where to look when things so south or need changing.  Code reuse is a fine idea -- right up until it becomes obfuscation.  There's no disagreement that you have to create maintainable code.

Let's say, in this case, that there are one of three different forms to return the focus to when any particular object is closed.  One can code that in every Close event, or do it through a Select Case in a generic handler like this one.

It becomes a question of when is the code really specialized, and when is it a cut-and-paste mosaic that should be rationalized by abstraction.
0
Dale FyeCommented:
In my personal CommonClose code I also check (similar to Pat) whether the form to be closed has an OpenArgs, and if so, use that rather then the "Switchboard" form to look for and either open or make visible.

I tend to agree with Nick, regarding testing for all of the form specific stuff before executing the common close stuff.  So, depending on how a particular form is being closed, I might have code in the Form_BeforeUpdate or in the cmd_Close_Click event which checks for dirty and checks for values in required fields and all of that.  Then, and only then, would I call my CommonClose code.
0
John SheehySecurity AnalystAuthor Commented:
@Dale.  Your second line comes up red for me.

is currentproject.AllForms("frm-MainSwitchboard").isloaded then

Is that how it is suppose to be?

in my module this is what I did:
DoCmd.Close frm.Name
    is currentproject.AllForms("frm-MainSwitchboard").isloaded then
        Forms("frm-MainSwitchboard").Visible = True
        Forms("frm-MainSwitchboard").SetFocus
    Else
        DoCmd.OpenForm "frm-MainSwitchboard", acNormal
    End If

End Sub

John
0
John SheehySecurity AnalystAuthor Commented:
@ Pat,

I copied your code into a new module.  I made the necessary changes but it doesn't close any forms out.  All it does is bring the main form into focus.

John
0
Rey Obrero (Capricorn1)Commented:
@John

did you try the first post?
0
John SheehySecurity AnalystAuthor Commented:
@Rey,

That did exactly what I needed. And not complicated at all.

Thank you,

John
0
Dale FyeCommented:
sorry, that should have read:

IF, not IS

IF currentproject.AllForms("frm-MainSwitchboard").isloaded then
0
John SheehySecurity AnalystAuthor Commented:
It was quick response and a very simple one.  I thought it was going to require a checks and balance solution and it turns out a few lines of code is all that was needed.  Rey did an awesome job.

John
0
Nick67Commented:
It can be as simple or as complex as you need to make it
Public Sub CustomClose(ObjectName As String, theObjectType As AcObjectType)
 DoCmd.Close theObjectType, ObjectName, acSavePrompt
 If Application.CurrentProject.AllForms("frm-MainSwitchboard").IsLoaded = True Then
     DoCmd.SelectObject acForm, "frm-MainSwitchboard"
     DoCmd.Restore
 Else
     DoCmd.OpenForm "frm-MainSwitchboard", acNormal
 End If
End Sub

Open in new window


This is about as simple as I'd want to make a generic closing/ toss focus routine
'pass in the object name and type
'close it
'see if the new target is loaded
'toss the focus there if so
'load it if not

Rey's stuff works, and he was posting it while I was composing my reply.
You are clearly using
DoCmd.OpenForm  "frm-MainSwitchboard", acNormal
on each an every call.
But that is not as efficient as bringing it back to focus if it was already open.  You are costing the user the cost of firing up the switchboard each time out, perhaps.
And likely sets the switchboard back to pristine, so your user loses the state that the switchboard was in.
Perhaps that is intentional and desired.
Or perhaps a side-effect of coding for simplicity.
0
PatHartmanCommented:
I would have to see your code to see what the problem might be.  Are you properly setting the form object?

I strongly advise you to take my advice on saving the record if it is dirty BEFORE attempting to close the form.  The best place to do that is the common code because then you don't have to remember to do it for EVERY form.  This is a KNOWN problem with Access forms.
0
John SheehySecurity AnalystAuthor Commented:
All the answers are great and I am in now way saying that I needed some simple.

The main form closes every time a user clicks on one of the buttons on the form.  
All I wanted was to be able to reopen the form when they close out of the other forms.  I could of easily of done it like I normally do on a close button.

DoCmd.Close
DoCmd.Openform "frm-MainSwithcboard", acNormal.

But typing that over and over gets insane.    I am trying to minimize the amount of redundant code I use.

My goal is to try and point to a single source of code when the user completes a task as easy as closing.  This app has a lot of forms.  But all the close buttons point back to the main form.  If they can all call the same code then I only need to change that code and not all the buttons.

Make sense?

John
0
John SheehySecurity AnalystAuthor Commented:
@Pat,

I think you actually answered a question I had about saving a record and tabbing to a new one a while back.
But all my data entry forms save if the form is dirty before closing as per your advice.  

John
0
Nick67Commented:
The main form closes every time a user clicks on one of the buttons on the form.
It doesn't have to be that way.
Certainly no switchboard I've ever built has worked that way.

What @Dale Fye was suggesting is an echo of how some folks go about ensure the user never sees and empty user interface.

What some folks do is put code in the close event of the switchboard.
If a form other than switchboard is open, the close event gets cancelled and the form's .Visible gets set to false.  If the switchboard is the only form open, then it closes and does Application.Quit.  That's why @Dale Fye's code was looking to do
Forms("frm-MainSwitchboard").Visible = True.
Some folks code the switchboard with a Timer() event that looks at how many forms are presently open.  If the answer is ever just one, the switchboard then makes itself visible and maximized.

With mine, I ask the user, if they try to close the primary form, did they intend to quit the app.  If they answer yes, everything closes.  If they answer no, the primary form remains open.

I am not sure why you have your switchboard open and close with every use.
Did you code it that way for a reason?
0
John SheehySecurity AnalystAuthor Commented:
I didn't code it that way on purpose....per say.  

Just my thinking is, if the form isn't needed why keep it open.  Open when needed closed when not.
This isn't an app that is stored on one computer.  We are talking several hundred users are on at any given time.  

The switchboard does other things than just show buttons.

It also displays statistical numbers.  And these numbers change almost hourly.
Instead of refreshing the switchboard every few seconds, it refreshes every time it is open.

My end goal, which I am still learning, is I want to have a display/logo screen and all the options are placed in a ribbon.
But for now this is working.

This thread has caused me to rethink the app and maybe abandon these small changes and make a whole new version.
I would need to get the customers list of wants and needs again since it has been 23 months since implemented but it is a doable project.

Thanks
John
0
Nick67Commented:
Just my thinking is, if the form isn't needed why keep it open.
Depending on what data is bound to the form and the back-end of the application, opening the form can be an expensive operation to both the front and back-ends.

Instead of refreshing the switchboard every few seconds, it refreshes every time it is open.
Forms have an Activate and Deactivate event.  If the refreshing is done by a Timer() event, you can set the Timer period to zero on Deactivate and a suitable value on Activate.  The hidden/minimized form is then doing nothing until the user brings it into focus.

all the options are placed in a ribbon.
Ribbon hacking is a doable, but very complex subject.  There are excellent paid tools for doing so.
Discussions touching on almost all aspects of ribbon hacking are found here
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28129836.html
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27231448.html 

Food for thought!

Nick67
0
John SheehySecurity AnalystAuthor Commented:
Nick,

I have  realized that the ribbon thing may be a bit out of my realm.

As much as I would love to learn how to create a custom one, it will have to be on my own time and not the customers.

Since my post a few minutes ago, the customer has agreed to an updated version.  They have stipulated that the tables and reports stay the same.  Everything else can change as deemed necessary .  They also mentioned new tables can be created and new reports can be added.  But the ones there can not change.

They are going to send a wish list later on today.  

So back to the white board, paper and pencil.  And leaving this site up on the second monitor.

What do you mean by an expensive operation to both front and back end?

John
0
Nick67Commented:
When you open a bound form, the front end is going to initialize a recordset, pull some data, marshall it, pull data for comboboxes and listboxes and other types of controls bound to individual datasources, draw the form, add the newly opened form to the Forms collection and get it ready to go.  On the back-end, all those data requests are going to be received, the data will have to be drawn from tables, sorted perhaps, and sent across the wire to the front-end.

Depending upon the complexity of the form, the types of controls on it, and the type and amount of data involved, this may not be a trivial exercise.  I certainly have forms that the end-user does not willing want to dismiss because they take 5-15 seconds to load.  Once they get some of these heavyweights initialized, they want to keep them open.

My users are not typers.  They are clickers.  So I have lots of comboboxes.  But those are heavyweight controls.  Each one is bound to a recordset.  Each one will make a request to the back-end.  When you start to optimize for performance and good user experience, you start to look at the queries behind each object.  Do they request the minimum amount of data needed?  Are they efficient queries?  Are all the fields where an index would be helpful actually indexed?  Is the combobox read-only?  Could it be populated by a better technique than a query, like a SQL Server View or stored procedure?  Could they actually be populated by a dummy value until they get the focus, and only then make the call for the actual data behind them?  Are they populated by static data that could be populated into local tables in the front-end instead of making calls to the back-end.

Getting Access to function as desired is a learning curve.  Getting Access to perform as well as possible for the end user -- well, that's an artform!
0
John SheehySecurity AnalystAuthor Commented:
I see what you are saying and I never thought of it that way before.  I have a lot of users from different locations. But they are all typers and clickers.  in the last version, I ended up placing a lot of common date in tables and using those tables for drill downs.  But I set that up in the table so when the form was created so was the combo boxes.  Saves time on design.

This is how I have the app setup.
I have the front/backend.  In my front end I do have the queries, forms, reports, modules.  But I also have some tables that are not linked.  I call them common tables.  Data in these tables never change unless corporate updates the data,  Which happens about once every 18 months or so.  This is where about 90% of all the combo boxes retrieve their data.  I figured this would save on data retrieval.   But some queries can be cumbersome.  Such as one query pulls as many as 3000 records at a time while another may only pull 150 records.  

But now I see why you said expensive.  I will take that into consideration as I move forward with the redesign.  I am sure with in the next week I will be posting a lot more questions.

John
0
Nick67Commented:
This is where about 90% of all the combo boxes retrieve their data.  I figured this would save on data retrieval.  
This is part of the art form, and the gotchas.
Local tables for static data can definitely be a time-and-bandwidth saver, just so long as you don't mix them in queries with linked tables.  Sometimes Access will then get it in its head that it needs to pull ALL that remote data to do the filtering and sorting at the front-end -- which is what you want to avoid.

Turn off SubDatasheets in tables!  Access will occasional pull stupid amounts of data because of them.  This is a global setting and a definite performance improver.  If you are starting near-scratch, then get those turned off in the beginning.  Saves scutwork later.

Good Luck!

Nick67
0
PatHartmanCommented:
But I set that up in the table so when the form was created so was the combo boxes.  Saves time on design.
But you usually create only a single form for each table.  You create many queries and potentially lots of code.  All of which will be laced with gotchas.  Lookups DO NOT belong on tables.  Use them only on forms.  When you open your table in datasheet view, you want to see the actual value of the field NOT the lookup field.  Say you have a department table:
DeptID, DeptName
1, Purchasing
2, Human Resources
3, IT
4, Sales

So your Employee table looks like this when you view it:
EmpID, EmpName, Department
1, Mary, Purchasing
2, Jim, Purchasing
3. Sam, Sales
4, Elvis, Human Resources
5, Tammie, IT
6, Joe, Sales

If you were to create a query that selects only employees in the Sales department would you look for employees where Department = 4 or Department = "Sales"?  Most people who use table level lookups haven't a clue.  What do you do in code?
0
Nick67Commented:
But I set that up in the table so when the form was created so was the combo boxes.  Saves time on design.

OH!
I didn't read that as creating lookup fields in the table.
I do now.

I completely agree with @PatHartman that those are grief just waiting to pounce.
I brutally confused myself on that very
for employees where Department = 4 or Department = "Sales"?  
Trying to build new queries, look in the table and it says "sales"
Great.  Put "sales" in as  parameter and promptly get 'data type mismatch'
WTF!?!  It says "sales"  Why doesn't that work!?!

I put up a big note to self: "***only permit tables to show what's actually there.  Don't gussie it up.  Only look at table data when you're in the weeds and you need to see what's reeeeeally there.  And then make sure that what a table shows is the base state of the data that exists.  Best way of avoiding being dans la merde***"

And given that I REALLY muck hard with the RowSource of most comboboxes (either they cascade, or they filter, or they don't load the data until OnGotFocus, or they're unbound and I do a Union Query to add an 'ALL' to the choices, etc.) They don't really save much development time.

And, usually I'll make the RowSource into a saved query anyway.  Chances are that the same data pull will be needed on both forms and reports.  Access (may still, but definitely used to) will optimize a saved query, but not bare SQL in a RowSource.
And if I ever want to see the contents of that query, I can open it at the same time as the object it comes from.  With a RowSource in Design View, the query viewing is modal, so you're trapped looking at the query until you close it.  That's been a PITA often enough that I avoid it by saving the query.

YMMV.

Nick67
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.