Close, exits out of Application

[Adrianna]

I have a simple form where the user enters the Department which selects a list of account on file. The user enters the justification for archiving the selected file then uses a control button.

The control button runs an SQL statement that appends the record to the ArchiveTbl and Deletes the record from the Mastertbl. A message box desplays upon completion letting the user know the verything has been completed and then.....

DoCmd.Close acForm, "ArchiveJusfityFrm"

..... well...that works...it does close the form, but for some reason....it closes all the way out of Access.


I'm using Access 2000. There is no other code on this form (other then a cancel option). Nothing follows the DoCmd.Close except End Sub.

Anyone have this happen to them?

How do I fix it?

 

[pono1]

Adrianna,

Relatively wild guess: Are there subforms involved or do you have any controls that have the sometimes-deadly DLOOKUP function as their control source?

Regards,
Tim

 

[jimbrooking]

Adrianna,

Have you tried the simpler expression

DoCmd.Close

?

Jim

 

[Rakier]

I had a similar problem. I would open a form from another form in order to edit information. Then when I closed the first form with the DoCmd.Close statement it would close the second form and then also close the first form. It turned out that I had a DoCmd.close statement on the first form in the code that ran on the first form after the second form closed. Could this be your problem too?

 

[Adrianna]

I've tried DoCmd.Close and that closes the application as well.

There is nothing fancy. No DLookUp, nothing.

I'm closing a Justification Form that lunachs from a Macro on the Archive form. But it is not set up as a Subform...the Macro opens the second form so that users can enter a justification...then either quit without saving (in which the DoCmd.Close functions properly) or Archive (which runs that SQL statements and the DoCmd.Close does closes the application completely).

So...where to go from here?

 

[Rich]

Do you have any error handling after deleting the record from the table?

 

[pono1]

Does the Append record operation function correctly? Does the Delete record operation function correctly? That is, when you go back to the app after it has crashed, is the rec properly appended to one table and deleted from another table? And when you first open the Justification form, is the Archive form still open behind it? And does the Archive form have a list box (or combo box) that refers to one of the tables that is losing or gaining a record?

If the answers to all of these questions are "Yes," then you might want to experiment by opening your Justification form alone (no other forms open) and running your SQL and close command. If it works under these conditions, then you can conclude that your two forms shouldn't be left open at the same time, which means you might consider changing your code so that the Archive form closes as you open the Justification form.

Otherwise, if the above doesn't apply, you can test further by taking out the CLose command after the SQL. Just put it on a temporary command button and see if that makes a difference -- if so, that says something, though I'm not sure what at the moment.

Tim

 

[jimbrooking]

Adrianna,

You could copy 'n' paste the code to the forum so we could take a look...?

Jim

 

[Adrianna]

ARCHIVEOPTIONSFrm
________________________________
Private Sub ArchiveRecord_Click()
'Opens the form to prompt users to enter a justification for the archive
DoCmd.OpenForm "JUSTIFYFrm", acNormal
End Sub


JUSTIFYFrm
_________________________________
Private Sub NoSave_and_Close_Click()
Me.ArchiveJustify = ""
DoCmd.Save
DoCmd.Close
End Sub

Private Sub Save_and_Close_Click()
If IsNull(Me.ArchiveJustify) Then
MsgBox "You must provide a justification for removing or archiving the selected systems from the database"
Else
MsgBox "You are about to archive a system record. Please verify that you have entered the correct MSC and Identifier information into the fields above. In order to access this file at a later date, you will need to RECOVER the record through the ARCHIVEOPTIONSFrm."
DoCmd.RunSQL "INSERT INTO [Master Systems - Archive] SELECT [MastSysTbl].* FROM [MastSysTbl]WHERE ((([MastSysTbl].MSC)=[Forms]![JUSTIFYFrm]![MSC]) AND (([MastSysTbl].Identifier)=[Forms]![JUSTIFYFrm]![Identifier]));"
DoCmd.RunSQL "DELETE [MastSysTbl].* FROM [MastSysTbl]WHERE ((([MastSysTbl].MSC)=[Forms]![JUSTIFYFrm]![MSC]) AND (([MastSysTbl].Identifier)=[Forms]![JUSTIFYFrm]![Identifier]));"
MsgBox "You have successfully archived " & Forms![ARCHIVEOPTIONSFrm]![AMCID2Delete] & " for " & (Forms![ARCHIVEOPTIONSFrm]![MSC2Delete]) & "."
DoCmd.Close acForm, "JUSTIFYFrm" <--this is IT
End If
End Sub

 

[Adrianna]

I posted the code..but I don't think any of you have seen it. I'm surprised how quickly it was bumped to the next page. I'm hoping that someone can help. This version will be released next week.

 

[Rich]

Just a wild guess, but I suspect Access is crashing because you've deleted a record while the form is still open, try
Forms!JUSTIFYFrm.Requery before the DoCmd.Close

 

[jimbrooking]

Sorry I have been out of it as well - daughter & granddaughter visiting.

I did notice you set a value to "" (empty string) and test the field against Null somewhere. Don't know if that might be causing SOME problem, maybe not THE problem....

I will try to look at this later but grandkid has priority, I'm afraid.

Jim

 

[jimbrooking]

Adrianna-

Looking at the code, and considering the other comments, I'm afraid I don't have any revelations to offer. A couple of things I looked at that didn't pan out were

-The name of your main table, MastSysTbl looked something like an Access system table, but the Access system tables in my copy are named MSys*, and there's no MSysTbl anyway.

-The name of one of your field names, Identifier, looked like it might be an Access reserved word, but when I checked
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q109312&
it didn't mention Identifier.

I also considered whether perhaps one or the other of your queries might have failed catastrophically and closed the database, but I would suppose some error message or other would have been shown to let you know there was a problem.

My guess is that you have hit on one of the many little bizzaro problems in Access that is solved by naming, spacing, or parentheses (more or fewer).

You might create a brand new database and import into it the bare minimum tables and forms in your problem and see if the problem still exists. If it goes away, import more of your old database until the problem recurs, or until you've imported everything and have a new database that doesn't exhibit the poblem. (Stranger things have happened!)

If the minimum import shows the problem, try changing table names (I'd start with the table named [Master Systems - Archive] because of the "-"), field names, and names of text- or combo-boxes on the form to see if one of these is the problem.

Incidentally, with regard to naming, consider adopting the commonest MS Access naming convention (Leszynski/Reddick Guidelines) for your database objects. See
http://www.mvps.org/access/general/gen0012.htm
for more information.

Sorry I have no quick fix. Hope you are able to find one somewhere.


Jim