bug? Repeated OpenForm / SizeToFit operations

[Guest]

Hi. Sorry if this post is a bit long…

I seem to have run into a bug in Access 2002, in the SizeToFit method
(important to the front end of the application I am developing). I initially
assumed the bug was in my application, but after I created a separate
stripped down mdb for testing purposes, I found I repeatedly get the results
described below. I found nothing relevant after extensive searching on the
net (unless of course I was asking the wrong question).

When SizeToFit is called 16,366 times (in my case) on a label control, the
following symptoms occur:

SizeToFit stops working immediately. If I try to open a form, table, query
or report manually from the database window, in any view, nothing happens.
Macros, modules and data access page design DO open and appear to behave
normally, but if I run code that attempts to open a form, error 3011 occurs
(...couldn't find the object ''…). Note: the object name in the error message
IS actually blank. Application files do not open, and new databases cannot be
created.

Everything’s fine once I quit Access and go back in.

I found that if 'Size > To Fit' is invoked repeatedly from the menubar using
SendKeys, no such problem occurred (this is of no use to me, but I thought it
may be worth noting).

I also found the same symptoms occurred with repeated OpenForm operations
(either form view or design view), after about 10,000 cycles. When the same
process was invoked using SendKeys instead, the number of successful cycles
actually halved to around 5,000. In these cases of course, because they were
inherently opening and closing a form, they terminated with the error
described above.

I did read somewhere that repeatedly opening and closing forms could cause
data corruption but apart from that, I have found nothing relating the the
above problem.

I have tested the problem on another machine, with the same results.
Admittedly the other machine in question is very similar to my own, so I
don’t know for sure that the problem isn’t somehow related to the setup we
have here…

Because the problem is quite predictable, I have been able to code a warning
message into my application, advising the user to restart the application
‘soon’ once it counts a certain number of SizeToFit operations, but this is
isn’t really satisfactory.

All the above problems are cumulative, i.e. interrupting the code does not
reset the ‘counter’, so to speak, and I haven’t found a way of doing so.

I have installed Office XP service pack 3, the latest Jet service pack, and
am running Windows 2000.

If anyone knows what is causing this and/or can suggest a solution or
workaround I would be very grateful. Thanks

 

[Guest]

Hi. Sorry if this post is a bit long…

I seem to have run into a bug in Access 2002, in the SizeToFit method
(important to the front end of the application I am developing). I initially
assumed the bug was in my application, but after I created a separate
stripped down mdb for testing purposes, I found I repeatedly get the results
described below. I found nothing relevant after extensive searching on the
net (unless of course I was asking the wrong question).

When SizeToFit is called 16,366 times (in my case) on a label control, the
following symptoms occur:

SizeToFit stops working immediately. If I try to open a form, table, query
or report manually from the database window, in any view, nothing happens.
Macros, modules and data access page design DO open and appear to behave
normally, but if I run code that attempts to open a form, error 3011 occurs
(...couldn't find the object ''…). Note: the object name in the error message
IS actually blank. Application files do not open, and new databases cannot be
created.

Everything’s fine once I quit Access and go back in.

I found that if 'Size > To Fit' is invoked repeatedly from the menubar using
SendKeys, no such problem occurred (this is of no use to me, but I thought it
may be worth noting).

I also found the same symptoms occurred with repeated OpenForm operations
(either form view or design view), after about 10,000 cycles. When the same
process was invoked using SendKeys instead, the number of successful cycles
actually halved to around 5,000. In these cases of course, because they were
inherently opening and closing a form, they terminated with the error
described above.

I did read somewhere that repeatedly opening and closing forms could cause
data corruption but apart from that, I have found nothing relating the the
above problem.

I have tested the problem on another machine, with the same results.
Admittedly the other machine in question is very similar to my own, so I
don’t know for sure that the problem isn’t somehow related to the setup we
have here…

Because the problem is quite predictable, I have been able to code a warning
message into my application, advising the user to restart the application
‘soon’ once it counts a certain number of SizeToFit operations, but this is
isn’t really satisfactory.

All the above problems are cumulative, i.e. interrupting the code does not
reset the ‘counter’, so to speak, and I haven’t found a way of doing so.

I have installed Office XP service pack 3, the latest Jet service pack, and
am running Windows 2000.

If anyone knows what is causing this and/or can suggest a solution or
workaround I would be very grateful. Thanks

I just thought I'd add my test code - create a new mdb file, add this and
run it
(if you want to)

Function TestSTF() '# Failure occured after 16,367 SizeToFit operations
Const maxCount = 1000000, fixedWidth = 4000
Dim itCount As Long, frm As Form, lab1 As Label, lab2 As Label
Set frm = CreateForm
Set lab1 = CreateControl(frm.Name, acLabel, acDetail, , , 500, 500, 500,
500)
Set lab2 = CreateControl(frm.Name, acLabel, acDetail, , , 500, 1500,
500, 500)
DoCmd.Restore
With frm
For itCount = 0 To maxCount - 1
lab1.Caption = itCount
lab1.Width = fixedWidth
lab1.SizeToFit
If lab1.Width <> fixedWidth Then lab2.Caption = itCount Else
Exit For
DoEvents
Next itCount
End With
If itCount = maxCount Then
MsgBox "OK!"
Else
MsgBox "Not OK!" & vbCr & "Now see if you can still open forms, etc."
End If
DoCmd.Close , , acSaveNo
End Function

It doesn't make any difference if a saved form is used instead.
(btw I'm leaving work now, back on Monday)

Do you get an 'OK', a 'not OK', or is there something I'm not doing right?

 

[Ron Weiner]

Ran your code in an Access 2K database and got different results:

When starting access and loading your test database then executing the code
you provided I would get the following dialog after 9085 iterations.

You won't be able undo this operation.
Do you want to continue anyway?
Yes No

Selecting Yes it continued to run until the loop condition was satisfied.

However, after closing the form without saving and immediately running the
code again, does NOT produce the above message. The code executes until the
loop completes

Closing the database (NOT Access) and reopening it again, and running the
code also did not produce the warning and again the loop ran to completion.

So in Access 2K at least this warning message only appears to happen one
time per instance of Access.

I changed your code a little to speed it up by allowing a DoEvents only
every 1000 iterations. I changed this:
DoEvents
to this:
If itCount Mod 1000 = 0 Then DoEvents

Even so a million is a big number and it takes a while to get there on my
box.

When I selected NO at the warning dialog and then Immediately run the code
again I would get the warning dialog at the very first iteration of the
loop, and the SizeToFit fails.

I have no problem opening Forms and Queries, but get the same warning dialog
when trying to open an Table. If I answer Yes to the dialog when opening
the Table I get an "There isn't enough memory to perform this operation.
Close unneeded programs and try the operation again." Dialog. When is
Dismiss this second dialog the table opens. I close the table an then run
the code again it gives the warning dialog at a generally lower number
between 6 and 8000.

Sooo..... It looks like Access 2K has a slightly different version of the
bug to me. Anyone else wanna' chime in.

FYI I am running Access 2000 (9.0.6926 SP-3) according to the Help | About
Dialog.

Ron W

 

[Guest]

Thanks for looking into this

I adopted your amended DoEvents line - as you suggested it sped things up a
huge amount (a million was an arbitrary choice anyway, and I normally
interrupted the code before it got beyond 100,000).

Here’s a summary of my results, including a correction to my original
posting (*), and some new ones:

Repeated Operation Failure Occurred At
------------------------------------------------------------
Setting .Width and .Height properties (none)
SizeToFit 16,367
Size>To Fit using SendKeys (none)
OpenForm/close 10,059
Open/close a form using SendKeys 10,059 *
Open/close a table using SendKeys 5,690
Switch between form/design view (none)

After a failure occurs, closing the database (not Access) and attempting to
re-open it (or open any other mdb file) often results in an empty database
window and the following error, before the database window closes again:

Microsoft Access was unable to create the Database window.
Please upgrade to a newer version of Microsoft Internet Explorer.

However, sometimes there is no error message - the Database window either
disappears immediately or doesn’t visibly appear at all. In any case, nothing
opens until Access is restarted.

I am running Access 2002 (10.6501.6735, SP3)

and Internet Explorer version: 6.0.2800.1106CO
update versions: ; SP1; Q823353; Q833989;

I have to admit, neither the "undo" warning you got, nor my Internet
Explorer-related error are making a lot of sense to me at the moment. I
normally get the "undo" one if deleting a large number of records.

I found two hidden methods in the Object browser:
Application.SetUndoRecording(yes/no as integer) and
Application.BeginUndoable(hwnd). I tried them and they had no effect on the
results. I haven’t found anything that explains what they do. It seems in
Word there is ActiveDocument.UndoClear, but I can’t find an equivalent for
Access.

Any ideas of what might be going on or how it could be resolved would be
greatly appreciated.

Below is a modified version of my SizeToFit test code for anyone interested
to see how it behaves on their system / version of Access, or explain what’s
missing that would make it behave as expected:


Function TestSTF() '# Failure normally occured after 16,367 SizeToFit
operations
Const maxCount = 100000, fixedWidth = 4000
Dim itCount As Long, frm As Form, lab1 As Label, lab2 As Label
Set frm = CreateForm
Set lab1 = CreateControl(frm.Name, acLabel, acDetail, , , 500, 500, 500,
500)
Set lab2 = CreateControl(frm.Name, acLabel, acDetail, , , 500, 1500,
500, 500)
DoCmd.Restore
For itCount = 0 To maxCount - 1
lab1.Caption = itCount
lab1.Width = fixedWidth
lab1.SizeToFit
If lab1.Width <> fixedWidth Then lab2.Caption = itCount Else
Exit For
If itCount Mod 1000 = 0 Then DoEvents
Next itCount
If itCount = maxCount Then
MsgBox "OK!"
Else
MsgBox "Failed at " & itCount & "." & vbCr & "Now see if you can
still open forms, etc."
End If
DoCmd.Close , , acSaveNo
End Function

~

I've been having a look at using DrawText or DrawTextEx with DT_CALCREC
instead, but I haven’t quite got the hang of how it works.


Thanks again

Richard (Time zone: London - back tomorrow!)

 

[Guest]

I eventually used the GetTextExtentPoint32 API instead of SizeToFit.

I did look into passing the font name and size information to it, but it
seemed complicated so I’ve temporarily hard-coded a multiplier constant and
also the twips-per-pixel. It’s not massively accurate but it does the job for
now, and it doesn’t bring the whole application down.

The code I used is below. As implied above, I would recommend if you were to
use it, that you remove my twipsPP and szMult constants, and replace them
with API calls to properly determine the user’s screen settings and to
specify the font properties of the control being operated on.

The error handling may want to be improved too, although I’m not getting any
errors here.


Declarations and definition (located in a standard module):
-------------------------------------------------------------

Private Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long

Private Declare Function ReleaseDC Lib "user32" (ByVal hwnd As Long, ByVal
hDC As Long) As Long

Private Declare Function GetTextExtentPoint32 Lib "gdi32" Alias
"GetTextExtentPoint32A" _
(ByVal hDC As Long, ByVal lpStr As String, ByVal cbStr As Integer,
lpSize As SIZE) As Long

Private Type SIZE
cx As Long
cy As Long
End Type

Function tmpSizeToFit(ctrl As Control) ' Temporary replacement for built-in
SizeToFit method
Const TwipsPP = 15, szMult = 1 ' TwipsPP = twips per pixel, szMult =
size multiplier
Dim ctrlSize As SIZE, ctrlText As String, hDC As Long
With ctrl
hDC = GetDC(.Parent.hwnd)
ctrlText = .Caption
If GetTextExtentPoint32(hDC, ctrlText, Len(ctrlText), ctrlSize)
= 0 _
Then MsgBox "Error: " & Error(Err.LastDllError)
.Width = (ctrlSize.cx) * TwipsPP * szMult
.Height = (ctrlSize.cy) * TwipsPP * szMult
ReleaseDC .Parent.hwnd, hDC
End With
End Function



Example of usage:
-------------------

tmpSizeToFit Forms!Form1!label02


An additional advantage is that, unlike SizeToFit, the replacement function
isn’t limited to design view, so can be used live, with the ‘Me’ keyword,
etc..

I may still find it useful though, to be able to get the built-in SizeToFit
function to work without effectively crashing Access.

 

[Guest]

I thought I should add for clarity that the code I provided in the last post
is specific to label controls, but with slight modification (eg. change
..Caption to .Value), it should work with textboxes, etc.