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

Put this behind your sub form

Private Sub Form_Click()

Dim rst As DAO.Recordset

Set rst = Me.Parent.RecordsetClone

rst.FindFirst "ID=" & Me.ID

If Not rst.NoMatch Then

Me.Parent.Bookmark = rst.Bookmark

End If

End Sub

Answer 3

Thanks for your response. I wasn't sure what you meant by "Put this behind your sub form", so I guessed. What I did was I went to layout view. Then I went to the property sheet for the subform and clicked the three dots next to "On Enter" because "On Click" was not available. Then I clicked "Code Builder." This opened up visual basic and created a module called "Private Sub SubFormPreviousSiteWork_Enter()" and I changed "Enter" to "Click". I pasted your code and put code stops so that I could see if the code ran when I click on a row inside the sub form.

Nothing happens when I click in the subform. Furthermore, when I change my combo box which usually lets me jump to the record I enter into it, I receive the error: "The expression you entered refers to an object that is closed or doesn't exist. For example, you may have assigned a form to a Form object variable, closed the form, and then referred to the object variable."

Is the issue where I am putting the VBA procedure? As I mentioned before, I am familiar with VBA in MS Excel, but this is slightly different.

Thanks again for your response!

Answer 4

As Ken says, you could change the sub-form to a continous form.

Next (still in design view)

right click in the black section at the bottom of the form and open the Properties Box

Ensure that in the Format Coloumn the Record Selector row = Yes

In the Event Coloum click the OnClick row and select Build (...)

Select Code Builder

You will see something like this

Private Sub Form_Click()

End Sub

Between those two lines add the code I gave so it looks like this

Private Sub Form_Click()

Dim rst As DAO.Recordset

Set rst = Me.Parent.RecordsetClone

rst.FindFirst "ID=" & Me.ID

If Not rst.NoMatch Then

Me.Parent.Bookmark = rst.Bookmark

End If

End Sub

** Note I assume you have a control called ID in the main and sub forms.  If not then change the code to whatever it is called

Save and go back to form view

Click the rRecord Selector (the blue box on the left of the record row)

This will take the main form to whichever record was selected in the subform

Answer 5

It looks like using continuous forms will do the trick. The funtion for what I want done can be completed using continuous forms, now I just need to work on making it look nice. Thank you!