Finding a Specific Record Programmatically

Originally Posted by ADezii

1. Use the FindRecord Method of the DoCmd Object (least efficient) - The simplest approach for finding a specific Record is to create an Event Procedure for the Combo Box that mirrors each step of the generic Find process.
   
   
   Expand|Select|Wrap|Line Numbers

   1. Private Sub cboFind_AfterUpdate()
   2.   Application.Echo False
   3.  
   4.   Me![EmployeeID].SetFocus
   5.  
   6.   DoCmd.FindRecord cboFind
   7.  
   8.   cboFind.SetFocus
   9.  
   10.   Application.Echo True 
   11. End Sub
   12.  
2. Using the ApplyFilter Method of the DoCmd Object (more efficient) - A more efficient approach is to use a Filter to select a Record directly from the Form's Recordset. The Applyfilter method lets you apply a Filter to a Table, Form, or Report to restrict or sort the Records in the Table or in the underlying Recordset of the Form or Report. You can specify a saved Query as the Filter using the filtername Argument, or you can enter a SQL Where Clause in the wherecondition Argument.
   
   
   Expand|Select|Wrap|Line Numbers

   1. Private Sub cboFind_AfterUpdate()
   2.   Dim strSQL As String
   3.  
   4.   strSQL = "[EmployeeID] = " & Me![cboFind]
   5.  
   6.   DoCmd.ApplyFilter wherecondition:= strSQL
   7. End Sub
3. Using the RecordsetClone (most efficient) - This approach is the most efficient and uses the Form's RecordsetClone to refer to the Form's Recordset.
   
   
   Expand|Select|Wrap|Line Numbers

   1. Private Sub cboFind_AfterUpdate()
   2.   Dim strCriteria As String
   3.  
   4.   Dim rst As DAO.Recordset
   5.  
   6.   Set rst = Me.RecordsetClone
   7.  
   8.  
   9.  
   10.   strCriteria  = "[EmployeeID] = " & Me![cboFind]
   11.  
   12.   rst.FindFirst strCriteria
   13.  
   14.   Me.Bookmark = rst.Bookmark
   15. End Sub

Originally Posted by dima69

A little comment to the last method.
Setting the bookmark

Originally Posted by ADezii

Excellant point dima69 - I was totally unaware of this, but will definately keep it in mind now! Thanks.

Originally Posted by rk2008

Hi,
I was looking at your 3rd example and it is similar to what I'm using for my forms. But I have a problem, If one person opens form A and select one of the record from the list it works fine. Now at the same time if second person open the same form A from the same copy of MSAccess the first form A starts pointing at the new record also and save changes to the newly selected record. How can I prevent this happen, so that each instance of form A points to its selected record?
I'm using MSSQL ODBC linked tables inside MS Access.

Thanks