469,271 Members | 1,014 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

From Subform, refer to a control on a Mainform

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

Expand|Select|Wrap|Line Numbers
  1. Forms![Your Main Form Name]![Subform Name].Form![Subform control name]
  2.  
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.
Oct 26 '11 #1

✓ answered by 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.

8 14709
Seth Schrock
2,962 Expert 2GB
I believe that this should work:

Expand|Select|Wrap|Line Numbers
  1. 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.
Oct 27 '11 #2
NeoPa
32,171 Expert Mod 16PB
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.
Oct 27 '11 #3
Joe Y
77 64KB
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.
Oct 27 '11 #4
NeoPa
32,171 Expert Mod 16PB
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.
Oct 27 '11 #5
Joe Y
77 64KB
The codes below work, but I need further helps.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Cbo_ProductCode_AfterUpdate()
  2.  
  3. Dim strFilter As String
  4.  
  5. strFilter = "([ordh_cust_no]='" & Forms![F_Order_Header]![txt_ordh_cust_no] & "') AND " & _
  6.  "([ordd_stock_no]='" & Me.Cbo_ProductCode & "')"
  7.  
  8. End Sub
  9.  
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Cbo_ProductCode_AfterUpdate()
  2.  
  3. Dim strFilter As String
  4. Dim frmCurrentForm As Form
  5. Set frmCurrentForm = Screen.ActiveForm
  6. Dim strFormName As String
  7. strFormName = frmCurrentForm.Name
  8.  
  9. MsgBox (strFormName)
  10.  
  11. strFilter = "([ordh_cust_no]='" & Forms!strFormName![txt_ordh_cust_no] & "') AND " & _
  12.  "([ordd_stock_no]='" & Me.Cbo_ProductCode & "')"
  13.  
  14. End Sub
  15.  
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.
Oct 27 '11 #6
NeoPa
32,171 Expert Mod 16PB
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.
Oct 27 '11 #7
Joe Y
77 64KB
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Cbo_ProductCode_AfterUpdate()
  2.  
  3. Dim strFilter As String
  4.  
  5. strFilter = "([ordh_cust_no]='" & Me.Parent![txt_ordh_cust_no] & "') AND " & _
  6.  "([ordd_stock_no]='" & Me.Cbo_ProductCode & "')"
  7.  
  8.  
  9. Call DoCmd.OpenForm(FormName:="F_Ord_History", View:=acFormDS, WhereCondition:=strFilter)
  10.  
  11. End Sub
  12.  
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.
Oct 27 '11 #8
NeoPa
32,171 Expert Mod 16PB
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).
Oct 27 '11 #9

Post your reply

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

Similar topics

1 post views Thread by Spurry Moses | last post: by
3 posts views Thread by Christopher Young | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.