Finding a Specific Record Programmatically

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

A little comment to the last method.
Setting the bookmark when the form record is already the one we are looking for may cause Access to crash.
So I would add the check like

A little comment to the last method.
Setting the bookmark

Expand|Select|Wrap|Line Numbers

1. Me.Bookmark = rst.Bookmark

when the form record is already the one we are looking for may cause Access to crash.
So I would add the check like

Expand|Select|Wrap|Line Numbers

1. If Me![EmployeeID] <> Me![cboFind] Then ...

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

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?

Thanks

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

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

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

I imagine that a Client/File Server architecture with multiple Front End Databases linked to a single Back End would solve this problem. Each Front End would now have its own, independent Form A instances, and this type of conflict should not then be a problem.

This looks like something I've been trying to do, but I dont know how to implement it to try it out.

I'm already using the first example, but what I want is a pull down list to select a record. I have that working in a different form, and it's working perfectly, but I cant get it to work on the primary form.

Is it because I'm trying to pull up the record by it's Primary Key, that I've been unsucessful?

Will that third example solve that? If so, how do I implement it? If not, is there a way to do this?

BTW there was an explaination that I tried to use, but it didnt work (I posted to the thread 3/28 or 3/29.

Thanks!!!

Hello. The First solution works for me very well. Thanks for sharing and saving me from long nightmares!!! Great!!!!!