Closing Issues with Subforms

[davidmo]

Hey Everyone:
I have a form that has 4 tabs with subforms on them.

In my form header I have a command button to close out the main form(used the wizard). My problem is when I push the button to close the form the program crashes and Access shuts down.

I believe it might be the subforms causing the problem because when I took them out the close button worked fine.

I am using Access 2002.

Is this a common problem? Is there a work around?

Thanks.

DMO

 

[kjv1611]

Are you sure you setup the button to exit the form as apposed to exiting Access? It sounds to me that you just chose the wrnog option, possibly.

To check, right-click on your "exit form" button, and choose "build event."

In the VBA window, find the listing that says "Private Sub YourExitButton_Click()"

In that procedure, you will see either:
"DoCmd.Quit" or "DoCmd.Close"

If it says "DoCmd.Quit", then you are telling Access to completely shut down/turn off. If it shows "DoCmd.Close", then apparently, it's some other problem.

If it is a different problem, and not the wrong choice from the wizard, then post the code listed in that sub-procedure, and we can take a look.

Forgive me if I was too basic in my explanation - I was unsure of your experience in VBA.

 

[davidmo]

kjv1611:
Here is the code.

Private Sub closeclientinfo_Click()
On Error GoTo Err_closeclientinfo_Click


DoCmd.Close

Exit_closeclientinfo_Click:
Exit Sub

Err_closeclientinfo_Click:
MsgBox Err.Description
Resume Exit_closeclientinfo_Click

End Sub

DMO

 

[kjv1611]

Well, that certainly looks correct. Strange.

Is ther any code in something like Form_Unload, or Form_Close?

 

[davidmo]

kjv1611:
I have code in Form_Close but this was happening before I even put the below code in. I usually get a detail report of what happened, do you want me to post that report?

thanks.

DMO

Private Sub Form_Close()
Dim strappendClient

strappendClient = "INSERT INTO tblGeneralList ( First_Name, Last_Name, Type, Full_Name )" & _
" SELECT tbl_Borrower_Personal_Information.Borrower_First_Name, tbl_Borrower_Personal_Information.Borrower_Last_Name," & _
" 'Client' AS Expr1, [Borrower_Last_Name] & ', '" & _
" & [Borrower_First_Name] AS Full_Name" & _
" FROM tbl_Borrower_Personal_Information"

'Turn off Warnings
DoCmd.SetWarnings False

DoCmd.RunSQL strappendClient

'Turn on Warnings
DoCmd.SetWarnings True

End Sub

 

[kjv1611]

Well, it wouldn't actually cause any problems per se, but you really should Dim your variable in your statement as a String, as a Variant of that would take far more memory than a Sring variable.

Dim strappendClient As String
strappendClient = "INSERT INTO....."
DoCmd.RunSQL StrappendClient

Another question would be do you have any code in Form_Close or Form_Unload of your Subforms?

 

[davidmo]

kjv1611:
Two of the subforms have Form_unload in their code to close a calendar form if it is still open.

Private Sub Form_Unload(Cancel As Integer)
'this is required in case user Closes Form with the
'Calendar still open. It also handles when the
'user closes the application with the Calendar still open

If Not mc Is Nothing Then
If mc.IsCalendar Then
Cancel = 1
Exit Sub
End If

Set mc = Nothing
End If

End Sub

 

[kjv1611]

Is mc a public variable? I'm assuming it's Dimmed as an public object variable.

Also, although I cannot say that it would fix any problems, you might could try changing your If statement to something like this:

  [highlight]If mc Is Nothing Then
  Else[/highlight]
    If mc.IsCalendar Then
      Cancel = 1
      Exit Sub
    End If

  End If

or possibly:

    If Not [highlight]([/highlight]mc Is Nothing[highlight])[/highlight]Then
        If mc.IsCalendar Then
            Cancel = 1
            Exit Sub
        End If
        
    Set mc = Nothing
    End If

That last one, I am not 100% sure it will make a difference, but it might.