How to find newly entered record after form requery?

[DonBerry]

In an application I have a form that must be requeryed after a new record is added to update info on the form. My question is how can I get back to the last entered record and make it current? Its position in the recordset may change from the last record after the requery due to the sort.

 

[Lightning]

Have a look at the Bookmark property in the Access OnLine Help file. This should give you what you are after.

HTH

Lightning

 

[BrooksR]

Don,

To expand on what Lightening said, you would want to:

1. Save the bookmark (after the add completes).
2. Refresh the query.
3. Set the recordset location to the saved bookmark. This makes the form "jump" to the last record added.

You can store bookmarks into string variables. There should be an example with code in the help system.

Brooks

 

[DonBerry]

A Bookmark does not work for a new record nor if you have an empty recordset and are making the first entry. You get an invalid bookmark error. You cannot get the Bookmark property of a record until after it is saved and inserted into the recordset. By then, in my case, it has moved and because of the requery it is no longer current.

Don

 

[Lightning]

Maybe you can adapt something from this code:

'If the main form is open, then close this form and
'return focus to the main form.

    If IsLoaded("frmMain") Then
        Dim X As Variant
        X = Me![MyField]
        DoCmd.Close
        Forms!frmMain![MyMainField].Requery
        DoEvents
        Forms!frmMain![My MainField] = X
        DoCmd.GoToControl "[MyMainField]"
    Else

'Close this form and return to the Main Menu

        DoCmd.Close
    End If

I use this code to return to a specific record in my main form after opening a secondary form and adding new data to a list. The secondary form is called by the "Not In List" event of a combo box.

The above code is in the Click event of my Closeform command button on the secondary form, and works because the X variable is always a unique value within the main table at the time it is called.

Type new value in combobox
Not In List event opens secondary form
Add new data to secondary form and save
Close secondary form.
Close event runs above code


Hopefully you can get something out of this that will help you with your current problem.

Lightning

 

[cmmrfrds]

I believe an access form will refresh your record set after you add the record. If you went to add a new record the id you are looking for should be id.OldValue regardless on any sorting.