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

How to Display Subform Value on the Main Form

100+
P: 418
I am trying to display a value from the sub form on to the main form. It's not a calculated data, just plain text.

This is what I attempted without success.

1. Created a text box called "Hero" within the subform [sfrmItemsCast] footer.
2. In its control source I added this function:
=iif(([icrSort] = 1,[iccID]),"")
3. Created another text box on the main form and added this in its control source:

=[sfrmItemsCast].Form![Hero]

But I am getting this error message: #Name? I should mention that icrSort is a text field.

My goal is to display the hero's name in a fancy font up on the main form.

Can anyone please help me with this problem? Thanks a lot.
Jun 5 '10 #1

✓ answered by ADezii

@MNNovice
This should do the trick:

Share this Question
Share on Google+
24 Replies

ADezii
Expert 5K+
P: 8,736
@MNNovice
Try this in the Control Source of the Text Box in the Main Form:
Expand|Select|Wrap|Line Numbers
  1. =IIf(Forms!<Form Name>![sfrmItemsCast].[Form]![icrSort]="1",Forms!<Form Name>![sfrmItemsCast].[Form]![iccID],"")
Jun 6 '10 #2

100+
P: 418
@ADezii
Adezeii:

I modified the code to match names of my forms. But I am getting this "invalid syntax" error message that reads: "You have entered a comma without a preceding value or identifier"

Expand|Select|Wrap|Line Numbers
  1. =IIf(Forms!<fItems>![sfItemsCast].[Form]![icrSort]="1", Forms!<fItems>![sfItemsCast].[Form]![iccID], "")
  2.  
Thanks for looking into my problem. M
Jun 6 '10 #3

ADezii
Expert 5K+
P: 8,736
@MNNovice
Any chance of Uploading the Database?
Jun 7 '10 #4

100+
P: 418
@ADezii
Certainly. Please see the attached.

Main form: fItems
Sub form: sfItemsCast

I am trying to display names of the main characters (1 and 2) on fItems. Currently it's showing as "#Name?"

Thanks for your time and effort.
Jun 7 '10 #5

ADezii
Expert 5K+
P: 8,736
@MNNovice
It appears to me, that what you are requesting can be accomplished via the Current(0 Event of the sfItemsCast Sub-Form. Delete the Control Sources for the Tetxt Boxes on the Main Form, then Copy-and-Paste this Code into the Current Event of the Sub-Form:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. 'Heroine Text Box in Main Form
  3. If Me![icrSort] = 1 Then
  4.   Forms!fItems![Text51] = Me![iccID]
  5. Else
  6.   Forms!fItems![Text51] = ""
  7. End If
  8.  
  9. 'Hero Text Box in Main Form
  10. If Me![icrSort] = 2 Then
  11.   Forms!fItems![Text52] = Me.[iccID]
  12. Else
  13.   Forms!fItems![Text52] = ""
  14. End If
  15. End Sub
Jun 7 '10 #6

100+
P: 418
@ADezii
ADezii:

Sorry but it didn't work. I get blanks on fItems.

As suggested I made the two text boxes on fItems "Unbound".

Added the following codes on Current of the sfItemsCast:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3. 'Heroine Text Box in Main Form
  4. If Me![icrSort] = 1 Then
  5. Forms!fItems![Heroine] = Me![iccID]
  6. Else
  7.  
  8. Forms!fItems![Heroine] = ""
  9.  
  10. End If
  11.  
  12. 'Hero Text Box in Main Form
  13. If Me![icrSort] = 2 Then
  14. Forms!fItems![Hero] = Me.[iccID]
  15. Else
  16. Forms!fItems![Hero] = ""
  17. End If
  18.  
  19.  
  20. End Sub
I have replaced Text51 and Text52 with "Hero" and Heroine respectively. What am I doing wrong? I didn't get any error message but the unbound text boxes on fItems does not even display although the visible is set to YES.

Thanks.
Jun 7 '10 #7

ADezii
Expert 5K+
P: 8,736
@MNNovice
Text51 and Text 52 remain the same, and do not change.
Jun 7 '10 #8

100+
P: 418
@ADezii
Even though on sfItems Footer, I changed the names of these two text boxes (Text51 to Heroine and Text52 to Hero)?
Jun 7 '10 #9

ADezii
Expert 5K+
P: 8,736
@MNNovice
I am referring to the Text Boxes on the Main Form, as per the Attachment:
Attached Files
File Type: zip 060710_DVD_2.zip (659.4 KB, 197 views)
Jun 7 '10 #10

100+
P: 418
@MNNovice
Okay. I see what you mean. But why no names is being displayed on the main form? Why those two text boxes do not even show up? On record no. 1, it should read as "Suchitra Sen" and "Uttam Kumar" for example. But they remain blank and invisible. Why is that?
Jun 7 '10 #11

ADezii
Expert 5K+
P: 8,736
@MNNovice
Because you are referring to the Current Record on the Sub-Form the way I read it. Put the Cursor on a Record with an ID of either 1 or 2 in the Sub-Form.
Jun 8 '10 #12

100+
P: 418
@ADezii
Okay. It works but not the way I intended. First of all, it displays the iccID and NOT the name associated with it.

If I were to put my cursor on an ID - it beats the purpose. I intend to automatically display the names of those actors who play the main roles (icrSort = 1 and 2 or Hero or Heroine) in a given movie.

Hope this makes sense. Thanks again for your help. M
Jun 8 '10 #13

NeoPa
Expert Mod 15k+
P: 31,770
Subscribing for later :)
Jun 8 '10 #14

