Way to speed up searching function

[mlai08]

I have an employee input form containing a "Find" combo box for user to search employee records in the embedded sub form. The VBA coding as follows:

Private Sub cboFind_AfterUpdate()
'Purpose: Find employee indicated in drop-down box
On Error GoTo Error_cboFind_AfterUpdate
Dim rst As Recordset

If IsNull(Me.cboFind) Then Exit Sub
Set rst = Me.fsubEmployeeInput.Form.RecordsetClone
With rst
.FindFirst "EmpID = " & gcstrQuote & Val(Me.cboFind) & gcstrQuote
If Not .NoMatch Then
Me.fsubEmployeeInput.Form.Bookmark = .Bookmark
End If
End With
Exit_cboFind_AfterUpdate:
Exit Sub
Error_cboFind_AfterUpdate:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_cboFind_AfterUpdate
End Sub

(The EmpID is an indexed Primary Key)

This feature works fine until we put the backend of the database on a shared network drive. As the number of records increased in the database, it takes a long while to search employee from the cboFind box - worst case takes over a minute). I know that network traffic plays a important role since every search needs to query over 5000 records at the backend but wonder if there are any more effecient coding which may speed up the search process under this situation.

Thanks

 

[CJ_London]

Having found an employee, what are you doing with it? You seem to have a very complex solution to a very simple problem.

Why not simply have

me.filter="EmpID = " & me.cboFind
me.filteron=true

your method has to populate a recordset (rst) then the .find does a sequential search of the recordset. My suggestion makes use of the fact the table is indexed whereas your recordset is not indexed.

You can index the recordset but then you use seek, rather than find - this link can explain in more detail with examples

http://msdn.microsoft.com/en-us/library/office/ff194103(v=office.15).aspx

And please, in future use the code tags and indenting when presenting code - it is otherwise very difficult to read particularly when not familiar with the context

 

[mlai08]

Thank you for your suggestion. It would be great to use lighter codes. However, the filter option does not work. The purpose of the Find box is to pick the employee from cboFind in the main form and return the respective record of that employee in the subform. EmpID is the linked field between the main form and subform. How can we make the filter works for the records on the sub form?

 

[CJ_London]

OK , a couple of things

First, To summarise you have a combobox called cboFind with a rowsource something along the lines of

SELECT EmpID, EmpName From tblEmployees

And you have a subform called fsubEmployeeInput with a recordsource something like

SELECT * FROM tblEmployees

To populate the subform with the selected employee does not require any code. All you need to do in the subform control properties is set the liinkChild property to EmpID and the linkMaster property to cboFind.

Alternatively If you want to keep some code, then remove the existing code and use this instead

Me.fsubEmployeeInput.Form.Filter = "EmpID = " & me.cboFind
Me.fsubEmployeeInput.Form.FilterOn=True

However this does not necessarily fully address the performance issue across the network. To address this look initiallly at the infrastructure. Wireless is a wonderful thing but is not ideal for transferring large amounts of data. So make sure all your computers are hardwired and all routers along the way are of a suitable capacity.

Next, look at the amount of data being transfered - websites transfer a page at a time so not much data, so you need to emulate this.

You can't do much about the combobox, but it only needs to transfer data once on initial population and the recordset is 'narrow'.

Your subform however is another matter - you are transferring across the network (presumably) the entire table. And if you are using lookups in your table design, all the associated tables as well.

If you are using lookups in your table design, remove them and see what performance gains you get.

So how to reduce the amount of data to transfer? Answer - only bring through the record for the employee selected.

How to do this?

1. remove the recordsource from the subform form and save it so it opens without a recordsource
2. in your cboFind afterupdate event remove all code and replace with

Me.fsubEmployeeInput.Form.Recordsource="SELECT * FROM tblEmployees WHERE EmpID=" & me.cboFind

For tidyiness, I prefer to actually set the subform control sourceobject to a blank form (i.e. no controls) set to the same colour as the main form so it blends in. Then in the code above I put

Me.fsubEmployeeInput.sourceobject="EmployeeInput"

before the code above so the correct form is assigned to the subform control before assigning the recordsource.

Hope that all makes sense

 

[spikepl]

#2
Thanks CJLondon.

I never thought that .FindFirst did not use indexing , but it makes perfect sense why .Seek differs from .Find.

Many loops I made over time would benefit from rewrites base on my new insight: the linked tables could get opened directly so .Seek could be used.

 

[CJ_London]

@Spike

Yeah, took me a few years to realise that .find works the same way as 'find' in a query or table.

Re .seek, remember the source table needs to be indexed on the approriate fields(s) so it can be passed to the recordset using .index. If it not indexed, you get an error.

 

[mlai08]

Thank you so much for your comprehensive and informative comments. The orginal code on the cboFind_AfterUpdate sub routine was inherited from someone who created the database.

The codes for filtering work great and it seems to speed up the employee searching process but I can't tell the degree of improvement since the network traffic in our office fluctuates during the day.

As regards other possible performance barriers, all database users have wired connection and there is no lookup fields in the Employee table design. All dropdown fields for the employee input are happened at the form level.

I shall try your other suggestion to reduce data traffic on the input form later when I have more time and get back to you if necessary.

Thanks again for your great help.