On Double Click Event in Subform (1 Viewer)

Hi everybody,

I have a form for users to enter project data into a table. On this form, I also have a subform displaying a list of the users projects.

When a user selects a project record from the subform, I'd like the main form to navigate to this record so the user can update their project data.

When the user selects a record from the subform, the selected record primary key is stored in an invisible text box called txtSelectedRecord.


Then, I have a command button telling the main form to navigate to the record selected in the subform.


I'd like the user to be able to double click the record selected in the subform, but I can't seem to find the event I would use to put my gotorecord command into to trigger on the double click on the subform.

Anybody have any ideas how to make Private sub for a double click event on a subform? I understand subforms are controls, not forms -- which means the double click event is not accessible from the form properties.

Any advice is appreciated.

I'd use the events of a control or controls on the subform ("double click on the project ID to move main form to that record").

@ mhorner

Your set up seems upside down to me with the list of projects shown in an unbound subform, and the individual project in the main form.

The normal set up is to display the list of projects in the parent form, and bind the subform to the parent form so whenever you select a project record in the parent form, the system will automatically display the project record in the sub form.

Otherwise, I agree with Paul to use a double click event. If ProjectID is hidden, use the double click on another control(s).

pbaldy said:

I'd use the events of a control or controls on the subform ("double click on the project ID to move main form to that record").

Click to expand...

Thank you! Unfortunately, this is exactly where I'm looking for guidance - in how to do exactly this. I tried referencing the field name, ID, as seen below:


I also tried referencing the subform directly:

You'd use the textbox properties in design view:

http://www.baldyweb.com/FirstVBA.htm

I'm sorry if I offended you, it wasn't my intention. It appeared you were trying to type events directly into the VBA editor, I was trying to direct you how to enter them from design view. If you want to attach the db here, I'll show you.

Sorry, if I could retract reporting the post -- I would.

I honestly did take offense as I was asking for guidance in how to set up a subroutine on the double click event of a subform and provided my attempt. The response was a link to "My First VBA" and told me to use design view to change the textbox properties. This akin to saying "just open access and change the property." I actually chuckled out loud and thought you were trolling me.

I'm over it, sorry for reporting.

No worries. I was trying in the previous post to say that I'd use the events of a textbox within the subform, not the subform itself. With 20 posts I wasn't sure where you were knowledge-wise.

The subform itself does have a double click event, but I don't think it would be specific enough to tell you what record was clicked on. You could maybe use the current event of the subform, which would tell you when the user changed records. That would change the main form whenever/however they changed records (clicking on a record, using navigation controls, etc).

Lunchtime for bonzo, let me know if that helps or gives us a direction to go in.

pbaldy said:

No worries. I was trying in the previous post to say that I'd use the events of a textbox within the subform, not the subform itself. With 20 posts I wasn't sure where you were knowledge-wise.

The subform itself does have a double click event, but I don't think it would be specific enough to tell you what record was clicked on. You could maybe use the current event of the subform, which would tell you when the user changed records. That would change the main form whenever/however they changed records (clicking on a record, using navigation controls, etc).

Lunchtime for bonzo, let me know if that helps or gives us a direction to go in.

Click to expand...

Thank you much! I ended up resolving this, but not easily. I did not use the double click event. As you said, it was not specific enough to identify what exact record was selected in the subform. I was using the subform in datasheet mode, so no other controls were used. I actually backtracked and tried what Cronk suggested and got it working within a few minutes. Thank you much for the help, pbaldy.

Cronk said:

@ mhorner

Your set up seems upside down to me with the list of projects shown in an unbound subform, and the individual project in the main form.

The normal set up is to display the list of projects in the parent form, and bind the subform to the parent form so whenever you select a project record in the parent form, the system will automatically display the project record in the sub form.

Otherwise, I agree with Paul to use a double click event. If ProjectID is hidden, use the double click on another control(s).

Click to expand...

Thanks, Cronk. You nailed it right on the head here. I swapped them around such that the main form was bound to the table query. I set the form to split view, and put the subform on the bottom half -- bound to the table itself. Now, when a record is selected from the top half of the split form, the subform on the bottom half automatically goes to the selected record. There was no code even required, just had to link the parent and child fields.

Thanks again for all of your help and patience.

I'm glad you got it resolved but I'm confused by

mhorner said:

I was using the subform in datasheet mode, so no other controls were used.

Click to expand...

A subform in datasheet mode still has controls whose events can be used.

pbaldy said:

I'm glad you got it resolved but I'm confused by



A subform in datasheet mode still has controls whose events can be used.

Click to expand...

The subform source object was a query, and I had it displayed in datasheet view (not form view), so there were no additional controls being used on the subform to use an event to trigger on (like a command button or combo box), only the subform's form events -- which wasn't giving me the result I wanted.






There was more wrong with that strategy than meets the eye. Because the sub form was referencing a query and the form itself was bound to the table, you can't link the master and child fields without having the subform query only show you the record which was selected on the main form. In order to have the query show the full list of records, you'd have to unlink the master and child fields.


I gave up on that route and tried Cronk's suggestion which was supremely easy. The main form is bound to the query to show the list of records. The subform is the actual data entry form and is bound to the datatable. The master and child fields are linked and whenever the main form record changes, the subform data entry form automatically points to the selected record so the user can edit the record. Simple and elegant.