Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   TechHelp   Forums   Help   Contact   Merch   Join   Order   Logon  
 
Back to Access Developer Forum    Comments List
Upload Images   @Reply   Bookmark    Link   Email   Next Unseen 
VBA for Ctrl f aka find
Gregory Clancey 
    
3 years ago
I have a continuous Form based on a many-to-one source with the "one" displayed in the Header Section and the "many" in the continuous Detail Section.

A TextBox In the Header displays the key field and I use this with DoCmd.GoToRecord. . . in a set of Command Buttons to navigate through the records.

With the focus in this TextBox, I've always used the keyboard shortcut "Ctrl-f" to locate a specific record. At one time I had the habit of doing this with a "search" Textbox which used some simple VBA triggered at its OnLostFocus Event to accomplish the same thing. Damned if I can remember the Function. Can you help?
Gregory Clancey OP  @Reply  
    
3 years ago
Got it!
Recordset.Clone bookmark after Recordset.clone "FindFirst"
Gary James  @Reply  
      
3 years ago
Here's a global function I wrote to perform a similar record selection.

Public Enum FindRecordType

   frtFindByAutonumber
   frtFindFirstRecord
   frtFindLastRecord
   frtFindByOffsetValue
   frtFindByIndexPosition
  
End Enum

Public Sub FindFormItem(frm As Form, Optional value As Long = 0, Optional FindType As FindRecordType = FindRecordType.frtFindByAutonumber)
  
   Dim idx As Long
   Dim rs As DAO.Recordset
   Set rs = frm.RecordsetClone
  
   ' if table empty
   If rs.EOF Or rs.BOF Then Exit Sub
  
   Select Case FindType
      Case FindRecordType.frtFindByAutonumber
         rs.FindFirst "ID = " & value
         If (rs.NoMatch = False) Then
            frm.Bookmark = rs.Bookmark
         End If
        
      Case FindRecordType.frtFindFirstRecord
         rs.MoveFirst
         frm.Bookmark = rs.Bookmark
        
      Case FindRecordType.frtFindLastRecord
         rs.movelast
         frm.Bookmark = rs.Bookmark
        
      Case FindRecordType.frtFindByOffsetValue
         rs.MoveFirst
         rs.AbsolutePosition = value
         frm.Bookmark = rs.Bookmark
        
      Case FindRecordType.frtFindByIndexPosition
         idx = value
         If (idx < rs.RecordCount - 1 And idx >= 0) Then
            rs.Move idx
         Else
            rs.Move rs.RecordCount - 1
         End If
         frm.Bookmark = rs.Bookmark
        
   End Select
  
   rs.Close
   Set rs = Nothing

End Sub

Gregory Clancey OP  @Reply  
    
3 years ago
Thanks for this, Gary. There's a bit here and I want to digest it all slowly to see exactly what the code is doing.

My approach has been to Set a RecordSetClone of the underlying table and use 'FindFirst = "searchString" ' to locate my search string in that clone. If found, BOOKMARK its place in the clone and define the underlying table's BOOKMARK equal to the clone's. The Form instantly snaps to the record of my search string.

I appreciate your solution and expect to learn something new as I come to fully understand the code. Thanks.
Gary Becker  @Reply  
     
3 years ago
Here's some code I have used for years to go to a record from a combo.
Private Sub cboMoveTo_AfterUpdate()
    Dim rst As Recordset
    Set rst = Me.RecordsetClone
    rst.FindFirst "[emnum] = " & Me![cboMoveTo] & ""
    If Not rst.NoMatch Then Me.Bookmark = rst.Bookmark
    Me.cmdExit.SetFocus
    Me.cboMoveTo.SetFocus
    
End Sub
Gary Becker  @Reply  
     
3 years ago
But a lot more recently I started to use this code'and it seems to do the same thing. Find and go to a record from a combo. I think I initially let the wizard create a macro for finding the record from the combo, then I used the little tool to convert the macor to VBA and this is what I got. If I remember correctly. :)

    DoCmd.SearchForRecord , "", acFirst, "employeenumber=" & """" & [EmployeeNumberCombo] & """"
Gary James  @Reply  
      
3 years ago
Gregory, I wrote this function for use on several forms that have a main form and a continuous form. The main forms allow a user to add or edit the records stored in a table related to that form; while the continuous form allow the user to delete records from the table.  Selecting a record in the main form by way of the navigation buttons, or in the continuous form by way of clicking on the record, causes the other form to track and highlight the record in the continuous form, and update the fields in the main form.   The different types of record section supported by the function were needed to reposition the selected record should the user Add, or Delete a record, that would require the selected record to change.
Gregory Clancey OP  @Reply  
    
3 years ago
Yes, Gary, I see. I've had more time to study the code and I've great admiration for the clarity and competence behind it. I think both your and my process use the same basic tactic, but yours seems to cast the net further wider and from different angles. My need was less complicated, I think, and the compact nature of my simple subroutine sufficed. I plan, having said that, to incorporate your code into my practice DB to see if I can get the results for which you designed it. Thanks so much.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.
 

Next Unseen

 
New Feature: Comment Live View
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2026 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 5/2/2026 7:10:18 AM. PLT: 1s