ADezii
Expert 5K+
P: 8,736
@MNNovice
How about Posting some specific examples from Records contained within the Sub-Form from the Demo DB, listing the specific Main Record Number, Sub-Form Record(s), Hero/Heroine's Name, etc. This is one of those problems that is very hard to conceptualize from the opposite end of a Web Page (LOL).
Jun 8 '10 #15

ADezii
Expert 5K+
P: 8,736
@NeoPa
I'm really off on a Tangent on this one, think you can have a look-see?
Jun 8 '10 #16

100+
P: 418
@ADezii
ADzeii:

1. Consider the very first record iID 1.
2. on the main form fItems, the title (Haarano Shoor) is displayed on the Main Form's header section.
3. I am trying to get the names of the main role players displayed on the form's header section as well. So basically for iID #1, these names will be a)Heroine: Suchitra Sen and b) Hero: Uttam Kumar
4. Issue: I had no problem getting the title displayed as this data resides on the Main Form fItems. But the names of the main role players reside inside the sub form sfItemsCast. So my question is how do I accomplish the goal stated above in item#3?

Hope this explains my objective. Thanks.
Jun 8 '10 #17

NeoPa
Expert Mod 15k+
P: 31,770
I will certainly try when I can ADezii, but that's unlikely to be for a few days I'm afraid. Lots going on at the moment and this appears to be one that will take a certain amount of time just understanding the question and catching up etc.
Jun 8 '10 #18

100+
P: 418
@NeoPa
NeoPa:

I appreciate your time and willingness to help me. Thank you very much.

M
Jun 8 '10 #19

ADezii
Expert 5K+
P: 8,736
@NeoPa
Thanks NeoPa, but I do believe that I have found a solution, although in a different manner. Thanks.
Jun 8 '10 #20

ADezii
Expert 5K+
P: 8,736
@MNNovice
This should do the trick:
Attached Files
File Type: zip 060710_DVD_3.zip (658.9 KB, 187 views)
Jun 8 '10 #21

100+
P: 418
@ADezii
ADzeii:

Awsome. This is exactly what I was looking for. Your effort and time in helping me is much appreciated.

Please unload the zipped file of my DB from this site.

Until next question. Thanks for teaching me a new trick. MNNovice
Jun 9 '10 #22

NeoPa
Expert Mod 15k+
P: 31,770
I hope to get time to look at this tomorrow (Thursday) so let me know if ADezii's latest solution solves the issue.
Jun 9 '10 #23

100+
P: 418
@NeoPa
NeoPa:

Thanks for checking. Yes, ADzeii's solution worked just fine. But I was going to ask if it's possible to explain how does it work. This is only for my own learning.

He created a query for the sub form. Then added the following code in the text boxes on the main form:

Expand|Select|Wrap|Line Numbers
  1. =DLookUp("[Name]","qrySubForm","[iciID]=" & Forms!fItems!iID & "AND [icrSort] = '1'")
My understanding of this code:

Dlookup Name from the qrySubForm where iciID matches the iiD of the main form fItems AND icrSort equals 1.

Is my understanding correct?

Thanks.
Jun 9 '10 #24

NeoPa
Expert Mod 15k+
P: 31,770
Yes. It is certainly correct :)
Jun 9 '10 #25

Post your reply

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