A2003 Form_Unload problem in MDE

[james]

Hello,
I am trying to convert an application that ran fine in A97 & A2K to
A2K3. The app runs as an MDE. Mty problem is that when running as an
MDE in A2K3 the Form_Unload code does not appear to execute. In a
nutshell: the code determines whether user entered data sums to exactly
100%, if not it display a MsgBox indicating the error and sets
Cancel=True to stop the unloading of the form. Interestingly, if I run
the code from the MDB I see exactly the behaviour I expect and it works
exactly as it did in the previous versions.

This testing is being done on a WinXp SP2 machine if that makes any
difference. The only other pieces of info I can add are that when the
app starts I:

1. DO NOT block unsafe expressions

2. even though I have said I don't want to block them I get the
Security Warning that they are not blocked and instructions regarding
what I have to do to block them. I answer 'Yes' I still want to open
the database

3. After my final warning that the database may not be safe I still
select 'Open' because that is what I clearly want to do....


Has anyone encountered such a problem and / or can anyone suggest where
to look and what might be wrong?

TIA,
James

 

[Allen Browne]

Post the code in the Form_Unload event.

Indicate whether the controls refered to in the code are bound to fields or
are calculated controls. There could be a timing issue where the calculated
controls are not being updated at the time the Form_Unload event executes.

 

[james]

Hi Allen,

I will post the code as you requested ... there is quite a bit going on
in there. As it turns out this problem may be more insidious than even
I first thought: this code works "on occassion" however, I cannot
determine what makes it work and what makes it fail. When it does not
work, the close button in the form can be depressed but the thing just
sits there and looks stupid. I can bring forward the main menu which
allows the user to Exit the application (and Access via
Application.quit) when this is invoked the msgbox indicating the error
condition comes forward (I'm not even sure if that happens each and
every time) but you have no chance to stop the application from ending.
I apologize for this description seeming so 'scatter-brained' but it is
difficult to get a handle on the exact sequence of events. I am headed
back to the location where the problem exists to do more testing; I
will try to identify precisely what works / when / etc. Thanks for your
interest!!!

James


Private Sub Form_Unload(Cancel As Integer)
Dim nSum As Single
Dim bOver As Boolean
Dim bNoCharge As Boolean
Dim sMsg As String
Dim rst As Recordset
Dim bHasMaterials As Boolean
Set rst = Me.Components_Subform.Form.RecordsetClone
If rst.RecordCount > 0 Then
rst.MoveFirst
bNoCharge = False
nSum = 0
bHasMaterials = False
Do While (rst.EOF = False)

