Close Access DB when last form closes?

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

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!

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.

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.

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

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

Fefo - that is my solution

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.

My post 06/28/2006 05:04PM PDT does work however I agree yours does too

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

er, the db window is hidden, but the main access window is still there.

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

Did you get your problem worked out?

Not really, I couldn't make much of that link...