From Subform, refer to a control on a Mainform

[Joe Y]

I read a thread about referring to a control on a Subform with the following format.

Forms![Your Main Form Name]![Subform Name].Form![Subform control name]

How to work on opposite way, referring to a control on a Mainform from SubForm?

I tried using the same format, except using Mainform control name inside the last bracket, but it doesn’t work.

Thanks.

[Seth Schrock]

I believe that this should work:

Forms![main form name here]![control name here]

I know that I have had to do exactly what you are describing, but I don't remember which database it was, but I do believe that this would work. Let me know.

[NeoPa]

When you're already within a module associated with one of these forms then you really don't want to use the fully specified version in your references. The article does give other options and explain how and when to use them.

When referring up, as in from Sub Form to Main Form, Me.Parent should be the base of your reference. The control name follows from there.

[Joe Y]

NeoPa,

Could you please point out the link to the article you referred to? I searched this subject by key words. The closest one I found was a thread dated in April this year. There was no discussion of other options.

Thanks.

[NeoPa]

My mistake Joe.

The article is Referring to Items on a Sub-Form.

It doesn't cover referring back to the main form, but I covered that the correct way in post #3.

[Joe Y]

The codes below work, but I need further helps.

Private Sub Cbo_ProductCode_AfterUpdate()

Dim strFilter As String

strFilter = "([ordh_cust_no]='" & Forms![F_Order_Header]![txt_ordh_cust_no] & "') AND " & _
 "([ordd_stock_no]='" & Me.Cbo_ProductCode & "')"

End Sub

The next challenge is that this Subform is embedded in two different Mainforms. When user clicks the combobox in the Subform, the program should perform same results disregard which Mainform user is in. To do so, I use the following codes to return Mainform names instead of spelling out specific Mainform name in the code.

Private Sub Cbo_ProductCode_AfterUpdate()

Dim strFilter As String
Dim frmCurrentForm As Form
Set frmCurrentForm = Screen.ActiveForm
Dim strFormName As String
strFormName = frmCurrentForm.Name

MsgBox (strFormName)

strFilter = "([ordh_cust_no]='" & Forms!strFormName![txt_ordh_cust_no] & "') AND " & _
 "([ordd_stock_no]='" & Me.Cbo_ProductCode & "')"

End Sub

The message box does show the correct Mainform name in the popup window, but after clicking OK button, it follows with an error message saying Access cannot find the form ‘strFormName’. I have tried to include the strFormName inside brackets or double quotes, that didn’t work, either.

Please guide what part of the code is wrong.

Thanks.

[NeoPa]

You have a few problems in there Joe.

In most cases using the code suggested by Seth is just not too clever. A bit clumsy but it will work if you know the name of the main form already.

In this case, where that information isn't known, it makes sense to use the more appropriate method suggested in post #3 which is to use the Me.Parent reference.

PS. I notice from your posted code that you do nothing with strFilter once you've set it up. Is this simply because you haven't got that far yet?

PPS. I'm going to have to reset Seth's Best Answer post I'm afraid. It's good thinking but just not quite correct for the question. I've avoided it thus far as I had no wish to discourage Seth in any way, but unfortunately it could mislead people searching for similar problems.

[Joe Y]

Oops! Sorry, I missed one important line. The missing line is the "Call DoCmd.OpenForm" in the working code below.

Also, thanks for suggesting the Me.Parent reference method. This would allow my reference task a lot more flexible.

Private Sub Cbo_ProductCode_AfterUpdate()

Dim strFilter As String

strFilter = "([ordh_cust_no]='" & Me.Parent![txt_ordh_cust_no] & "') AND " & _
 "([ordd_stock_no]='" & Me.Cbo_ProductCode & "')"

            
Call DoCmd.OpenForm(FormName:="F_Ord_History", View:=acFormDS, WhereCondition:=strFilter)

End Sub

However, could you please still let me know, using my previous broken codes as example, how to make Access know the program is calling the Mainform name in line # 11 of previous post and use it as part of the filter string?

Thanks.

[NeoPa]

Joe Y:
Oops! Sorry, I missed one important line. The missing line is the "Call DoCmd.OpenForm" in the working code below.

That's why we insist you use Copy/Paste etc (See When Posting (VBA or SQL) Code) when you post code. Don't worry about previous posts but please make sure you follow the guidance in all future posts.

Joe Y:
However, could you please still let me know, using my previous broken codes as example, how to make Access know the program is calling the Mainform name in line # 11 of previous post and use it as part of the filter string?

This bit's a little weird. It seems to me you already have a working bit of code for that (Lines #5 & #6) which appear to be correct. What else would you want? I see no sensible place to use the name of the main form as it's totally unrequired when coding the reference properly (As it appears to me you have already done).