Listbox Code

[PC User]

I have a form "frmMAINEntry" with a tabcontrol with several pages. The
form's Record Source is a query "qryMainData"; however, on two of the
pages there's a difference. On the page "Program List" I have a
listbox that has a Row Source to table "tsubProgramList" and on the
page "Program Details" I have a subform that's Record Source is also
"tsubProgramList". The two pages of the tabcontrol are:
"ctlProgramDetails" and "ctlProgramList".

The table tsubProgramList contains these fields:
ProgramID..............AutoNumber
ProgramDescription...Text
Facility...................Text
ProgramCoordinator...Text

I'm trying to develop code that will allow me to doubleclick on an item
in the listbox, change pages and display the selected record in the
subform that's on the other page. See code below:

Code:
=================================================
Private Sub lstPrograms_DblClick(Cancel As Integer)

Dim strSelectID As String
Dim strListPick As String

Set strSearchID =
Forms![frmMAINEntry]![fsubProgramList].Form.[ProgramID]

strListPick = str(Nz(Me![lstPrograms], 0))
strSelectID.Bookmark = strListPick
Me![ctlProgramDetails].SetFocus

End Sub
=================================================
Someone's help would be greatly appreciated.

PC

 

[PC User]

I changed my approach to this by using recordsets, but I get an error
on the reference to the subform. Can someone help me?
Code:
=================================================
Private Sub lstPrograms_DblClick(Cancel As Integer)
Dim rs As Object
Dim db As Database
Set db = CurrentDb()
Set rs = db.OpenRecordset("tsubProgramList", dbOpenDynaset)
rs.FindFirst "[ProgramID] = " & str(Nz(Me![lstPrograms], 0))
If Not rs.EOF Then Forms![frmMAINEntry]![fsubProgramList].Form.Bookmark
= rs.Bookmark
Me![ctlProgramDetails].SetFocus
End Sub
=================================================

Thanks,
PC

 

[Brendan Reynolds]

Bookmarks are created dynamically, so that even if the recordset you open
contains exactly the same records as the subform, they will not have the
same bookmarks. The only time you can count on two recordsets having the
same bookmarks is when one is a clone of the other.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[PC User]

According to some instuctions that I've read, the reference to the
subform is done to the subform control (alias container) and not to the
subform directly. I mean a reference like this:
'Forms.Item("ParentFormControlName").Controls
("SubformControlName").Form However, I still get an error. Any
suggestions for solution?
==========================================
Private Sub lstPrograms_DblClick(Cancel As Integer)
Dim rs As Object
Dim db As Database
Set db = CurrentDb()
Set rs = db.OpenRecordset("tsubProgramList", dbOpenDynaset)
rs.FindFirst "[ProgramID] = " & Str(Nz(Me![lstPrograms], 0))
If Not rs.EOF Then
Forms![frmMainEntry]![fctlProgramList].Form.Bookmark = rs.Bookmark
Me![ctlProgramDetails].SetFocus
Call HideNavButtons
End Sub
==========================================
Thanks,
PC

 

[Brendan Reynolds]

Please read my previous response. The problem has nothing to do with how you
are referring to the subform, it is how you are attempting to use bookmarks.
Bookmarks are created dynamically. The set of bookmarks associated with the
recordset that you are opening have no connection, none whatsoever, with the
set of bookmarks associated with the form's recordset. Two recordsets share
the same bookmarks *only* when one recordset is a clone of the other. To use
bookmarks in this way, do *not* open a separate recordset, use the form's
RecordsetClone property. Something like ...

Set rs = Forms![frmMainEntry]![fctlProgramList].Form.RecordsetClone

See the help topic 'Bookmark Property (DAO)'

<quote>
If you use the Clone method to create a copy of a Recordset object, the
Bookmark property settings for the original and the duplicate Recordset
objects are identical and can be used interchangeably. However, you can't
use bookmarks from different Recordset objects interchangeably, even if they
were created by using the same object or the same SQL statement.
</quote>

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[PC User]

Perhaps the misunderstanding lies in comparing the code I mentioned
earlier with the other code that I use reliably in the same form that
does not refer to a subform.
Code:
========================
Private Sub lstSearch_DblClick(Cancel As Integer)
Dim rs As Object
Set rs = Forms!frmMainEntry.Recordset.Clone
rs.FindFirst "[TrackingID] = " & str(Nz(Me![lstSearch], 0))
If Not rs.EOF Then Forms!frmMainEntry.Bookmark = rs.Bookmark
Me![ctlBackground].SetFocus
Call ShowNavButtons
End Sub
=========================
This code has not failed me and its in the same form, but a different
listbox. It appears that I don't have enough background to apply your
recommendation; so how would you rewrite the subform code to make it
work?

Thanks,
PC

 

[Brendan Reynolds]

Once again - the fact that in this code you do not refer to a subform is a
complete red herring. The difference is that in this code, the recordset is
a clone. In the code you posted previously, the recordset was not a clone.
Here's how the recordset is assigned in this code ...

Set rs = Forms!frmMainEntry.Recordset.Clone

.... and here's how the recordset was assigned in the previously posted code
....

Set rs = db.OpenRecordset("tsubProgramList", dbOpenDynaset)

.... and here, one more time, is the quote from the help file ...

<quote>
If you use the Clone method to create a copy of a Recordset object, the
Bookmark property settings for the original and the duplicate Recordset
objects are identical and can be used interchangeably. However, you can't
use bookmarks from different Recordset objects interchangeably, even if they
were created by using the same object or the same SQL statement.
</quote>

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[PC User]

Thank you Brendan Reynolds. With your help I finally got it to work.
This is the code that finally did it.
Code:
===============================
Private Sub lstPrograms_DblClick(Cancel As Integer)
Dim rs As Object
Dim db As Database
Set db = CurrentDb()
Set rs = Forms![frmMainEntry]![fctlProgramList].Form.RecordsetClone
rs.FindFirst "[ProgramID]" = " & Str(Nz(Me![lstPrograms], 0))"
If Not rs.EOF Then Forms![frmMainEntry]![fctlProgramList].Form.Bookmark
= rs.Bookmark
Me![ctlProgramDetails].SetFocus
End Sub
===============================

Thanks,
PC