Access forms using DoCmd.ShowAllRecords

Hi Richard, I'm an independent adviser and will try to help.

I'm a little unclear here. So Form A lists all records, and Form B allows you to update the selected record or add a new one. The save button closes which form? A or B or both?

If you want to search through the records, you can create a search combobox. The combobox wizard will walk you through creating one (third option). If you want to retrieve the last record entered, you can look for the highest ID value (assuming you are using an Autonumber PK)

If you need furthr clarification on this please provide more details and i will try to help further.

The save button closes form B.

When form B is closed, the save button (form B) uses [Event Procedure]...

Private Sub CommandSave_Click()
    DoCmd.Save
    DoCmd.Close
'    DoCmd.RepaintObject acForm, "Contributions"
    DoCmd.ShowAllRecords
End Sub

After form B closes, using DoCmd.ShowAllRecords, form A is positioned at the beginning of the data.

I have to scroll/page down to get back to where I want to be.

I tried DoCmd.RepaintObject but new records were not displayed.

I want to be able to return to the position I was before the change/update.

Thanks

Richard

You need to write code for this.  What you have to do is firstly assign the primary key of the edited or appended record in the second form to a variable.  Then, by synchronizing the first form with the record whose primary key is that stored in the variable, move the first form's record pointer to the record in question.

So, the code would go in the AfterUpdate event procedure of the second form, and would be along these lines:

    Dim lngID As Long
    Dim frm As Form

    lngID = Me.NameOfPrimaryKey
    Set frm = Forms("NameOfFirstForm")

    ' requery first form to reload its recordset
    ' and include any new record just inserted
    frm.Requery

    ' move first form's record pointer to new or
    ' edited record by first finding the record in its RecordsetClone
    ' and then synchronizing the form's Bookmark with
    ' the RecordsetClone's Bookmark
    With frm.RecordsetClone
         .FindFirst "NameOfPrimaryKey = " & lngID
         If Not .NoMatch Then
             frm.Bookmark = .Bookmark
         End If
    End With

In the above I've assumed that the primary key of the table to which the form is bound is a column of long integer data type, most probably an autonumber.

Thank you!!!!

This worked.  I really appreciate your help.

I have a lot to learn about Access, and this community help is great.

Richard

OK, First your code doesn't do what you think it does. DoCmd.Save does NOT save the data, what it saves is any design changes made to object specified (if no object is specified it works on the active object). So all you need is:

DoCmd.Close, acSaveNO

When you close Form B, Form A should be in the same record position it was when it lost focus. But you can try to use DoCmd.GotoRecord to reposition the focus. The problem is you will need to know the relative recordnumber.