DoCmd.ShowAllRecords

Copper Contributor

I have created an app which opens a form listing all entries. It has an open text box which opens another form with the selected record (or new). After updating the info, the save command button closes the form. My issue is...how do I return to the record that was updated (added)? In development, I found "DoCmd.ShowAllRecords" as the only way to reflect the update on the first form. But, it repositions to the beginning (top) of the database. Is there some other option to update the first form and remain on the updated record?

1 Reply

@RichardHG There are two questions here, actually.

 

First, there's the issue of requerying the original, calling form after adding a new record to a second form which you open for that purpose.

 

Since you don't show us how you actually open that second form for input, I'm going to make a bit of a guess. Something like this?


DoCmd.OpenForm formName:="frmYourFormNameGoesHere", view:=acNormal, datamode:=acFormEdit, windowmode:=acWindowNormal

 

If so, then you can take advantage of the fact that opening a popup form, in dialog mode, suspends that VBA procedure until the popup is closed again. Like this.

 

DoCmd.OpenForm formName:="frmYourFormNameGoesHere", view:=acNormal, datamode:=acFormEdit, windowmode:=acDialog
Me.Requery

 

When you do that, the Me.Requery line fires after you have made your edit or update in the other form, which has the same effect as your current approach.

 

That leaves the problem of returning to the same record after the Requery.

 

To do that you'll need to identify the current record BEFORE opening the popup for edit.

 

Set a variable to the value of the current record's primary key.

 

Dim lngYourRecordPrimaryKey  As Long

Set lngYourRecordPrimaryKey = Me.PrimaryKeyfortheCurrentRecordGoesHere

 

then the two lines to open the popup and requery the calling form.

 

And finally, move focus to the record matching the variable, lngYourRecordPrimaryKey .