source object on change - sometimes not

[johnlute]

Access 2003/Win 2000

I have a form with a tab control and subform. The tab control has an
On Change event to change the source object of the subform. The event
is also in the main form's current event.

This is a new design for me and it's been working fine except that
every so often the subform doesn't want to behave. It either won't
change to the proper source object or it will be invisible.

Here's the on change event:

Private Sub TabCtl_Change()
If Me.Dirty Then Me.Dirty = False
Select Case Me.TabCtl
Case 0
Me.sfrmCtl.Visible = False
Case 1
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmProfilesCodes"
Case 2
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmProfilesApprovals"
Case 3
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmFGPhysicalAttributes"
Case 4
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmPKProfilesQualifications"
Case 5
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmProfilesShipStorage"
Case 6
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject =
"sfrmProfilesLocationIDsSupplierIDs"
Case 7
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmPKProfilesAssociationsFG"
Case 8
Me.sfrmCtl.Visible = True
Me.sfrmCtl.SourceObject = "sfrmProfilesAttachments"
End Select

End Sub

Is there something that I've done wrong or is this a common problem
with this kind of design?

Thanks!!!

 

[Jeanette Cunningham]

The code looks fine. I haven't come across this problem, and I have used
this type of code quite a lot.

Sometimes you also need code to set the Link master and link child fields to
the correct fields - this could be an issue with your form.
This would be needed if the link master and child fields are not the same
for all the subforms.

Otherwise look for a problem with a particular subform, maybe there is a
problem with saving the data on a particular subform that is causing the
problem.

Also check for correct spelling for each subform in the code you posted.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[johnlute]

Hi, Jeanette.

Thanks for the reply.

The code looks fine. I haven't come across this problem, and I have used
this type of code quite a lot.

I've been wanting to do this for a long time and it's VERY helpful in
speeding load times and eliminating that ANNOYING "Can't open anymore
databases." error.

Sometimes you also need code to set the Link master and link child fields to
the correct fields - this could be an issue with your form.
This would be needed if the link master and child fields are not the same
for all the subforms.

Yes. In this instance, sfrmCtl has the Master and Child fields
assigned in its properties box because they are indeed all the same
for all subforms. I have another form where I've had to use code to
set the Master and Child fields. That particular form hasn't given me
any grief.

Otherwise look for a problem with a particular subform, maybe there is a
problem with saving the data on a particular subform that is causing the
problem.
Check.

Also check for correct spelling for each subform in the code you posted.

Check.

I just went and opened/closed the form again several times trying to
get it to misbehave. Nope. It figures. Just like a car - it shimmies
and shakes when you drive it around but then when the mechanic checks
it out then it's fine.

I don't think the problem is resolved for the simple fact that I've
not changed anything. It's just very peculiar that it's so
intermittent.

 

[Jeanette Cunningham]

This suggests that the fact of putting the subforms in the tab control is
not part of the problem.
I would look for problems with the individual subforms.

A long shot is that the form with the tab control has become corrupted.
You could try building a new form with a new tab control to see if the
problems still occur.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[johnlute]

Thanks, Jeanette. I'll check into those...I'm afraid it's going to be
like a needle in a haystack.

 

[Jeanette Cunningham]

Perhaps some error handling msgboxes for the OnChange event of the tab
control would help.
When one of those subforms refuses to cooperate, you could check the
immediate window to see what error it was throwing.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



Thanks, Jeanette. I'll check into those...I'm afraid it's going to be
like a needle in a haystack.

 

[johnlute]

Error handling is a new frontier for me. I'm not too sharp about the
topic but I can see what you're saying. Maybe it's time I venture into
that frontier.

 

[Jeanette Cunningham]

Have you tried debugging?
Put a break point on the tab control's on change event.
Back in form view, do whatever would normally happen to change the source
object of the tabcontrol.
Once the code hits the break point, the code window will open and a yellow
line will hilight the current line of code.
Push the F8 key to run the next line of code, then again for the next, . . .
When there is an error, the code will stop and you can see the error
message.

This will help you to find the problem with this form and tab control.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Error handling is a new frontier for me. I'm not too sharp about the
topic but I can see what you're saying. Maybe it's time I venture into
that frontier.

 

[johnlute]

Hi, Jeanette.

I've tinkered with this and maybe I'm doing it wrong but nothing
happens. Nothing is caught during compile, either.

The tab control has been behaving today except one time. Very strange!

 

[Jeanette Cunningham]

So what error message was there in the immediate window that one time when
the tab control acted strange?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Hi, Jeanette.

I've tinkered with this and maybe I'm doing it wrong but nothing
happens. Nothing is caught during compile, either.

The tab control has been behaving today except one time. Very strange!

 

[johnlute]

Hi, Jeanette.

Sorry for the confusion. It wouldn't act up so I removed the code. I'm
not so sure I had it in correctly, either. Shortly thereafter, it
acted up - it just doesn't always change as I noted above and the tab
page is blank - or perhaps the subform is invisible.

 

[johnlute]

Hi, Jeanette.

I found what I think is the problem. I had some code in the OnCurrent
Event that was needed in a previous incarnation of the form - I also
thought it was needed in this present design however it's not and when
I commented it out the problem seems to have gone away, too.

Thanks for all your help!