473,320 Members | 1,699 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,320 developers and data experts.

Finding a Specific Record Programmatically

ADezii
8,834 Expert 8TB
  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
7 79534
dima69
181 Expert 100+
  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
8,834 Expert 8TB
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
rk2008
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
rk2008
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
8,834 Expert 8TB
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
107 100+
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
Hello. The First solution works for me very well. Thanks for sharing and saving me from long nightmares!!! Great!!!!!
Jun 14 '16 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Ian | last post by:
I want to open a form at a particular record, but I think I'm running into problems because the recordsource query is executing asynchronously. In the form's open event I use...
3
by: bjaj | last post by:
Hi I got a form where I type a number. After taht I split it in two. Then I search in a table if there are any record that match the two numbers and not yet been completed. The code: ...
3
by: DavidB | last post by:
New to .net....sorry if this seems repetitive I have a dataset ordered by date (SQLDataAdapter SelectCommand uses Order By) and want to find a record by a UniqueID(Identity Column). Then I want...
2
by: nkoske | last post by:
I have a form with a subform in datasheet view and I have a event on one of the fields in the subform, so that the user can double click it and go to expanded information based on that field. ...
2
by: Timbo | last post by:
Hi there, I’m not used to working in VB and I think this situation calls for excactly that. I use Access 97 SR-2. My first table is a table containing all the Tickets I got. The field ”Ticket”...
4
by: BerkshireGuy | last post by:
Is there a way to create a shortcut to open a MS Acccess DB and have it go to a specific record? In my DB, when a user delegates a record to another users, I currently send out an email to the...
3
by: cmp80 | last post by:
I have a database that I am using to store student data. There are two tables and two forms to display the data: Tables tbStudent tbRE Forms fmStudent fmRE
1
by: cvillav | last post by:
Hello, I am new to Access and this forums, I have two tables and two forms to display the data. Tables Tbuilding Tcriteria Forms fmbuilding
8
by: Redbeard | last post by:
I have a button on a popup form that when clicked runs a code to duplicate the current record that I am viewing on my main form. It first asks me to enter a new number (primary key) into a pop-up...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.