weird behavior of subform that is used as a record selector

[delikedi]

I have a form that mimics a split form. I do not use split forms since my control over their layout is limited. My form is basically a normal form with a subform in it. The subform displays the same records in datasheetview. When the user selects a record on the subform, the subform runs the following procedure as its current event procedure, and moves the parent form to the record the user selected.

10 Private Sub Form_Current()
20       Dim rstKlon As Recordset
30       If Me.Parent.pstrPKField <> vbNullString 'just a checkpoint
40           Set rstKlon = Me.Parent.RecordsetClone
50           rstKlon.FindFirst "NoteID=" & Me![NoteID]
60           Me.Parent.Bookmark = rstKlon.Bookmark
70       End If
80    Set rstKlon = Nothing
90 End Sub

the code works as intended, but not always. The main form always moves to the the record the user clicked, no problem there. But the very subform that the user clicked on does not always move to the clicked record. Sometimes it stays at its current position, sometimes it moves to an unrelated record.

As I watch the program flow, I notice that sometimes the execution of line number 60 is not followed by line 70. It jumps back to line 10, and is executed again. I would expect this behavior if it were the subform's cursor that moved to a different record, but it isn't. Line 60 only moves the cursor of the main form. There's no link between the subform and the main form (their recordsources are different and no parentID-childID link is established.
Any ideas?

 

[delikedi]

Apparently moving to a different record of the main form, thus causing it to be refreshed, also causes the subform to be refreshed, even if it is not bound to any field of the main form. The subform gets refreshed just because it resides on the main form. I find this interesting, since the same thing dos not happen with listboxes. A listbox whose row source is dependent on a field of the main form has to be manually refreshed. I expected the same for the subform but I guess I was wrong.

 

[tjones]

I DID IT!!! Thanks to everyone for their help.

Solution:

Create a query based off the fields in the table of the form you want the subform to display. Be sure to include on both the main form and in the query:

1. The autonumber in the table (RecordID)
2. The information to limit what displays in the subform (StudentID)
​

Create the subform off the query and set it to datasheet/allow datasheet view=Yes (This will look like a listbox or the datasheet portion of a split form)

Once you have the subfom displaying the information the way you want.

In the auto RecordID field set format to Display as Hyperlink=Screen only

on the Form format on click enter the attached Macro (change the field name to match your entry) View attachment 45075

Save the form and close the database then reopen - the form should work like a split form. You select the RecordID in the subform to load the record to the main form.