UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Killer Clone    
 
   
rmsphoto
post May 24 2012, 09:19 PM
Post #1

UtterAccess Enthusiast
Posts: 63
From: Indiana, USA
UtterAccess is Awesome! I have learned so much, and am nearly ready to launch the database I have been working on for months...

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



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
Go to the top of the page
 
+
theDBguy
post May 24 2012, 09:37 PM
Post #2

Access Wiki and Forums Moderator
Posts: 47,901
From: SoCal, USA
Hi,

What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

You're getting a type mismatch error because you are enclosing the ID variable in quotes when it's probably a number. Try simply:

rs.FindFirst "[tbl_Personnel].[Pers_ID] = " & varID

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)
Go to the top of the page
 
+
rmsphoto
post May 24 2012, 09:52 PM
Post #3

UtterAccess Enthusiast
Posts: 63
From: Indiana, USA
Sorry - it was 2007

I thought it had to be a simple syntax issue. I tried several different ways - I get pretty tied up when it comes to single and double quotes.

Thanks for a perfect solution once again! (IMG:style_emoticons/default/notworthy.gif)
Go to the top of the page
 
+
theDBguy
post May 24 2012, 09:58 PM
Post #4

Access Wiki and Forums Moderator
Posts: 47,901
From: SoCal, USA
Hi,

QUOTE (rmsphoto @ May 24 2012, 07:52 PM) *
Sorry - it was 2007

No worries. I just had to mention it for consistency. Not a big deal.



QUOTE
Thanks for a perfect solution once again! (IMG:style_emoticons/default/notworthy.gif)

(IMG:style_emoticons/default/yw.gif)

Glad we could help. Good luck with your project.
Go to the top of the page
 
+
« Next Oldest · Access Forms · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 18th May 2013 - 09:01 AM