Find data on Access form
-
I would like to find data on my access form.
I try with this code:
Dim rs As Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "myField = " & Me!Value
If Not rs.NoMatch Then _
Me.Bookmark = rs.Bookmark
but is to slow, my form have 30000-40000 records. Does anyone know a faster metod.
Thanks!
-
This was removed by the editor as SPAM
-
Have you tried the SEEK method of the recordset object? i.e., rs.Seek
-
SEEK does not work: Operation does not suported for this type of object!
On form recordset index is not define and I use SQL server link table. Provider for SQL server not allow Seek.
-
Is the field you are doing to find on an indexed field? 30-40k still shouldn't take that long with the proper indices.
Richard
-
Yes, the search field is indexed. On this moment I have 36000 records in table, and search on form is 35 sec long.
SQL Server is instaled on 900 Mhz AMD with 700 MB RAM and new fast 80GB HD. Client is the same.
-
I think what might be happening is it is making a true copy of all 36k records.
One thing that you can try is doing a filter on the form. I think this will use the index more effectively.
Me.Filter = "value = " & Me!Value
Me.FilterOn = True
Be careful with the way the form cycles when the user tabs. If you allow the user to add records to the recordset and they tab off of the last field they will go to the next record. When you use the filter there is only one record, so you will be going to a blank screen. You can control the form cycling on the Other tab of the Form object.
Good Luck,
Richard
-
Thanks Richard, I will try
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply