Finding a Specific Record Programmaticall y

[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.
   
   

   Private Sub cboFind_AfterUpdate()
     Application.Echo False
    
     Me![EmployeeID].SetFocus
    
     DoCmd.FindRecord cboFind
    
     cboFind.SetFocus
    
     Application.Echo True 
   End Sub
    

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.
   
   

   Private Sub cboFind_AfterUpdate()
     Dim strSQL As String
    
     strSQL = "[EmployeeID] = " & Me![cboFind]
    
     DoCmd.ApplyFilter wherecondition:= strSQL
   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.
   
   

   Private Sub cboFind_AfterUpdate()
     Dim strCriteria As String
    
     Dim rst As DAO.Recordset
    
     Set rst = Me.RecordsetClone
    
    
    
     strCriteria  = "[EmployeeID] = " & Me![cboFind]
    
     rst.FindFirst strCriteria
    
     Me.Bookmark = rst.Bookmark
   End Sub

[dima69]

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.
   
   

   Private Sub cboFind_AfterUpdate()
     Application.Echo False
    
     Me![EmployeeID].SetFocus
    
     DoCmd.FindRecord cboFind
    
     cboFind.SetFocus
    
     Application.Echo True 
   End Sub
    

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.
   
   

   Private Sub cboFind_AfterUpdate()
     Dim strSQL As String
    
     strSQL = "[EmployeeID] = " & Me![cboFind]
    
     DoCmd.ApplyFilter wherecondition:= strSQL
   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.
   
   

   Private Sub cboFind_AfterUpdate()
     Dim strCriteria As String
    
     Dim rst As DAO.Recordset
    
     Set rst = Me.RecordsetClone
    
    
    
     strCriteria  = "[EmployeeID] = " & Me![cboFind]
    
     rst.FindFirst strCriteria
    
     Me.Bookmark = rst.Bookmark
   End Sub

A little comment to the last method.
Setting the bookmark

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

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

[ADezii]

A little comment to the last method.
Setting the bookmark

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

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

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

[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?

Thanks

[rk2008]

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

[ADezii]

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.

[truthlover]

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!!!

[Moussiel]

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