If Nz(rst.Fields("IsInstructionOnly"), False) = False Then
bHasMaterials = True
nSum = nSum + Nz(rst.Fields("AmtOfCharge"), 0)
End If
rst.MoveNext
Loop
Cancel = False
If bHasMaterials = True Then
If Abs(nSum - 100) > 0.0001 Then
nSum = (100 - nSum)
bOver = (nSum < 0)
sMsg = "" & "The 'Sum of Charges' must equal 100." &
vbCrLf & _
"It is currently " & IIf(bOver = True, "over",
"under") & " by " & Format(Abs(nSum), "##,##0.0000") & "."
If bNoCharge = True Then
sMsg = sMsg & vbCrLf & "ALSO," & vbCrLf & _
"At lease one material item has been listed
with no Charge Amount specified. All material must have a non-zero
charge amount."
End If
Cancel = True
End If
End If
If Cancel = False Then
If bNoCharge = True Then
sMsg = "At lease one material item has been listed with
no Charge Amount specified. All material must have a non-zero charge
amount."
Cancel = True
End If
End If
If Cancel = True Then
MsgBox sMsg, vbOKOnly, "The following error(s) must be
corrected..."
Else
If IsMDB() = True Then
rst.MoveFirst
If RecalcHAP(Me.IProdID, rst.Fields("IFMID"), False,
False, True, False, True, False) = False Then
If MsgBox("The HAP calculated as a result of this
formulation exceeds the HAP values for this product. Do you want to
view the HAP and Calculated HAP now?", vbCritical + vbYesNo, "HAP
Error...") = vbYes Then
RecalcHAP Me.IProdID, rst.Fields("IFMID"),
False, False, True, True, True, False
End If
If MsgBox("The HAP calculated as a result of this
formulation exceeds the HAP values for this product. This formulation
is NOT valid as it stands. Do you want to correct it now?", vbCritical
+ vbYesNo, "HAP Error...") = vbYes Then
Cancel = True
End If
End If
End If
End If
End If

rst.Close
Set rst = Nothing

End Sub

 

[Allen Browne]

Okay, most of that makes good sense.

Without going over it in detail, I'm wondering if there may be conditions
where Cancel is set to True, but the MsgBox is not displayed. Might be worth
moving the display of the message box until the end of the proc, so it is
displayed in all cases, i.e. after the last Endif:

If Cancel Then
MsgBox sMsg
End If

 

[james]

Allen,
That is a good idea and certainly makes good programming sense. I will
take a look at it and see what I can do. Without going in to a lot of
detail: there are a number of different conditions that may cause
Cancel to become true and different interactions with the user based on
that fact so it may not be completely straightforward to do (that is to
say, it isn't as simple as one msgbox indicating failure and ending the
interaction).

Well,.. my attempts to nail down the exact sequence of events was a
failure: it is inconsistent about whether it will work as it should or
not work at all. The thing that disturbs me the most is that this is
code that has worked exactly as it should for over 2 years in both A97
and A2K but now fails in A2K3. The only reason this is being converted
is that ALL machines are being standardized to XP Pro SP2 and A2K3 SP1.
Actually, I have tested this on build 11.5614.5606 and 11.6355.6360
(sp1) of A2K3 with the same results.

I am wondering if this has something to do with Sandbox mode? I noticed
that the Sandbox mode registry key is set to 2 which should mean it is
not used for Access. Is that true? If the key is set to 2 should I
still be seeing any messages at all about unsafe code?

TIA,
James

 

[Allen Browne]

My understanding is the setting the reg key to 2 means that ms Access is not
in sandbox mode.

Not sure what else to suggest. JET 4 is different than the JET 3.5x used in
A97, and some events to fire differently, e.g. Current can fire multiple
times. Some variables can be dereferenced in Form_Close if the database is
closing, but that was true in the earlier versions as well.

You seem like you have a fair handle on what's happening, so hope you're
able to nail this quickly.

There's some general stuff on converting from A97 to the later versions at:
http://allenbrowne.com/ser-48.html
However, I don't think that much of that is relevant to this particular
issue.

 

[james]

Bummer! I was hoping you were going to have some 'magic bullet' for me!

Thanks for the references I will check them out.

Quick question: what were you getting at with your comment about "Some
variables can be dereferenced in Form_Close..."? I don't really
understand what you mean there.

Also, my code is in the Form_Unload because that is the only place I
stop the form from closing if necessary. The sequence of events is, as
I understand it, still Unload -> Deactivate -> Close. But now I am
really intrigued because the close of the database appears to fire the
Form_Unload code whereas it was not firing when I simply tried to close
the form .... mmmm ... what's that all about???

Here is another tidbit that I turned up that may trigger something in
your mind (it certainly doesn't in mine!!!): the aberrant behaviour
seems only to manifest itself if I attempt to ADD a record through this
form. In other words, if I only edit the existing records things seem
to behave as I expect them to but once I add a new record the
Form_Unload code does not seem to fire.

I will definitely let you know if I come up with an answer however, if
the repeated bashing of my head against the wall results in an
inadvertent suicide you may never hear from me again ..... (just
kidding, I usually ease up at the first sign of blood!)

I am amazed at how close I live to the bleeding edge of technology .. I
should try to get Microsoft to hire me as a tester: I can find more
ways to break their toys than they can possibly imagine!!!!

Thanks again,
James

 

[Allen Browne]

Sure, Form_Unload fires before Form_Close. What I was referring to was
module-level variables reset to their default by the time Form_Close fires.

Certainly worth adding:
Debug.Print "Form_Unload at " & Now()
to the *top* of the event, so you have a trace of when it did and did not
fire.

Access has always silently lost the data if an entry can't be saved. Could
the new record be missing a required field or something? Not a particularly
inspired thought, but presumably you're not topping yourself while the
discussion continues.

 

[james]

I see.

The debug.print is a good idea, I'll give it a whirl...

It is, of course, possible that it is missing a required field .. or
something ... oddly enough the new record appears in the database once
it is shut down and restarted. The Form_Unload is supposed to trap
errors like 'sum of components <> 100%' and when I reopen the database
I will see the new record and the sum > 100% for example.

Did I mention that the "microsoft Access has encountered a problem and
needs to close. We apologize for the inconvenience." message is
displayed when I have to close the database once the form with the
Form_close problem refuses to close normally? I keep sending off the
information to Microsoft but I have no idea if any of it is useful
information to them.

I'll keep you posted.....

Thanks again,
James

 

[Allen Browne]

No: you did not mention that Access is crashing.

If that is the case, then it is definitely worth persuing the idea that
there is a corruption in the database. Standard rebuild-blurb follows.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, and reference ambiguities are
resolved.

If it is still a problem, the next step would be to get Access to rebuild
the database for you. Follow the steps for the first symptom in this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

 

[james]

Remember this whole thing started with a problem in an MDE so I am
assuming that you mean that the corruption, if there is any, was in the
original MDB and has migrated its way, i.e., been compiled in to, the
MDE. With that in mind I will try all this on the MDB. There is a lot
there so it will take me a bit of time to do as I have other things to
tend to as well.

I am guessing that if I can get the MDB to work the MDE should follow
suit. I will definitely let you know how it goes.

Thanks so much for your time and input! More later.....
James

 

[james]

Allen,

Well, I finally got my basic problem resolved. To make a long story
short it turned out to be the use of 'form.recalc' for another open
form during the AfterInsert event. As I said before, all that was
working just fine in A97 and A2K but for some reason it just didn't
want to cooperate in A2K3. I ended up changing these to 'form.requery'
and resolved my basic problem. That is to say, the app is now
functional under A2K3 however there is still a slight problem with the
form that I am doing the recalc of: I can cause Access to abend
consistently if I simply use the Records, Refresh (or F9) on that form.
It has a subform and the main form displays some summary info from the
details listed in the subform; these are the only complications on the
form. I can't see why they would be causing this problem but the fact
remains I can abend Access at any time. I have gone through all manner
of gyrating to build new, import, compact, repair, etc and all of that
goes well but the problem remains. I doubt that I will be able to spend
much more time on it but if I get a brainstorm and figure it out I will
definitely let you know!
Thanks again for your interest and input!!
James

 

[Allen Browne]

James, thank you for posting your findings. That may help someone else who
is researching a similar issue in these groups.

I've not experienced what you describe, but it may depend on what events are
triggering other events. I did have one recently where I used the
AfterUpdate event of a form to reorder its records. The code worked
correctly unless the user tabbed out of the edited main form directly into
the subform. If that happened, my AfterUpdate code worked correctly, but
after it completed Access changed the main form record to a different one!!!
It also failed to fire the Enter event of the subform control when this
occurred. The workaround I ended up using was to explicitly save the main
form record in the AfterUpdate event of the control where the users could
specify the order of the records, and then all worked correctly again. That
problem was reproducable in A97 as well as A2003, but explicitly saving is
often useful, as it forces Access to clear all pending events before it goes
on to whatever else you need done.

There is another bug where A2003/A2002 will crash (shut down by Windows) if
the field named in LinkChildFields is not represented by a control in the
subform (i.e. if it is just an AccessField and not a control.) The other
issues that I am aware of are listed here:
Converting from Access 97 to 2000, 2002 or 2003
at:
http://allenbrowne.com/ser-48.html

All the best of your Accessing.

 

[james]

I am glad to help when I can although I'm not completely comfortable
that I fully understand what the problem was (even though I figured out
how to get around it).

I am always uncomfortable when I find a way to "break" a program like
Access consistently. I took a look at my form that "abends" Access when
a Refresh is attempted. I noticed that the linking field was not on the
subform however adding it did not solve the problem (thanks for the
insight and references though - they were very interesting!!!).

Just out of curiousity, when you say you resolved your problem by
saving the main form's record exactly what means did you use to do
that?

Thanks again,
James

 

[Allen Browne]

Setting the form's Dirty property to False is my preferred way to save the
the record, so the code reads:

Private Sub SortOrder_AfterUpdate()
On Error GoTo Err_Handler
'Purpose: Force the save to avoid a bug which sends this form to the wrong
record,
' and fails to fire the Enter event of the subform.

If Not IsNull(Me.SectionName) Then
Me.Dirty = False
End If

Exit_Handler:
Exit Sub

Err_Handler:
Call LogError(Err.Number, Err.Description, conMod &
".SortOrder_AfterUpdate")
Resume Exit_Handler
End Sub