By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 2,928 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

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

TheSmileyCoder
Expert Mod 100+
P: 2,321
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?
May 7 '10 #1

✓ answered by NeoPa

Check out this code in the OnLoad event procedure of my general purpose form that goes in the subform.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Form_Load()
  5.     Dim strOldTag As String
  6.     Dim ctlThis As Control
  7.  
  8.     With Me
  9.         strOldTag = .Tag
  10.         .Tag = "ActiveForm"
  11.         For Each ctlThis In .Parent.Controls
  12.             If ctlThis.ControlType = acSubform Then
  13.                 If ctlThis.Form.Tag = "ActiveForm" Then
  14.                     .lblTest.Caption = ctlThis.Name
  15.                     Exit For
  16.                 End If
  17.             End If
  18.         Next ctlThis
  19.         .Tag = strOldTag
  20.     End With
  21. End Sub
To see it working download the very small db attached.

Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,494
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.
May 7 '10 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
May 7 '10 #3

NeoPa
Expert Mod 15k+
P: 31,494
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.
May 7 '10 #4

TheSmileyCoder
Expert Mod 100+
P: 2,321
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:
Expand|Select|Wrap|Line Numbers
  1. If Me.Parent.MyName="ctrlPreparedBy" Then
  2.   Me.RecordSource="SELECT * FROM tbl_QA WHERE ID_QAType=1"
  3.   Me.lbl_QA.Caption="Prepared By"
  4.   Me.cmb_QAType.DefaultValue=1
  5. 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 :)
May 10 '10 #5

NeoPa
Expert Mod 15k+
P: 31,494
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.
May 10 '10 #6

TheSmileyCoder
Expert Mod 100+
P: 2,321
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_PreparedBy, ctrlFrm_CheckedBy, ctrlFrm_ApprovedBy , 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.
May 10 '10 #7

NeoPa
Expert Mod 15k+
P: 31,494
Check out this code in the OnLoad event procedure of my general purpose form that goes in the subform.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Form_Load()
  5.     Dim strOldTag As String
  6.     Dim ctlThis As Control
  7.  
  8.     With Me
  9.         strOldTag = .Tag
  10.         .Tag = "ActiveForm"
  11.         For Each ctlThis In .Parent.Controls
  12.             If ctlThis.ControlType = acSubform Then
  13.                 If ctlThis.Form.Tag = "ActiveForm" Then
  14.                     .lblTest.Caption = ctlThis.Name
  15.                     Exit For
  16.                 End If
  17.             End If
  18.         Next ctlThis
  19.         .Tag = strOldTag
  20.     End With
  21. End Sub
To see it working download the very small db attached.
Attached Files
File Type: zip Smiley.Zip (16.9 KB, 97 views)
May 10 '10 #8

TheSmileyCoder
Expert Mod 100+
P: 2,321
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!
May 10 '10 #9

NeoPa
Expert Mod 15k+
P: 31,494
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.
May 10 '10 #10

Post your reply

Sign in to post your reply or Sign up for a free account.