Cell Selection in Subform

[MikeEller]

Hello Forum,
I have a form with a subform. The subform was created with the form wizard and displays data from a specific table.
What I want to do is:
when the user clicks in a specified cell in the subform table (the "Remarks" column), I want that click event or focus event to display another form. The second form will display text data from another table. Which data is displayed depends on the row index of the table in the subform that user has selected.

Can this be done?
If so, how?

Thanks,
Mike

 

[Pat Hartman]

Take off your Excel hat and put on your relational database (Access) hat. Relational databases do not have cells. They do not use row and column to reference a single instance of a column. They use the primary key of the row and the field name. So, to open your form to a specific record, you can do a number of things.
1. Use the where argument of the OpenForm method to specify the pk of the row you want to show.
2. You can base the popup form on a query (all forms/reports should be based on queries anyway) that includes selection criteria that refers to the current record on an open form to specify the pk of the row you want to show.

There are others but these are the most common.

 

[MikeEller]

OK,
That makes sense to me. One last question...
Can I capture the user clicking into that grid as a click event or focus event? If not, how do I initiate the opening of the other form? I would like to use the click event if possible.

Thanks for the insight and push in the right direction.

Mike

 

[Pat Hartman]

It is better to use the double-click event. People are used to using it for "drill-down". Using the click event could prove annoying because just clicking in a field would open the other form. Look at the way Windows explorer works. Notice what happens when you click on the +, click on the directory name, and double-click on the directory name. That is the type of behaviour that is standard.

If you have never coded an event procedure before:
1. Open the properties dialog for the control.
2. Switch to the events tab
3. Find the double click event
4. At the far right of the property box you will find the builder button. Three dots that are hidden until you hover over them.
5. Click on the builder button.
6. Choose code if you get options.
7. Write the OpenForm instruction. It has intellisense so you might be able to write the code without reading the help entry first. But, I suggest that you read the help entry so you understand the options that the intellisense is showing you.
8. save the form.

 

[mresann]

Incidental to this discussion, I have come across a website that should clarify the correct code to access variables, controls, and recordsets from parent forms to subforms, and vice versa. This should alleviate a lot of frustration.

Correct Subform Coding Procedures