Using DoCmd.FindRecord

[Ben Lahoy]

Hi!

My problem is basically in a search modal window, where the user is allowed
to make a selection on which option to take. After selecting an option and
then giving the data to search, the user clicks the "OK" button and the user
gets taken back to the "Customer" form with the correct searched record.
The function works fine except that every now and then it takes 3-5 seconds
to close the search modal window and goes back to the "Customer" form after
clicking the "OK" button

Below is the actual code for the "OK" button:
- - - - - - - - - - - - - - - - - - - - - - -
On Error GoTo OK_Click_Err
If IsNull(Forms![yCustomer Search Dialog]![By Search Type]) Then
Response = MsgBox("You Did Not Select a Record to Search For", 32, "No
Record Selected")
Else
DoCmd.SelectObject A_FORM, "Customers"
Forms![Customers]![Customer Number].SetFocus
DoCmd.FindRecord [Forms]![yCustomer Search Dialog]![By Search Type],
A_ENTIRE, False, A_DOWN, False, A_CURRENT

Select Case Me.Type_of_Search
Case 1
Forms![Customers]![CustomerSubFrm].Form![First Name].SetFocus
Case 2
Forms![Customers]![CustomerSubFrm].Form![Company].SetFocus
Case 3
Forms![Customers]![Customer Number].SetFocus
Case 4
Forms![Customers]![CustomerSubFrm].Form![Phone].SetFocus
End Select

DoCmd.Close A_FORM, "yCustomer Search Dialog"
End If
Exit Sub

OK_Click_Err:
MsgBox "Error is " & Error$ & Chr(13) & Chr(10) & "In yCust Search
OK_Button" & Chr(13) & Chr(10) & CPBSMSG, 16
Exit Sub
- - - - - - - - - - - - - - - - - - - - - - -

Aside from using "DoCmd.FindRecord" command, are there other ways to find a
record that is faster?

I have a couple of ideas but what I am asking are the ways to do it in
Access and not in MS SQL.

Using the "DoCmd.FindRecord" command seems to be slow every now and then.
It takes 3 to 5 seconds sometimes to execute.

But if you see another cause, in the code, that slows down the performance,
please let me know.



Thanks,

Ben

 

[mscertified]

Your code is strange, I have never come across anything quite like it before.
Why not use Docmd.ApplyFilter to find the record?
The speed will depend on how many records there are and whether the columns
being compared are indexed.

-Dorian.

 

[Ben]

If I use apply filter then this will filter out all other records and the
user will not be able to use the navigation bar anymore. This will not work
in this case.

Your code is strange, I have never come across anything quite like it before.
Why not use Docmd.ApplyFilter to find the record?
The speed will depend on how many records there are and whether the columns
being compared are indexed.

-Dorian.

Hi!

My problem is basically in a search modal window, where the user is allowed
to make a selection on which option to take. After selecting an option and
then giving the data to search, the user clicks the "OK" button and the user
gets taken back to the "Customer" form with the correct searched record.
The function works fine except that every now and then it takes 3-5 seconds
to close the search modal window and goes back to the "Customer" form after
clicking the "OK" button

Below is the actual code for the "OK" button:
- - - - - - - - - - - - - - - - - - - - - - -
On Error GoTo OK_Click_Err
If IsNull(Forms![yCustomer Search Dialog]![By Search Type]) Then
Response = MsgBox("You Did Not Select a Record to Search For", 32, "No
Record Selected")
Else
DoCmd.SelectObject A_FORM, "Customers"
Forms![Customers]![Customer Number].SetFocus
DoCmd.FindRecord [Forms]![yCustomer Search Dialog]![By Search Type],
A_ENTIRE, False, A_DOWN, False, A_CURRENT

Select Case Me.Type_of_Search
Case 1
Forms![Customers]![CustomerSubFrm].Form![First Name].SetFocus
Case 2
Forms![Customers]![CustomerSubFrm].Form![Company].SetFocus
Case 3
Forms![Customers]![Customer Number].SetFocus
Case 4
Forms![Customers]![CustomerSubFrm].Form![Phone].SetFocus
End Select

DoCmd.Close A_FORM, "yCustomer Search Dialog"
End If
Exit Sub

OK_Click_Err:
MsgBox "Error is " & Error$ & Chr(13) & Chr(10) & "In yCust Search
OK_Button" & Chr(13) & Chr(10) & CPBSMSG, 16
Exit Sub
- - - - - - - - - - - - - - - - - - - - - - -

Aside from using "DoCmd.FindRecord" command, are there other ways to find a
record that is faster?

I have a couple of ideas but what I am asking are the ways to do it in
Access and not in MS SQL.

Using the "DoCmd.FindRecord" command seems to be slow every now and then.
It takes 3 to 5 seconds sometimes to execute.

But if you see another cause, in the code, that slows down the performance,
please let me know.



Thanks,

Ben

 

[gllincoln]

Hi Ben,

I've experienced that kind of erratic and seemingly unreasonable delay when running a search; I don't think it's the code. Code issues tend to be predictable/repeatable.

On my end, it seems to be [sometimes] anti-virus software going hyperactive and other times it appears I've hit a point where the system has to pull another chunk of data or maybe load a different library or something, and that read gets delayed or is extra slow for whatever unknown reason. The AV software interference can be spotted if you leave Task Manager running in the corner, Processes tab, sort by CPU descending. If the data is local, you can sometimes hear the hard drive get active and/or see the activity light start blinking like it was sending Morse Code. I do know that disabling the anti-virus software seems to let Access open large queries, do large searches faster. Of course, and it should be noted, disabling your AV software has other potential consequences you may not appreciate.

Going after the form's recordsetclone, doing a findfirst then setting the form's bookmark to the recordsetclone's bookmark seems somewhat more consistent in response times and may be a little faster.

I use DAO for this - something along the lines of the code below, this is air code - not tested, so it may have syntax issues;

Hope this helps,
Gordon

=================================================
Public Function FindMe(myCol As String, myCriteria As String) As Boolean
'myCol is the field we are searching
'myCriteria is the pattern we are searching for

Dim rs As DAO.Recordset
Set rs = Forms!Customer.RecordsetClone
rs.FindFirst myCol & "= '" & myCriteria & "'"
If Not rs.NoMatch Then
Forms!Customer.Bookmark = rs.Bookmark
FindMe = True
Else
MsgBox myCriteria & " not found in " & myCol, vbInformation, "Record Not Found"
FindMe = False
End If

Set rs = Nothing

End Function
============================

Hi!

My problem is basically in a search modal window, where the user is allowed
to make a selection on which option to take. After selecting an option and
then giving the data to search, the user clicks the "OK" button and the user
gets taken back to the "Customer" form with the correct searched record.
The function works fine except that every now and then it takes 3-5 seconds
to close the search modal window and goes back to the "Customer" form after
clicking the "OK" button

Below is the actual code for the "OK" button:
- - - - - - - - - - - - - - - - - - - - - - -
On Error GoTo OK_Click_Err
If IsNull(Forms![yCustomer Search Dialog]![By Search Type]) Then
Response = MsgBox("You Did Not Select a Record to Search For", 32, "No
Record Selected")
Else
DoCmd.SelectObject A_FORM, "Customers"
Forms![Customers]![Customer Number].SetFocus
DoCmd.FindRecord [Forms]![yCustomer Search Dialog]![By Search Type],
A_ENTIRE, False, A_DOWN, False, A_CURRENT

Select Case Me.Type_of_Search
Case 1
Forms![Customers]![CustomerSubFrm].Form![First Name].SetFocus
Case 2
Forms![Customers]![CustomerSubFrm].Form![Company].SetFocus
Case 3
Forms![Customers]![Customer Number].SetFocus
Case 4
Forms![Customers]![CustomerSubFrm].Form![Phone].SetFocus
End Select

DoCmd.Close A_FORM, "yCustomer Search Dialog"
End If
Exit Sub

OK_Click_Err:
MsgBox "Error is " & Error$ & Chr(13) & Chr(10) & "In yCust Search
OK_Button" & Chr(13) & Chr(10) & CPBSMSG, 16
Exit Sub
- - - - - - - - - - - - - - - - - - - - - - -

Aside from using "DoCmd.FindRecord" command, are there other ways to find a
record that is faster?

I have a couple of ideas but what I am asking are the ways to do it in
Access and not in MS SQL.

Using the "DoCmd.FindRecord" command seems to be slow every now and then.
It takes 3 to 5 seconds sometimes to execute.

But if you see another cause, in the code, that slows down the performance,
please let me know.



Thanks,

Ben