How to reference a field on a subform on a tab control?

[kmsperry]

As best I can tell my form has the following hierarchy of controls. As you go down the list, the item below is a child of the item above it.

Form - Partners-Detail
Tabcontrol - TabCtL6
Page - Interactions
Subform - Contract_Detail subform
Combo Box - Customer

How would I reference the combo box so as to requery it? So far all of my attempts have failed.

Thanks,

KMS

 

[John Big Booty]

Have a look at this link for the correct syntax for referencing sub-forms and the controls and properties from various relative locations.

If you are referencing the Combo from your main form the code to requery it should look something like;

Me!SubformName.Form!ComboName.Requery

 

[boblarson]

Just a quick lesson on this.

1. When referring to a subform, you need to refer to the CONTROL that houses the subform on the parent form and NOT the subform name itself.

2. If your subform Control is named differently than the subform itself, when you use the subform control name you also need to have the .Form. part at the end (some people use the bang (!) but I use the dot. The .Form. part tells Access that you want a property, method or event on the subform itself and not the subform control.

So,

if the code is on the main form:

Me.SubformControlNameHere.Form.ControlOnSubformHere

If on a different form:

Forms!SubformControlNameHere.Form.ControlOnSubformHere

And if the subformcontrol and subform are named the same and the code is on the main form:

Me.SubformControl.ControlNameHere

would be fine but I still use the .Form. part even though it is a default when they are both named the same. It just helps keep things consistent.

 

[kmsperry]

Thanks guys for all of the pointers but I still can't refer to the controls that I need to. I've read all of the articles and used your suggestions as best I can but the path I'm using to the control isn't working. I'm trying to do a Requery from a macro. I am able to get it to work on a different control that is on the main form by simply referring to the name of the control. (e.g. Requery Customer_Ctrl). If I try to refer to it by it's complete path (Requery Forms.Partners-Detail.Customer_Ctrl) it doesn't work. I tried this with another Control on the form and the behavior is the same. If I try to refer to the control on the subform directly (Requery Customer) it reports an error because that control is on the subform. If I try to refer to that control using the complete pathname it doesn't work either.

Also, I've read from a few people, including Bob, that I need to refer to the control that the subform is on and not the subform name itself. How do I determine the control that the subform is on? It is actually on a tabbed page. Is that the control it is on? I've tried using that name as well but to no avail.

Thanks for your help.

KMS

 

[spikepl]

The control in which a subform is housed you can identify by clicking on it once, in the designview of the main from, and looking at the Property-window. The tab does not intersperse itself in the control hierarchy, so don't worry about it.

 

[kmsperry]

Thanks spikepl. That at least confirms that I am looking at the correct control name on the form.

Anyone else got any advice what else I might be doing wrong?

 

[boblarson]

You may need to use some VBA code with this based on what I'm seeing in the Macro actions screen. It isn't set up to do subforms, from what it looks like. So, if you can get to the VBA screen (see here for how) then paste this function in a new standard module (not form or report module) and name the module basSubformActions:

Private Function RequeryForm(ByRef frm As Form)
    frm.Requery
End Function

And then in your macro, instead of using the Requery Action, use RunCode and in the function area type:

RequeryForm([Forms]![Partners-Detail].[Contract_Detail subform].[Form])

(leaving the [Form] part exactly as shown)

 

[kmsperry]

Bob,

Thanks. I was just trying the VBA option. I haven't written any VBA but the requery is very simple. I tried the same references I was trying in the Macro and they work in VBA.

The lesson learned for anyone reading this is that if you want to access a control in a subform to do something like a Requery, use VBA and not a Macro. All of the suggestions listed above for how to do that are valid for VBA.

Thanks everyone who provided help on this one.

KMS