- 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.
[CODE=vb]Private Sub cboFind_AfterUp date()
Application.Ech o False
Me![EmployeeID].SetFocus
DoCmd.FindRecor d cboFind
cboFind.SetFocu s
Application.Ech o True
End Sub
[/CODE] - 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.
[CODE=vb]Private Sub cboFind_AfterUp date()
Dim strSQL As String
strSQL = "[EmployeeID] = " & Me![cboFind]
DoCmd.ApplyFilt er wherecondition: = strSQL
End Sub[/CODE] - Using the RecordsetClone (most efficient) - This approach is the most efficient and uses the Form's RecordsetClone to refer to the Form's Recordset.
[CODE=vb]Private Sub cboFind_AfterUp date()
Dim strCriteria As String
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClo ne
strCriteria = "[EmployeeID] = " & Me![cboFind]
rst.FindFirst strCriteria
Me.Bookmark = rst.Bookmark
End Sub[/CODE]
-
-
A little comment to the last method.Originally posted by ADezii
Setting the bookmarkwhen the form record is already the one we are looking for may cause Access to crash.Code:Me.Bookmark = rst.Bookmark
So I would add the check likeCode:If Me![EmployeeID] <> Me![cboFind] Then ...
-
Excellant point dima69 - I was totally unaware of this, but will definately keep it in mind now! Thanks.Originally posted by dima69Comment
-
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?
ThanksComment
-
Hi,Originally posted by ADezii
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.
ThanksComment
-
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.Originally posted by rk2008Comment
-
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!!!Comment
-
Hello. The First solution works for me very well. Thanks for sharing and saving me from long nightmares!!! Great!!!!!Comment
Comment