Speedy search on continuous form needed 1

[tekkyun]

Hello,
I have a subform that displays order data records. The subform recordset is switched dependant on option buttons selected by the user on the main form. Each recordset consists of approx. 4000 records displayed continuously on the subform and shows the orders at a particular status selected by user
I have added a search facility for the users to find particular OrderNo / OrderLine field combinations. This works fine but is very slow when the user selects an order which is way down the list.

I need a method of navigating straight to the record. I'm sure this must be easy but the other methods I use don't seem to work with subform recordsets. Can anyone help?

My current method (on_click event on button on main form):
Ctl.Form.Recordset.MoveFirst
Do Until Ctl.Form.Recordset.EOF
If Ctl.Form.Recordset.Fields("OrderNo") = PO And Ctl.Form.Recordset.Fields("OrderLine") = POLine Then Exit Do
Ctl.Form.Recordset.MoveNext
Loop

 

[HarleyQuinn]

Could you not use the Recordsets .Find method?

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[tekkyun]

I did try that but get 'Object doesn't support this property or method'

 

[HarleyQuinn]

What kind of recordset are you using?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[tekkyun]

I am just using the subform recordset, ie ctl.form.recordset.

 

[tekkyun]

That's it. I just added Dim Rs as recordset and used this:-

Dim rs as recordset
Set Rs = Ctl.Form.Recordset

Rs.FindNext "OrderNo = " & PO & " And OrderLine = " & POLine

Seems to work great!

thanks for your help

 

[HarleyQuinn]

You're welcome, glad I could help

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Remou]

tekkyun
Clicking

Thank HarleyQuinn
for this valuable post!

Is a nice way of thanking people.

 

[HarleyQuinn]

Cheers Remou

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.