How to find the name of the present subform

[Access infant]

Hi All,
I have a problem with finding the name of the present subform in the main
form. Before coming to the problem,let me give a background of the design
Actually every first level subform is unbound and they hold one or two
second level subforms which are bound to tables and are in one- to- many
relation with the mainform. And every level1 subform has two navigation
buttons ".Next > ", "< Back ". the code in the click event of these buttons
see what form follows next. The code is as follows.
Me.Parent.sfrmholder.SourceObject = "sfrm to come next" etc...

Now i want the second level forms to display records related to the record
in the main form. the code is i used in the current event of the main form is:
Me!sfrmholder.Form!sfrm11.Form.Filter = "EmpSid = " & Me.txtEmpSId
Me!sfrmholder.Form!sfrm11.Form.FilterOn = True
Me!sfrmholder.Form!sfrm11.Form.Requery
it works perfectly well if i display only one subform. but the subforms
change one by one as in a control wizard in access. so, my idea is to find
the name of the subform in a select case statement and depending of the
present subform i want to take action accordingly. it is something like
Private Sub Form_Current()
Select Case Me!sfrmholder.Form.Name
Case sfrmOldIncrements
Me!sfrmholder.Form!sfrm2.Form.Filter = "EmpSid = " & Me.txtEmpSId
Me!sfrmholder.Form!sfrm2.Form.FilterOn = True
Me!sfrmholder.Form!sfrm2.Form.Requery
case XXX
Me!sfrmholder.Form!sfrm2.Form.Filter = "EmpSid = " & Me.txtEmpSId
Me!sfrmholder.Form!sfrm2.Form.FilterOn = True
Me!sfrmholder.Form!sfrm2.Form.Requery

End Select
End Sub
But this is not working.can any one tell me how to do this? If i get the
name of the subform, i can continue with the above code. I am quite hopeful
of a solution!!!! please tell me how to solve this

 

[Douglas J. Steele]

I'll assume that that's not really what you want to do (since you've got the
same code regardless of which form is being used as the subform...)

Look at the SourceObject property of the subform control. Remember that the
property is text, so you names need to be in quotes:

Private Sub Form_Current()

Select Case Me!sfrmholder.SourceObject
Case "sfrmOldIncrements"
Me!sfrmholder.Form!sfrm2.Form.Filter = "EmpSid = " & Me.txtEmpSId
Me!sfrmholder.Form!sfrm2.Form.FilterOn = True
Me!sfrmholder.Form!sfrm2.Form.Requery
Case "XXX"
Me!sfrmholder.Form!sfrm2.Form.Filter = "EmpSid = " & Me.txtEmpSId
Me!sfrmholder.Form!sfrm2.Form.FilterOn = True
Me!sfrmholder.Form!sfrm2.Form.Requery
End Select

End Sub

 

[Marshall Barton]

I have a problem with finding the name of the present subform in the main
form. Before coming to the problem,let me give a background of the design
Actually every first level subform is unbound and they hold one or two
second level subforms which are bound to tables and are in one- to- many
relation with the mainform. And every level1 subform has two navigation
buttons ".Next > ", "< Back ". the code in the click event of these buttons
see what form follows next. The code is as follows.
Me.Parent.sfrmholder.SourceObject = "sfrm to come next" etc...

Now i want the second level forms to display records related to the record
in the main form. the code is i used in the current event of the main form is:
Me!sfrmholder.Form!sfrm11.Form.Filter = "EmpSid = " & Me.txtEmpSId
Me!sfrmholder.Form!sfrm11.Form.FilterOn = True
Me!sfrmholder.Form!sfrm11.Form.Requery
it works perfectly well if i display only one subform. but the subforms
change one by one as in a control wizard in access. so, my idea is to find
the name of the subform in a select case statement and depending of the
present subform i want to take action accordingly. it is something like
Private Sub Form_Current()
Select Case Me!sfrmholder.Form.Name
Case sfrmOldIncrements
Me!sfrmholder.Form!sfrm2.Form.Filter = "EmpSid = " & Me.txtEmpSId
Me!sfrmholder.Form!sfrm2.Form.FilterOn = True
Me!sfrmholder.Form!sfrm2.Form.Requery
case XXX
Me!sfrmholder.Form!sfrm2.Form.Filter = "EmpSid = " & Me.txtEmpSId
Me!sfrmholder.Form!sfrm2.Form.FilterOn = True
Me!sfrmholder.Form!sfrm2.Form.Requery

End Select
End Sub
But this is not working.can any one tell me how to do this? If i get the
name of the subform, i can continue with the above code. I am quite hopeful
of a solution!!!! please tell me how to solve this

I think you are wandering off the path there.

First, a Requery after setting the Filter and FilterOn
properties is redundant and a waste of resources.

Second, all that code is unnecessary. If the subsubform
records are related to mainform records, then you can use
the LinkMaster/Child properties to sync the subsubform
records to the current main form record. The trick with an
intervening subform is to set the LinkMaster to:
Forms!mainform.linkfield

And if you are using a single subform control and setting
its SourceObject property to change the form object it
displays, you can set the LinkMaster.Child properties as
needed at the same time.

With Me.Parent.sfrmholder.
.SourceObject = "sfrm to come next"
.LinkMasterFields = "Forms!mainform.txtEmpSId"
.LinkChildFields = "EmpSid"
End With

 

[Access infant]

Thank you, Douglas
It worked well. I actually tried the same at first but failed to get the
result until you pointed that i should put the subform name in quotes. Thanks
a lot and lot

 

[Access infant]

Mr.Marsh,
your suggestion is quite interesting but i don't know how to do this. could
you please explain to me in detail. Actually the level1 subform is unbound so
no question of linking fields arises. So, please tell me how i can link the
subsubform directly with the mainform. I confuse where i should put the
following code:
Forms!mainform.linkfield
Thanks in advance

 

[Marshall Barton]

your suggestion is quite interesting but i don't know how to do this. could
you please explain to me in detail. Actually the level1 subform is unbound so
no question of linking fields arises. So, please tell me how i can link the
subsubform directly with the mainform. I confuse where i should put the
following code:
Forms!mainform.linkfield

That's not "code". It's just a reference to a field or
control on the main form. Code is the VBA statements you
put in a module.

I was trying to explain that you should put that (with the
real form and field/control names) in the subsubform
control's LinkMasterFields property. I think the
LinkChildFields property would be EmpSid. (The subform is
not involved.)

 

[Access infant]

Mr. Marsh,
Simple but wonderful!!!!!!!!! Thanks a lot

 

[Marshall Barton]

Mr. Marsh,
Simple but wonderful!!!!!!!!! Thanks a lot

You're welcome. It does not come up often, but that little
trick should shortcut all kinds of funky design issues in
your situation.

I think I got the fact that the LinkMasterFields property
does not really have to be a field/control on the parent
form when someone that didn't know any better posted a
question about something else. I was about to say the link
master might be the problem, but thought I should try it
myself before I stuck my foot in my keyboard. I was amazed
that it worked and hastily added it to my bag of little
known tricks. You never know what new and interesting
things you can learn about Access in these newsgroups.