Unanswered: Me.Bookmark = Me.RecordsetClone.Bookmark = Form opening on new record
I am attempting to open a form based upon a user's selection of a record that appears in a listbox on another form. Everything seems to be working fine, but when I attempt to open up the form on the record that I want to go to, the form is opening on a new record, and not on the record in the recordset that I have bookmarked. I am using the code below:
I myself use a lot of List Boxes to show data. But, I use the Double-Click Event of the List Box to open a form to the correct record. Here is an example if you would like to change your code:
Private Sub ListBox_DblClick(Cancel As Integer)
Dim ctl As Control
Dim varItem As Variant
Dim Filter As Variant
If Me.ListBox.ItemsSelected.Count = 0 Then
MsgBox "No selection was made", vbInformation, "Message"
ElseIf Me.ListBox.ItemsSelected.Count > 1 Then
MsgBox "You may only select one record to view", vbInformation, "Message"
Set ctl = Me.ListBox
For Each varItem In ctl.ItemsSelected
Filter = ctl.ItemData(varItem)
Filter = "RecordNum=" + Filter
DoCmd.OpenForm "frmEnrollmentRevisions", , , Filter
If necessary, you can add a close event after the Open Form event to close the initial form. If you have any questions let me know.
Thanks for the Response, but it is not exactly what I am looking to do.
For one thing, I did not really want to open the form using a filter.
If that is the only choice I have, then I suppose I could swing it. But also, the RecordNum field is not displayed on my form--I could do it with a filter and an invisible field, but I would rather not clutter the form with invisible fields if they are not neccessary, and again I would like to avoid moving to that record through the use of a filter.
Perhaps I wasn't clear enough about the problem the first time around. Maybe this clarifies it?
Any other ideas?
I set up a quick database with your idea in mind and got it to work. There are a few variations you can try but I think the easiest will be the following:
Change your bound column on your List Box to 11 then use the following code.
Private Sub cmdGotoRecord_Click()
If Not IsNull(Me.lstRecords) Then
varRecordNum = Me.lstRecords
Forms!frmEnrollmentRevisions.RecordsetClone.FindFi rst "[RecordNum] = " & varRecordNum
Forms!frmEnrollmentRevisions.Bookmark = Forms!frmEnrollmentRevisions.RecordsetClone.Bookma rk
DoCmd.Close acForm, "frmRequestRecordSelector"