Custom Search on SUBform (Reference Issue ?)

[jamie_b]

Hi,
(Sorry about a previous post with the same Title. There's no content for that post, don't know what happened there...)

I am using Access2000.

Scenario 1 - no problem, works as expected.
Scenario 2 - Need your help here.

Scenario 1: Mainform named [frmPatAuths] (no subforms).
Based on a Table with PK = [auth_id] and many other fields.
On this form I have a custom 'SEARCH_BUTTON' which takes me to another form [frmPatSearch]. On [frmPatSearch] I have a Listbox [lstPatients] which gets it's data from a query.

I have the following code for the [OnDoubleClick] event of [lstPatients]

Private Sub lstPatients_DblClick(Cancel As Integer)

DoCmd.OpenForm "frmPatAuths"
Set Recordset = Forms!frmPatAuths.Recordset.Clone
Recordset.FindFirst "[auth_id] = " & Me.lstPatients
Forms!frmPatAuths.Bookmark = Recordset.Bookmark
DoCmd.Close acForm, "frmPatSearch"

Exit_lstPatients_DblClick:
Exit Sub
End Sub

So the [OnDoubleClick] of the listbox takes me back to my mainform and to the correct record. This all works perfectly well.

Now for my problem. Forget about Scenario1 above. It was just a test. What I need is explained below:

Scenario 2:
All is the same as in Scenario 1 above, with only the following difference:

Main form [frmPatAuths]. Not based on a Table. Just a Form to hold my Subform.

I have a Subform named [frmPatAuthsSub].
Based on a Table with PK = [auth_id] and many other fields.
On this form I have a custom 'SEARCH_BUTTON' which takes me to another form [frmPatSearch]. On [frmPatSearch] I have a Listbox [lstPatients] which gets it's data from a query.

I have the following code for the [OnDoubleClick] event of [lstPatients]

Private Sub lstPatients_DblClick(Cancel As Integer)

DoCmd.OpenForm "frmPatAuths"
Set Recordset = [COLOR="Blue"]Forms!frmPatAuths!frmPatAuthsSub[/COLOR].Recordset.Clone
Recordset.FindFirst "[auth_id] = " & Me.lstPatients
[COLOR="Blue"]Forms!frmPatAuths!frmPatAuthsSub[/COLOR].Bookmark = Recordset.Bookmark
DoCmd.Close acForm, "frmPatSearch"

Exit_lstPatients_DblClick:
Exit Sub
End Sub

The problem is that the [OnDoubleClick] of the listbox takes me back to my mainform, BUT the cursor is placed at the First Record, instsead of the record I selected from the SearchForm.

I'm almost sure the problem is in my 'reference' to the subform (highlighted in BLUE above), but Access is not giving any errors. It just doesn't move to the correct record.

Any help is appreciated.

Thank you.
Jamie.

 

[SOS]

Subform syntax needs to refer to the subform container (control that houses the subform on the main form) on the main form and not the subform itself (unless the subform and the subform container are named the same).

Plus it isn't Recordset.Clone (with a period) it is RecordSetClone

DoCmd.OpenForm "frmPatAuths"
Set Recordset = [COLOR=blue]Forms!frmPatAuths!frmPatAuthsSub.[/COLOR][COLOR=red][B]Form.RecordsetClone
[/B][/COLOR]Recordset.FindFirst "[auth_id] = " & Me.lstPatients
[COLOR=blue]Forms!frmPatAuths!frmPatAuthsSub[/COLOR][COLOR=red][B].Form[/B][/COLOR].Bookmark = Recordset.Bookmark

And the .Form. part needs to stay in there AS WRITTEN (do not think you substitute a name for the .Form. part).

 

[jamie_b]

SOS, Sorted now. The ".Form" part was clearly the issue. Thank you for pointing it out.

Just for interest sake:

Plus it isn't Recordset.Clone (with a period) it is RecordSetClone

What is the difference between [RecordSetClone] and [Recordset.Clone] ?
Both does the same for me.

Thanks again.
Jamie.

 

[SOS]

What is the difference between [RecordSetClone] and [Recordset.Clone] ?
Both does the same for me.

It shouldn't. RecordsetClone is a valid property of a form, Recordset.Clone is not.