Solved

Close Access DB when last form closes?

Posted on 2006-06-28
16
444 Views
Last Modified: 2008-02-01
Hi all,

I have an access database that has 4 forms in it. A user maybe in one, or all four forms at once. I'd like for the main access window to be hidden at all times, and when the last form is closed-- access quits!

I'll make this worth 500 points because I'm in quite the hurry.

TIA!,
qbn
0
Comment
Question by:qbn321
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 5
16 Comments
 
LVL 35

Expert Comment

by:Raynard7
ID: 17005119
Hi,

What I would do is create a global method and call it from the Private Sub Form_Close() passing only the name of the current form (Me.Name) to it.

This would then create a loop for each of the forms and check if they are open or not.  if no forms are open then call the close database method.

Dim f as form
dim chkBool as boolean

chkBool = false

for each f in Forms
    if f.Name <> ClosingFormName then
        if fIsLoaded(f.Name) then
            chkBool = false
        end if
    end if
next f

if chkBool = false then
    DoCmd.Close
end if

Function fIsLoaded(ByVal strFormName As String) As Boolean
    fIsLoaded = false
    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0 Then
        If Forms(strFormName).CurrentView <> 0 Then
            fIsLoaded = True
        End If
    End If
End Function
0
 

Author Comment

by:qbn321
ID: 17005577
Is a method similar to a module? And when you say "and call it from the Private Sub Form_Close()" where is that? Is that in the form settings under form design view?

Thanks!
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17005682
Hi,

a method is a sub ie Public Sub checkOpenForms(currentFormName as string) if this is global then you put it into a module.

Private Sub Form_Close() is created when you go to the form - go properties - then go events - then on close - click the ... and then select code.
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 

Author Comment

by:qbn321
ID: 17005882
You're confusing me further. Where do I put the above listed code? In a module? And then what? You gave me all this code but I don't know where to put it.

I don't see checkOpenForms anywhere in anything you listed.
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17005960
Create a new module.

Copy and paste the following code (Between the lines of ***) into that module

Save the module

*****************************************************
public sub checkOpenForms(currentFormName as string)
Dim f as form
dim chkBool as boolean

chkBool = false

for each f in Forms
    if f.Name <> ClosingFormName then
        if fIsLoaded(f.Name) then
            chkBool = true
        end if
    end if
next f

if chkBool = false then
    DoCmd.Close
end if

end sub

Function fIsLoaded(ByVal strFormName As String) As Boolean
    fIsLoaded = false
    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0 Then
        If Forms(strFormName).CurrentView <> 0 Then
            fIsLoaded = True
        End If
    End If
End Function
*****************************************************

Then create a Private Sub Form_Close() event on each form
add one line in that sub
call checkOpenForms(Me.Name)

This will check if all forms are closed and close if that is the case
0
 
LVL 7

Expert Comment

by:fefo_33065
ID: 17006071
qbn321,

To make the access window hidden at all times:
go to Tools > StartUp > uncheck the box "Display Database Window"

To close the Access database when you close all the forms:

1-  Right-click your form, go to design mode:
2-  click the Event tab
3-  click the "..." button in the OnClose event
4-  Select Code Builder in the "Choose builder" dialog box, click OK

The Visual BAsic editor opens and the cursor will be in the Form_Close event ready for you to type your instructions

Paste the following code:

                                      CloseForm Me.Name

5- Do this for the other three forms.


Now, go to the database window, click on "Modules", then "New"

Paste the following code:

'===========================================begin code
Public Sub CloseForm(ByVal strFName As String)
Dim f As Form
Dim I As Integer

I = 0

For Each f In Forms
    If f.Name <> strFName Then
        If IsLoaded(f.Name) = True Then
            I = 1
        End If
    End If
Next f
If I = 1 Then
'    DoCmd.Close acForm, strFName
Else
    DoCmd.Quit
End If
End Sub
Function IsLoaded(ByVal strFormName As String) As Boolean
 ' Returns True if the specified form is open in Form view or Datasheet view.
   
    Const conObjStateClosed = 0
    Const conDesignView = 0
   
    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
        If Forms(strFormName).CurrentView <> conDesignView Then
            IsLoaded = True
        End If
    End If
   
End Function

'===========================================End of code

Re-open the database, and test, test, test

hope this helps,
Fefo
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17006098
Fefo - that is my solution
0
 
LVL 7

Expert Comment

by:fefo_33065
ID: 17006243
Sorry Raynard my goal is not to take your points but to help in this issue.

The IsLoaded function is a solution provided by Microsoft and the CloseForm sub is mine, based upon a modification to your first post which has no sub name nor does work since you use a variable (ClosingFormName) that is not declared anywhere. If you run either of your codes you will cancel the close event of the form, causing an error to occur.
Also, qbn321 requested to hide the main database window during use of the database which I provided an answer.
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17006277
My post 06/28/2006 05:04PM PDT does work however I agree yours does too
0
 

Author Comment

by:qbn321
ID: 17011824
@Raynard7: I tired your solution first, and it did not work.

@fefo_33065: I tried your solution next, and it works great! The only remaining issue is that the main access window still shows in the background. I was hoping to hide that totally. My forms are indeed set to popup and modal.
0
 

Author Comment

by:qbn321
ID: 17011834
er, the db window is hidden, but the main access window is still there.
0
 
LVL 7

Expert Comment

by:fefo_33065
ID: 17012233
hmm, you are asking for something that requires more complex and advaced programming, but it is doable of course.
Take a look at this article:

http://www.mvps.org/access/api/api0019.htm

0
 
LVL 7

Expert Comment

by:fefo_33065
ID: 17072943
Did you get your problem worked out?
0
 

Author Comment

by:qbn321
ID: 17074026
Not really, I couldn't make much of that link...
0
 
LVL 7

Accepted Solution

by:
fefo_33065 earned 500 total points
ID: 17113350
Well, there is the fact that the form will always run WITHIN THE APPLICATION WINDOW so there is no way to have the form show but the application window hide in the background. I guess one way not to allow users to access any of the menus in the application window, is maybe to set the POPUP & MODAL properties of the forms to Yes.
0

Featured Post

 Watch the Recording: Learning MySQL 5.7

MySQL 5.7 has a lot of new features. If you've dabbled with an older version of MySQL, it is definitely worth learning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question