Access Subform Shows Deleted Form

Here's a weird one:

I have a form that contains a dynamic subform - the subform is not
bound to any data, and simply contains a bunch of labels that I'm
creating dynamically using CreateControl.

Based on user interaction, I want to completely change this subform. I
set the SourceObject to empty (to unbind the subform from the Main
form), delete the subform, recreate the subform with new controls, and
rebind it to the main form:

formMain.subFormObject.SourceObject = ""
If FormExists("SubForm") Then
DoCmd.DeleteObject acForm, "SubForm"
End If
Set formSub = CreateForm
strSub = formSub.Name
'
' I add my custom controls to the form here
'
' Then I close and rename the subform, and rebind it:
DoCmd.Close acForm, strMultiBar, acSaveYes
DoCmd.Rename "SubForm", acForm, strSub
formMain.subFormObject.SourceObject = "SubForm"


90% of the time, this works just fine, and the subform is replaced with
new data correctly. Every so often, however, a real shocker occurs;
even though the Subform itself is deleted and recreated perfectly (you
can open it in design view to verify), the DISPLAY of the subform, when
it appears in the main form, will show a previous (deleted) version of
the subform.

From this point on, even if I physically drag the subform onto the main
form, it displays as it's previous incarnation.

I know what's going on - the database is looking at its garbage heap
for the form instead of looking at the live version. And indeed, doing
a "Compact And Repair" fixes the problem, but this seems a little
drastic (and it screws up my app in doing so).

Is there anything I can do to prevent and/or repair this odd behaviour
without actually repairing the entire database?

I wouldn't really describe this as weird. Access wasn't designed to
create and destroy forms on the fly and various problems arise if you
try.

Instead of creating a new form with new controls each time, set up a
single form with all the controls you're going to need, and then just
hide/unhide, move and size them in response to the user input. This is
likely to be more stable.

On 3 Jan 2007 17:06:49 -0800, wrote:

Here's a weird one:

I have a form that contains a dynamic subform - the subform is not
bound to any data, and simply contains a bunch of labels that I'm
creating dynamically using CreateControl.

Based on user interaction, I want to completely change this subform. I
set the SourceObject to empty (to unbind the subform from the Main
form), delete the subform, recreate the subform with new controls, and
rebind it to the main form:

formMain.subFormObject.SourceObject = ""
If FormExists("SubForm") Then
DoCmd.DeleteObject acForm, "SubForm"
End If
Set formSub = CreateForm
strSub = formSub.Name
'
' I add my custom controls to the form here
'
' Then I close and rename the subform, and rebind it:
DoCmd.Close acForm, strMultiBar, acSaveYes
DoCmd.Rename "SubForm", acForm, strSub
formMain.subFormObject.SourceObject = "SubForm"


90% of the time, this works just fine, and the subform is replaced with
new data correctly. Every so often, however, a real shocker occurs;
even though the Subform itself is deleted and recreated perfectly (you
can open it in design view to verify), the DISPLAY of the subform, when
it appears in the main form, will show a previous (deleted) version of
the subform.

From this point on, even if I physically drag the subform onto the main
form, it displays as it's previous incarnation.

I know what's going on - the database is looking at its garbage heap
for the form instead of looking at the live version. And indeed, doing
a "Compact And Repair" fixes the problem, but this seems a little
drastic (and it screws up my app in doing so).

Is there anything I can do to prevent and/or repair this odd behaviour
without actually repairing the entire database?

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

John Nurick wrote:
I wouldn't really describe this as weird. Access wasn't designed to
create and destroy forms on the fly and various problems arise if you
try.

cough That sounds a little like "it ain't a bug, it's a feature"
Call me a pessimist, but showing something that has supposedly been
deleted is just plain weird. If it'sot designed to do that, then why
have the command in there?

I agree with you that hiding and showing controls is normally the best
route, but in this particular case, hiding and showing existing
controls is not an option; there are too many possible permutations.

However, since the form deletion thing appears to be buggy (ahem), I
might instead look into destroying the controls themselves, leaving the
actual form intact... it takes a little longer but it might work.
Destroying the form was simply a quick way to nuke everything in one
swell foop.

On the more geeky side of things, I'm tempted to poke behind the
scenes, into Access's hidden system tables, and see if the problem
can't be fixed in there.

On 4 Jan 2007 02:21:15 -0800, "Snaux" wrote:


John Nurick wrote:
I wouldn't really describe this as weird. Access wasn't designed to
create and destroy forms on the fly and various problems arise if you
try.

cough That sounds a little like "it ain't a bug, it's a feature"
Call me a pessimist, but showing something that has supposedly been
deleted is just plain weird. If it'sot designed to do that, then why
have the command in there?

The commands are there to make it possible to create or destroy forms
and controls under program control, and they work fine when used to
create or customise forms while developing or installing a database.
But it's clear that they are not intended for continual use as part of
ordinary operation - if only because the problems that then occur
haven't been fixed in umpteen versions of Access.

I agree with you that hiding and showing controls is normally the best
route, but in this particular case, hiding and showing existing
controls is not an option; there are too many possible permutations.

However, since the form deletion thing appears to be buggy (ahem), I
might instead look into destroying the controls themselves, leaving the
actual form intact... it takes a little longer but it might work.
Destroying the form was simply a quick way to nuke everything in one
swell foop.

I fear that won't help: there's a limit of 754 controls *over the
lifetime of the form* (this is under "Access specifications" in Help).

On the more geeky side of things, I'm tempted to poke behind the
scenes, into Access's hidden system tables, and see if the problem
can't be fixed in there.

Have fun! But I suspect that whatever goes wrong is hidden deeper than
the system tables, and that if it was easy to fix it would have been
done about 1998. Also, remember thatt Access's built-in controls are not
standard Windows controls.

In your place I'd either change the user interface to work with controls
created at design time and then moved/hidden/revealed as needed - or
else develop the application using a tool that doesn't limit what you
can do with forms.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.