How to Display Subform Value on the Main Form

[MNNovice]

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.

[ADezii]

@MNNovice
Try this in the Control Source of the Text Box in the Main Form:

=IIf(Forms!<Form Name>![sfrmItemsCast].[Form]![icrSort]="1",Forms!<Form Name>![sfrmItemsCast].[Form]![iccID],"")

[MNNovice]

@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"

=IIf(Forms!<fItems>![sfItemsCast].[Form]![icrSort]="1", Forms!<fItems>![sfItemsCast].[Form]![iccID], "")
 

Thanks for looking into my problem. M

[ADezii]

@MNNovice
Any chance of Uploading the Database?

[MNNovice]

@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.

[ADezii]

@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:

Private Sub Form_Current()
'Heroine Text Box in Main Form
If Me![icrSort] = 1 Then
  Forms!fItems![Text51] = Me![iccID]
Else
  Forms!fItems![Text51] = ""
End If
 
'Hero Text Box in Main Form
If Me![icrSort] = 2 Then
  Forms!fItems![Text52] = Me.[iccID]
Else
  Forms!fItems![Text52] = ""
End If
End Sub

[MNNovice]

@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:

Private Sub Form_Current()
 
'Heroine Text Box in Main Form
If Me![icrSort] = 1 Then
Forms!fItems![Heroine] = Me![iccID]
Else
 
Forms!fItems![Heroine] = ""
 
End If
 
'Hero Text Box in Main Form
If Me![icrSort] = 2 Then
Forms!fItems![Hero] = Me.[iccID]
Else
Forms!fItems![Hero] = ""
End If
 
 
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.

[ADezii]

@MNNovice
Text51 and Text 52 remain the same, and do not change.

[MNNovice]

@ADezii
Even though on sfItems Footer, I changed the names of these two text boxes (Text51 to Heroine and Text52 to Hero)?

[ADezii]

@MNNovice
I am referring to the Text Boxes on the Main Form, as per the Attachment:

[MNNovice]

@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?

[ADezii]

@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.

[MNNovice]

@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

[NeoPa]

Subscribing for later :)

[ADezii]

@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).

[ADezii]

@NeoPa
I'm really off on a Tangent on this one, think you can have a look-see?

[MNNovice]

@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.

[NeoPa]

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.

[MNNovice]

@NeoPa
NeoPa:

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

M

[ADezii]

@NeoPa
Thanks NeoPa, but I do believe that I have found a solution, although in a different manner. Thanks.

[ADezii]

@MNNovice
This should do the trick:

[MNNovice]

@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

[NeoPa]

I hope to get time to look at this tomorrow (Thursday) so let me know if ADezii's latest solution solves the issue.

[MNNovice]

@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:

=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.

[NeoPa]

Yes. It is certainly correct :)