Find not working for sproc recordsource

[ingineu]

I have a Form with a subform. The subform is a continuous form. Upon clicking the subform area, I highlight the selected row and display the details. When a Sort option is selected, I want to keep the current line highlighted. To accomplish this I use the following code: (Access 2002 'adp' SQL Server)

Me.Form.InputParameters = "@XSortOrder nvarchar = '" & XSQLOrder & "'"
Me.Form.Requery
Me.Form.Recordset.Find "[EmpKey] = '" & strSave

The FIND command does not always find a match, in which case it defaults to the 1st row displayed in the subform. I have heard that only a certain amount of records are returned at a time, so the Find command may not return a match.

If I insert a Msgbox command after the Requery, it works, seems like a timing issue. How do I handle this?

 

[cmmrfrds]

Instead of the Find method on the recordset object, use the Filter method. It is much more flexible and efficient.

Me.Form.Recordset.Filter "[EmpKey] = '" & strSave

With the Filter method you can use multiple criteria just like the where claue in an SQL Statement.

 

[ingineu]

What I am trying to do is set the current record pointer in the continuous form to the saved key, which is why I use the FIND command. It does not seem to work if the key is in descending order.

 

[cmmrfrds]

Did you try the Filter?

 

[ingineu]

Yes, I tried it but was not sure how to set the bookmark in the Forms recordset, at least it is not highlighting the correct record, it defaults to the 1st record.

Me.Form.Recordset.Filter = "[EmpKey] = '" & strSave & "'"

What follows the Filter command? Thanks.

 

[ingineu]

Me.form.bookmark = me.form.recordset.bookmark seems to work. Thanks. There's a bit of a delay before the screen pops up with the data, but I suppose I can live with that. I'm only dealing with 450 records.