Displaying individual client details from subform

[mikeone610]

HI. I am going to try and explain this clear because I feel like it is confusing.

I have a form with many control criteria at the top and Subform1 below that displays the query results in a table that matches the control criteria. So a user could say search for a client that is in a certain region and have a average order size of X and when the user puts in there criteria and hits the search button it refreshes Subform1 below that displays the matches.



What I want to do is add another subform to my current form that would show the user detailed results about the client because currently in the existing Subform1 it just shows some top level aggregate information.

Would it be possible to add another subform that would be populated with the clients detailed info when a user clicks on a client from Subform1?

I can not post a copy of the Database for privacy reasons but here is pic that I think explains it pretty well (sorry about the spelling error it should be "entry" and not "entiery").

[RuralGuy]

Absolutely! For ease I usually create an invisible TextBox on the MainForm that points to the LinkChildField of the 1st SubForm. Then point your LinkMasterField of the 2nd SubForm to this invisible TextBox.

[mikeone610]

Hi thanks for the quick reply. I am a bit new at this so I have some questions.

Would I point the individual Textbox to the LinkChildField of the 1st form in the Textboxes Control Source? Or does that take place somewhere else?

[June7]

Sounds like synchronizing two subforms. Review http://www.fmsinc.com/MicrosoftAcces...edSubforms.asp

[RuralGuy]

Your control source of the invixible textbox should read something like:
=[Your1stSubFormControl].FORM.[TheLinkFieldOnThisForm]
...replacing the RED values with your actual values.

[mikeone610]

Boy this is going to be tough. So look at the examples that June7 posted I see that the user has to type in the ID number to actually populate the subform with the detailed info, does it have to be that way of can it be populated when the user clicks on a entry of the 1st subform?

And RuralGuy what do you mean by [Your1stSubFormControl]?

[RuralGuy]

SubForms are displayed on Forms by means of a SubFormControl. It does not have to be named the same as the SubForm it displays. You will be looking at the SubFormControl property sheet when you can see then LinkMaster/ChildFields properties on the Data tab and the name of the control in on the Other tab.

[mikeone610]

I am at that properties tab on the subform where I can see LinkMaster/LinkChild but I do not see the SubFormControl Name, it is just the name of the SubForm itself?

[June7]

You misunderstood the article. User does not type in the ID number, it is referenced by expression in the textbox ControlSource. The image with the textbox in red is demonstrating the linked ID value but that red textbox would be set as not visible. Review the article again.

ControlName property is on Other tab. The BoundObject (the subform) property is on Data tab. I always give subform container control a name different from the object it holds, such as ctrDetails

[mikeone610]

Ok. I am trying to go the route the June7 has recommended via the link above. I am having a hard time with the Textbox which references the MasterID for the Details Form to be populated.

I added a Textbox and in the Control Source I put: =[SubformMaster].[Form]![UserID]

Because every user displayed in the aggregate query results has a UserID which is the Key that links them to the detail Table with individual activities that I want to display on the Details subform.

When I place the above text in the Control Source of the Textbox in Form view the box just says "#NAME?" without quotes. Is this because I am querying many results that have UserIDs and it does not know which one to select or is this because I did something wrong?

Thank you very much for your help thus far.

[June7]

The expression should retrieve the UserID of the current record of that subform. Give the UserID textbox a name different from the field it is bound to, like tbxUser, and refer to the textbox name.

That technique is referencing the control and therefore needs the Form class in the reference. Referencing a field that is in the subform RecordSource would not require the Form class so this version might work:

[subformcontainername]![fieldname]

I know, it's weird and confusing but that is what I have experienced.

I always give controls a name different from the object or field they are bound to.

[mikeone610]

Man this is confusing.

So for clarification sake the form which is the aggregate query result is called SubformMaster and the field I want in the Textbox is called UserID, that field is currently displayed in a Textbox on the SubformMaster called UserIDtext.

I am trying to display the current selected UserID field in a Textbox called TextUserID. In the control box for TextUserID I have tried both the following:

=[SubformMaster].[Form]![UserID]

and

=[SubformMaster].[Form]![UserIDtext]

and

=[SubformMaster]![UserID]

and

=[SubformMaster]![UserIDtext]

All of those one at a time of course, but the results were the same in Form view it would always show #NAME? in the Textbox called TextUserID. Any idea what I am doing wrong?


EDIT: SUCCESS!!! It worked when I was messing around with the ControlSource expression builder instead of:

=[SubformMaster].[Form]![UserIDtext] I had to put in =[CustomerSearch Subform].Form![UserIDtext]

I really do not know why because the Subform is not named that but whatever it works! Now onto linking the Textbox to the Subform.

Would it be possible for me to refrence a field which is not shown on the subform query? I am refrencing the UserID because it is linked in the detailed table but I would prefer to not have users see the UserID because it doesn't really mean anything to them.

[June7]

Yes, set the control Visible property to No. That is noted in the tutorial I referenced.

[mikeone610]

Ok. So I have my new DetailsSubform set up but it is pulling all records opposed to the actual UserID that I am clicked on and is being displayed in the Textbox I just setup.

Sorry if this is frustrated. The example link you provided is very helpful but I was hoping to ask just a few more questions.

So the MasterSubform is pulling info from two tables but the new DetailsSubform will pull info from one table which is named DetailTable. It will display all information from that table as long as it has the same UserID that is displayed in the Textbox I set up which is named TextUserID.

My new DetailsSubform is set up as followed:

Source Object: Table.DetailTable
Link Master Field: TextUserID
Link Child Field: UserID

Yet it still just displays all UserID details opposed to the current UserID that is displayed in the TextUserID Textbox. Any thoughts on where I went wrong here?

[RuralGuy]

Try: Master Field: Me.Parent.TextUserID