Re: bug? Repeated OpenForm / SizeToFit operations


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.




"rictmas" wrote:

> 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!)
>
>
>
> "Ron Weiner" wrote:
>
> > 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
> >
> > "rictmas" <rictmas@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:D03D1428-07D4-4594-BF00-6914B7363CFB@xxxxxxxxxxxxxxxx
> > > "rictmas" wrote:
> > >
> > > > 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?
> > >
> > >
> >
> >
> >
.

Relevant Pages

  • Re: bug? Repeated OpenForm / SizeToFit operations
    ... When starting access and loading your test database then executing the code ... code also did not produce the warning and again the loop ran to completion. ... loop, and the SizeToFit fails. ... Dismiss this second dialog the table opens. ...
    (microsoft.public.access.formscoding)
  • Re: bug? Repeated OpenForm / SizeToFit operations
    ... closing the database and attempting to ... Microsoft Access was unable to create the Database window. ... I have to admit, neither the "undo" warning you got, nor my Internet ... > Dismiss this second dialog the table opens. ...
    (microsoft.public.access.formscoding)
Loading