Navigate Form to Specific Record Based on Clicking a Record in Subform

Question

Hi,

I am new to MS Access, but I have extensive use with MS Excel and VBA (although VBA in Access seems to be slightly different).

I am creating a database to monitor construction progress for a wind farm. We are building 100 wind turbines and each turbine has multiple construction milestones (e.g., excavation, rebar installation, concrete pours, etc.). I have a form named "Foundation Completion" which is used as an interface between the underlying table database, also named "Foundation Completion", and the user. The form includes combo and text boxes, table subforms, and PivotTable subforms. The combo and text boxes are used to view, change, and input data into the underlying table database. The table subform of interest lists "previous site work". See below for an example of what the subform looks like:

Say I am looking at Turbine 89. The subform is filtered by turbine by linking master and child fields between the main form and subform. So when the main form is set at looking at the excavation at Turbine 89, the subform will show all previous work done at Turbine 89 such as the excavation, rebar installation, and concrete pours. Each of those work items have a record ID which is the same as the record ID listed in the underlying database AND is the same as the record the main form would be using if it was focusing on the specified task.

What I want is to be able to jump the main form to the record by double clicking on a record in the "previous site work" subform. Currently, I have a combo box where I can type in the record I want to jump to, but that is clunky.

I hope that VBA is not necessary to complete this request, but I am not opposed to it.

This is my first time posting a question to the community so please let me know if you need more information.

Thanks for your help!

Answer 1

If you are going to use the Click event the code would need to be called by the event of each control in the subform.  You could do this by wrapping the code in a function in the subform's module and calling the function as the Click event property of each control.

Alternatively you could  try the subform's Current event procedure, but I'd be concerned that this might result in a perpetual loop as when the parent form move's to a different record, the subform will be requeried by the linking mechanism, and its Current event procedure would execute again, and so on ad infinitum.

In this sort of scenario I would normally add a little command button, with a suitable image as its Picture property, to one end of the detail section, but this requires the subform to be in continuous forms view, not datasheet view.

Answer 2

Wayne,

I was unable to get your code to work, but that is probably just a lack of understanding VBA with access. I am instead going to move forward with using continuous forms until I become more comfortable with VBA in Access. Thanks for you time and help!