How to get the control name of my subform, from within the subform?

[TheSmileyCoder]

I have a subform in a form, and that subform is repeated 3 times within the same Parent form. When the subform opens, I want to set the recordsource of a combobox within the subform, depending on where the subform is placed (and whether is subform 1, 2 or 3)

What I do currently is that I know they open in a specific order, and simply use a public counter, to keep track of which subform is opening, and set my recordsource according to that.

What I would like to do, is to access the control name within the parent, I.E the name that the subform has been givin in the parent form. Anyone know how to get this?

[NeoPa]

The term Subform is often used to refer to the Access Form that is contained within the .Form property of a Subform control. This is not correct usage and can cause confusion.

Can you say exactly how you're using the term here. Is it a subform control you're referring to, or a form within such a control?

You can be assured that whatever your answer is, we will try especially hard to help you find a solution, due to the efforts you've already made on behalf of our members.

[TheSmileyCoder]

On my main form, I have a Control, within that control is a form (what I call my subform). From within my subform, I need to know the name of the control, within which it resides.

[NeoPa]

Me.Parent gives you access to the form that the subform controls are on. It doesn't tell you which of the subforms the current form is attached to though I'm afraid (at least not anywhere I know about or could find). If you have multiple subform controls, all with the same form attached to them, then I see no way of determining which is the attached one.

It could be determined with some fiddly code I expect. Set the .Tag property to a value that doesn't occur naturally, then check through all the subform controls in Me.Parent to find one whose attached form also has that .Tag setting. It's certainly not direct, but can determine the correct one for you.

Referencing items can be done using Referring to Items on a Sub-Form.

[TheSmileyCoder]

In the end I decided to put the code in the parent form's Load event.
Im unsure whether this is the best place to put it, but its working now, so I don't wish to spend any more time on it.

Thank you for taking the time to look at it.
The .Tag approach wouldn't work, any approach that requires looping through the parents controls would not work, since I need to know which ctrl im "in" before I can use the tag approach.

Basicly what I was looking for is to be able to write in the forms open/load event:

If Me.Parent.MyName="ctrlPreparedBy" Then
  Me.RecordSource="SELECT * FROM tbl_QA WHERE ID_QAType=1"
  Me.lbl_QA.Caption="Prepared By"
  Me.cmb_QAType.DefaultValue=1
End If

Because that would allow me to reuse the same form once for Prepared By, once for Checked By, and once for Approved by. Now as I said, I just do it from the parent form.
But seems there is no .MyName property :)

[NeoPa]

I'm not sure I see your problem with the .Tag approach Smiley. There are .Tag properties on the form as well as on most of its controls. This could easily be set in the code, and also checked with reference to the parent form. It's your choice of course, but I think it should work.

As for the .MyName property, there is a .Name property of course. Is this what you need or do I misunderstand what you're talking about.

PS. I love the idea of re-using a form intelligently rather than creating three of them with minor differences.

[TheSmileyCoder]

I have noticed that as I get more and more experience in VBA the amount of code I choose to attach to a form increases as well. And having to keep 3 forms uptodate, when they only have minor differences was really starting to annoy me. Thats when I started looking for a more re-usable approach.

Imagine a form frm_DocumentQA containing 3 controls, each control containing a sub form (the same form). Lets name the subfrm frm_QA, and the 3 controls named ctrlFrm_Prepare dBy, ctrlFrm_Checked By, ctrlFrm_Approve dBy , each having the same sourceobject frm_Qa. They all have the same Link Child Fields and Link Master Fields properties, namely Link Master Field: KEY_Document and Link Chield Fields: ID_Document.


Even giving them tags, I could just as well cycle through the .Name property as the .Tag property. The problem remains that from within the sub form, I don't know which control my subform is "stored" in.


The .MyName was an imaginary name for the property I was wishing for, the .Name would just return frm_QA for all 3 subforms.

[NeoPa]

Check out this code in the OnLoad event procedure of my general purpose form that goes in the subform.

Option Compare Database
Option Explicit

Private Sub Form_Load()
    Dim strOldTag As String
    Dim ctlThis As Control

    With Me
        strOldTag = .Tag
        .Tag = "ActiveForm"
        For Each ctlThis In .Parent.Controls
            If ctlThis.ControlType = acSubform Then
                If ctlThis.Form.Tag = "ActiveForm" Then
                    .lblTest.Caption = ctlThis.Name
                    Exit For
                End If
            End If
        Next ctlThis
        .Tag = strOldTag
    End With
End Sub

To see it working download the very small db attached.

[TheSmileyCoder]

Brilliant!

I haven't tried it yet, but im sure it will work. I never thought of actually setting the tag of the subform, I always thought you were setting the tag of the control in the parent form, containing the subform.

Now I see why it will work. Thank you NeoPa!

[NeoPa]

I'm please to help of course Smiley :)

I think your confusion was probably related to using the terms incorrectly. This can so easily happen. Anyway, the important thing is that you understand now.