Refer to a field on a subform 1

[hceonetman]

I want to do validity checking on a field on a subform, but am a bit confused about how to refer to the field in VBA. The main form is "Frm_Receipts_Header_Admin" and the subform is "Frm_Receipts_Detail_Admin". I created a module on the afterupdate property of the field "OL3_Code". In the module I can reference a field on the main form with "[Screen].[ActiveForm]![Dept_Code]", but I'm not clear on the syntax to check the field from the subform. As usual, MSoft help is no help. Any help will be much appreciated.

HCEONETMAN
When in doubt, mumble; When in trouble, delegate; When in charge, ponder.

 

[RoyVidar]

Microsoft has for instance this information How to Refer to a Control on a Subform or Subreport, where some of the information, is that a subform isn't considered an open form (thus not recogniced as an open form in the forms collection, and I believe not as ActiveForm) but a control on the main form. Controls on subforms need to be referred thru the main form.

Controls on a subform should then be reachable thru different types of syntaxes, for instance:

General syntax:
[tt]forms("frmMain")("frmSubFormControlName").Form("txtControl")
forms!frmMain!frmSubFormControlName.Form!txtControl[/tt]

See the discussion on subform control name in the above link. If your subform name is also the subform control name, then perhaps something like this

[tt]forms("Frm_Receipts_Header_Admin")("Frm_Receipts_Detail_Admin").Form("txtMySubControl")[/tt]

Roy-Vidar

 

[hceonetman]

Roy-Vidar
That's put me on the right track. By experimenting with the syntax I can see when I'm referencing the correct form, then the correct form and sub-form, then form, sub-form and control. My code now includes:

Str_OCA = Forms(Frm_Receipts_Header_Admin)(Frm_Receipts_Detail_Admin)![OCA_Code].

However, this produces a run time error 438 'Object doesn't support this property or methos. (Str_OCA is defined as a string variable within the procedure). Could this have to do with there being multiple rows in the sub-report? MS Knowledgebase didn't help.
Thanks again,

HCEONETMAN

 

[PHV]

And this ?
Str_OCA = Forms(Frm_Receipts_Header_Admin)(Frm_Receipts_Detail_Admin).Form("OCA_Code").

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[hceonetman]

PHV,
I had also tried that, based on the MS link supplied by Roy-Vidar. The result is the same. To make sure, I copied directly from your post into the procdure (minus the trailing period).
This is not make-or-break in my app, but still an annoyance that I can't get it to work. I appreciate your quick responses.

Hceonetman

 

[kjv1611]

Maybe try this:

Str_OCA = Forms(Frm_Receipts_Header_Admin)(Frm_Receipts_Detail_Admin).["OCA_Code"]

?

Just an idea, b/c I've noticed many times, myself, that one syntax works well in one instance, but a different syntax works better in another. Of course, sometimes, only one syntax will work correctly at all! [SMILE]

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[Zion7]

This works for me.

I have 2 subforms, on a main form. This procedure is called from subform1, in order to change ForeColor, on subform2.

Forms!Form1![SubForm2].Form!txtCity.ForeColor = vbRed


This, filters subForm2, from clicking on a text, in subForm1.

s = Forms!Form1![SubForm2].Form!txtCity
Forms!Form1![SubForm1].Form.Filter = "txtCity = """ & s & """"
Forms!Form1![SubForm1].Form.FilterOn = True

even if you were calling the procedure, from the main form, same syntax will apply.

Hope this helps, good Luck!

 

[PHV]

My bad:
Str_OCA = Forms("Frm_Receipts_Header_Admin")("Frm_Receipts_Detail_Admin").Form("OCA_Code")
Or
Str_OCA = Forms!Frm_Receipts_Header_Admin!Frm_Receipts_Detail_Admin.Form!OCA_Code

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

If you're working with 2003 (or might do), then I've found it to be "less forgiving" than previous versions (for instance regarding usage of the .Form keyword when referencing subform controls). If PHV's above suggestions don't work, then I'd guess on what I tried to emphasize in my first reply, might be the reason: The subform control name differs from the subform name.

Easiest way of obtaining the correct reference, is using the expression builder (for instance from the criteria row of a query, or a control source of a control on another form - and have this form open), double click thru forms, loaded forms, main form, sub form and then the control in question, and you should get a reference looking like PHV's second sample, but with the subform control name.

Used from the main forms form module, one could als drop off some of the referencing, using the Me keyword:

[tt]Str_OCA = Me("Frm_Receipts_Detail_Admin").Form("OCA_Code")
-----
Str_OCA = Me!Frm_Receipts_Detail_Admin.Form!OCA_Code[/tt]

- but again, with the subform control name.

Roy-Vidar

 

[hceonetman]

Roy-Vidar, PHV, dboulos, kjv1611:
Got off the track for a couple of days. Again, thanks for all the suggestions. Roy-Vidar's idea of using the expression builder was a great idea. After using that, I still get an error msg: "Object doesn't support this property or method". At least this tells me I'm referring to the object by the correct name but I'm trying to do something with it that is not supported. The expression builder provided me with:

Forms![FRM_Receipt_Header_Admin]![Receipt Details].Form![OCA_Code_Label],

so it looks like both the subform control name and the field name were wrong. I'll continue looking for an answer to the new problem, and appreciate any further ideas.
HCEONETMAN

 

[RoyVidar]

Yes, you are probably addressing the control by it's right name, but you might be addressing a property of the control that the control type doesn't support.

In your last reply, it seems that you are addressing a label not a text control. If you are addressing a label, then you need to specify which property, it would probably be the .Caption property, it doesn't support the default property (.Value) -> "Object doesn't support this property or method"

BTW - I'm getting more and more conservative, and has grown a slight distaste of using implicit referencing, and always use rather explicit referencing:

Text control:
[tt]Me("Receipt Details").Form("txtOCA_Code").Value[/tt]

Label:
[tt]Me("Receipt Details").Form("lblOCA_Code").Caption[/tt]

- also "showing off" the naming convention I use on all controls that are manipulated thru code or referenced in controlsources, queries... there are some times when using the same name for the control as the name of the field it's bound to, creates headaches...

Roy-Vidar

 

[hceonetman]

Roy-Vidar
That's my oops. I used the expression builder, but chose the first reference to OCA, which was OCA_Code_Label. Looking further down I find the correct one, OCA_Code. I changed the reference and added the ".Value" to the statement in my last post and it works. Just to experiment, I may change to the "me" naming convention, but either way, I've got it working.
Thanks again, a star for your help.

HCEONETMAN
If builders built buildings the way programmers write programms, the first woodpecker to come along would destroy civilization.