Keep focus on current record (1 Viewer)

[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!!!

 

[Pat Hartman]

The Requery is what is repositioning the recordset. There is no need to requery the recordset after a save. If you remove all the code except the
DoCmd.RunCommand acCmdSaveRecord the problem should disappear.

 

[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.

 

[Pat Hartman]

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.

- This is not necessary. Base your form on a query that joins the main table to whatever table the combo gets its data from. That way, when the selected value of the combo changes, ALL related fields on the form will populate AUTOMATICALLY. You don't need a single line of code to make this happen. Remove ALL the code in this AfterUpdate event and let the power of a relational join do its work. If you want to read more about this topic, search for AutoLookup queries in help.

For future reference,
1. A form's RecordsetClone is a DAO recordset, NOT an ADO Recordset no matter what version of Access you're using (at least up to AXP).
2. It is best to define objects and variables as specifically as possible. Since you know that rs is a Recordset and ONLY a recordset, never some other type of object, it is poor practice to define it simply as an Object. It should be defined as a DAO.Recordset. Not only does this save Access time during execution since Access doesn't have to figure out what type of object rs is every time it is referenced but it also prevents subtle errors that may arise because you are using a non-specific object type.

 

[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.

 

[Pat Hartman]

Your form must be unbound if you're using a stored procedure. Why not use a querydef instead. Performance will not be impacted if your query has selection criteria so that you are only retrieving the data necessary for the current record. With a querydef, your form can be bound which will save you a s___load of code.

 

[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?

 

[Pat Hartman]

I take it back, I guess the form can be bound but it can't be updatable unless you write the necessary code.

 

[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.