Goto a record based on selected record in subform datasheet

[JRRyan]

OK, the title prety much explains things, but for clarity:

I have a form ("Master Form") and a subform ("Basic Skills subform") displaying as a data sheet. The Master form is bound to a dynamic select query created by a set of filters in a separate switchboard style form. The subform is bound to the main data table with no filtration.

The purpose of the subform is to provide a "full view" of the primary data, and I would like to add the functionality to make the "Master Form" goto the record that is selected in the subform datasheet. This is less redundant than it sounds as there is other data from separate tables that is represented in the master form but not in the subform. Plus for data entry, it make things easier to have the data laid out in a form rather than a data sheet.

In my best attempt at making this work (best but not successful), I added the following to the ******* event in a textfield in the subform:

Private Sub CRN_Click()
With Me.Parent.RecordsetClone
    .FindFirst "CRN = " & Me.CRN
    Me.Parent.Bookmark = .Bookmark
End With
End Sub

Any bright people out there who could help make this work?

 

[JRRyan]

SOLVED Re: Goto a record based on selected record in subform datasheet

Ahh! The paradox of being the smartest and stupidest person in the room at the same time! It's a quantum state of idiocy, a Humdinger's Cat!

I was getting data type mismatch errors and not understanding why, then I realised

    .FindFirst "CRN = " & Me.CRN

should be

    .FindFirst "CRN = """ & Me.CRN & """"

Meow!

 

[JRRyan]

OK, spoke too soon! The cat is out of the box and it's definitely dumb.

Everything was working fine when my data sheet was bound to a static table, but shen I tried to bind it to the same dynaic selct query that powers the main form, I get this message:

BASIC SKILLS can't find the field '|' referred to in your expression.

BASIC SKILLS is the name of my primary data table from where the source select query pulls it's data and the '|' is a vertical bar as in ||

Unfortunately, I can't find a vertical bar anywhere in my code or in my query. I've searched the ascii version as well | So, I'm guessing this is a misguided error that is returning something different to reality. If anyone has any better guesses would be grateful for the help on this one.

JR

 

[JRRyan]

OK, a little farther down the line now. The issue seems to be associated with the subform's onCurrent function where I have the following code.

With Me.Parent.RecordsetClone
    .FindFirst "[CRN] = """ & Me.[CRN] & """"
    Me.Parent.Bookmark = .Bookmark
End With

The code isn't the problem, as this worked fine when the subform was bound to a different recordsource. And binding the form and subform to the same record source isn't (directly) the issue either since pasting the same code into a ******* event in a single fild on the subform also works fine.

In fact, I have found that the code is actually working (sort of). I hadn't noticed because I was focused on changing the mainform's active record using the subform's current selection, but the form was actually taking the current (i.e. first/top of the list) record. I proved this by sorting z-a and the main record dutifully changed. however it is stuck in a loop where the subform is calling the current record, and the current record is the same as the one being called.

I think this is why there is a wierd error, which is a shame because the benefit of having the form trigger onCurrent was that I could click anywhere in the subform record and it would change. i have tried putting the code into the subform's onActivate, *******, onGotFocus, and onSelectionChange but no joy.

this has become a real logic problem now, any work arounds would be much appreciated!

JR