By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,678 Members | 2,658 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Finding a Specific Record Programmatically

ADezii
Expert 5K+
P: 8,607
  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
Jun 24 '07 #1
Share this Article
Share on Google+
7 Comments


dima69
Expert 100+
P: 181
  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
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 ...
Jul 26 '07 #2

ADezii
Expert 5K+
P: 8,607
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.
Jul 26 '07 #3

P: 3
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
Mar 26 '08 #4

P: 3
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
Mar 26 '08 #5

ADezii
Expert 5K+
P: 8,607
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.
Mar 26 '08 #6

truthlover
100+
P: 107
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!!!
Mar 31 '08 #7

P: 1
Hello. The First solution works for me very well. Thanks for sharing and saving me from long nightmares!!! Great!!!!!
Jun 14 '16 #8