NavigationSubforms (Navigate To and Select RecordSource)

[Bluecider]

I just wanted to share my adventures about Navigation Subform controls and manipulations.

In my current design, I have a MAIN form, and within that form is embedded a NavigationSubform control.

There are two different NavigationSubform items in this example.

#1) The first one is presented as a datasheet within the NavigationSubform

#2) The second one is presented as a form within the NavigationSubform.

Objective is to allow the selected record in #1 above (based on dblclick by the user on the record row), be shown
as the record after calling the form from #2.

This is not the most elegant way to do this I'm sure, so please be kind with your alternative suggestions.


STARTING FROM #1 Datasheet view above

Sub Form_DblClick(Cancel As Integer)

Dim REC As Integer  'Defines variable to capture record number selected


REC = Me.JOB_ID  'Stores selected record number

'Calls into NavigationSubform control the JobEntry FORM
Forms![frm_MAIN].NavigationSubform.SourceObject = "frm_JOBENTRY"  

'Sets the record to display in this form as the record which was selected when initiating the dblclick
Forms![frm_MAIN].NavigationSubform.Form.RecordSource = "SELECT DISTINCTROW tbl_JOBS.* FROM tbl_JOBS WHERE (tbl_JOBS.JOB_ID = " & REC & ");"

End Sub

I hope this helps people looking to do something similar, as I had to rack my head around how to handle especially the second part.