Keep focus on current record

[Lynn_AccessUser]

I have a continuous subform in which I am having troubles keeping the focus on the current record that the user is on. For example, there are 3 records in the subform and the user changes record 2 or 3. The user is then taken back to record 1.

I have used similar code on other forms and can't figure what the difference is that is doesn't work now.

Here is the code:

Dim rs As String
rs = Forms!frmCheckInfo!sbfCheckItem!txtCheckItemID
DoCmd.RunCommand acCmdSaveRecord
Forms!frmCheckInfo!sbfCheckItem!Requery
rs = Forms!frmCheckInfo!sbfCheckItem!txtCheckItemID.SetFocus
DoCmd.FindRecord rs

I put a break on rs = Forms!frmCheckInfo!sbfCheckItem!txtCheckItemID.SetFocus and the rs = correct value

However, when I go to the next line of code:
DoCmd.FindRecord rs

rs = "" and I get the following error msg:

Run-Time error 2142
The FindRecord action requires a Find What argument.

Thanks for the help!!!

 

[Lynn_AccessUser]

The event is on the after update on a combo box. The value that is choosen in the combo box needs to be saved and then the requery populates a bunch of additional fields on the form based on the value they choose in the combo box.

Sorry should have mentioned this in my original post.

Thanks!

 

[ghudson]

Search for the keyword "bookmark" for that should do what you want.

 

[Lynn_AccessUser]

OK here is what I have so far . . . not sure if I am on the right track.

Dim rst As Object
Dim strCriteria As String
Set rst = Me.Recordset.Clone
strCriteria = "CheckItemID = " & Me!CheckItemID
rst.FindFirst strCriteria
Me.Bookmark = rst.Bookmark

I am using ADO which is why I am using Recordset.Clone and FindFirst.

The code is breaking on rst.FindFirst strCriteria with the following error:
Run-Time error 438
Object doesn't support this property or method

 

[Lynn_AccessUser]

OK I have fixed the prior error with the following code:

Rim rst As Object
Dim strCriteria As String
DoCmd.RunCommand acCmdSaveRecord
Set rst = Me.Recordset.Clone
strCriteria = "CheckItemID = " & Me!CheckItemID
Forms!frmCheckInfo!sbfCheckItem.Requery
rst.Find strCriteria
Me.Bookmark = rst.Bookmark

The code works if you change an existing record in the subform. However, I am getting a new error when you add a new record.

The error is:
Run-Time error 3021
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

 

[Lynn_AccessUser]

The form's record source is already a stored procedure that joins the main table to several tables.

However, the other fields won't poplulate until I save the record and requery.

 

[Lynn_AccessUser]

Pat,

Trying to play catch up here I guess . . . why must the form be unbound if the record source is a stored procedure?

 

[Lynn_AccessUser]

I haven't had any issues per say with updating data in a table via a form based on a stored procedure as long as I set the unique table value and resync command if there is more than one table in the stored procedure.

Seems like I struggle with all of the bugs though that exist in Access 2000. Alot of the problems I encounter, the knowledge base for Microsoft says the problems have been fixed in XP. Using XP isn't an option though.