Anyway - I thouhgt I was learning. But I am trying to use a recordset clone to "jump" to a record. I think this should be very simple - but it's killing me!
I have a form that displays several hundred records. With UA member help, I have a "jump to" list that allows me to jump to the first record of each letter of the alphabet. Now I also want to be able to pick a name from a combo box, and jump to that person's record.
So I set up this code, based on the alphabetical code from earlier:
Per_ID is the autonumber key in the tbl_Personnel table. jumpToID is the combo box that displays names, with the bound column being the Pers_ID. The form actually uses a query of several linked tables - The query uses asterices (*) to get all the table columns of each table. I get a data type mismatch when this code fires on the line: rs.FindFirst "tbl_Personnel.Pers_ID = '" & varID & "'"
CODE
Private Sub jumpToID_AfterUpdate()
btnSortByEntryDate.Value = False
Form.OrderBy = "[Pers_lastName],[Pers_firstName]"
Form.OrderByOn = True
Call IDfilter([jumpToID])
End Sub
Private Sub IDfilter(varID As Integer)
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[tbl_Personnel].[Pers_ID] = '" & varID & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
btnSortByEntryDate.Value = False
Form.OrderBy = "[Pers_lastName],[Pers_firstName]"
Form.OrderByOn = True
Call IDfilter([jumpToID])
End Sub
Private Sub IDfilter(varID As Integer)
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[tbl_Personnel].[Pers_ID] = '" & varID & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
P.S. this is the code that work for the alphabet jump to:
CODE
Private Sub jumpTo_AfterUpdate()
btnSortByEntryDate.Value = False
Form.OrderBy = "[Pers_lastName],[Pers_firstName]"
Form.OrderByOn = True
Call InitialFilter([jumpTo])
End Sub
Private Sub InitialFilter(strInitial As String)
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "Left([Pers_lastName],1) = '" & strInitial & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
btnSortByEntryDate.Value = False
Form.OrderBy = "[Pers_lastName],[Pers_firstName]"
Form.OrderByOn = True
Call InitialFilter([jumpTo])
End Sub
Private Sub InitialFilter(strInitial As String)
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "Left([Pers_lastName],1) = '" & strInitial & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
