Go to a record on one subform clicking on another subform.

[alfranco17]

Hi.


I would really appreciate your help on this issue.


I have two subforms on the same form. On the left side I have a list of authors, and on the right I have the books they have written.


I would like to select an author and automatically filter the list of their books, as shown here (I did it manually, by filtering by author on the right side and then hiding it). Click on Mark Twain, get his books.

Click on Bob Umlas, get his books:

I have found how to do it if I want to open the report in a new window, or if one is a subform of the other, but I want to have them both as subforms in the same form.


Thanks a lot!
Armando.

 

[apr pillai]

It can be done, if you have stored Author-Code (or author-name) along with the Books he wrote. Do the following:

1. Open the Main-Form with Sub-Form1 and Sub-Form2 in design view.
2. Create a Textbox somewhere on the Main-Form.
3. Press F4 to display the Property Sheet, while the Textbox is still in selected state.
4. Change the Name Property Value to txtAuthor.
5. Write an expression in the textbox like the sample one given below:

=Me.SubForm1.Form![AuthorName]

1. Replace .SubForm1 and ![AuthorName] with correct values.
2. Click on Sub-Form2 to select it.
3. Display it's Property Sheet.
4. Find the Property with the name Link Master Field and select it.
5. Type the value txtAuthor in the Property.
6. Type the Author Field Name of Sub-Form2 in the Link Child Field Property.
7. Save the Forms.
8. Open the Main Form in Normal View.
9. Click on one of the Author's name on the first Sub-Form.

You will see the selected Author's Name (or Code) appearing on the textbox you have created on the Main form. If you have followed the above instructions correctly with appropriate field names, form name then you will see the selected Author's Books list on the second Sub-Form.

NB: You can set the Visible Property value of the Textbox to No so that it stays hidden in normal view of the form.

 

[alfranco17]

Many thanks, it worked beautifully!