Solved Move To New Record on Continuous Form When You Close Data Entry Popup Form

[LGDGlen]

Hi All

I have a continuous form that shows records from a table. There is a button which opens a popup form for data entry. Once the user completes their data entry there is a button to Save/Exit and this requeries the continuous form so the new record is visible, but i would like to move the focus to the new record.

In the popup form the Save/Exit button has:

Private Sub btnSaveAndExit_Click()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close
'refresh the consignment form to show new product
[Forms]![form-DS-Consignments].Requery
End Sub

Which all works fine but this leaves the user at the first record of the continuous form and not the new one at the bottom. How do i move the user to that new record as they exit the popup form

Hope that makes sense

 

[June7]

Are you opening popup with acDialog parameter? acDialog suspends code execution of calling form until popup closes or set not visible. So if you are editing an existing record and therefore you have the ID for the record. I would use RecordsetClone and Bookmarks to locate that record after form requery/refresh. But there is another way to refresh form without changing record focus. Instead of requerying the form, requery its Recordset: Me.Recordset.Requery.

DoCmd.OpenForm "formname", , , "ID=" & Me.ID, , acDialog
Me.Recordset.Requery

And code in the popup:
Private Sub btnSaveAndExit_Click()
DoCmd.Close
End Sub

But if you are creating a new record in the popup and that record will be at end of data, use DoCmd.GoToRecord , , acLast. Otherwise, would need to get the new record ID from popup before it closes for use by main form to find that record, again using RecordsetClone and Bookmarks.

Do you have button for opening popup to existing record and another button for opening to new record?

Don't need to use SaveRecord. Record will be saved when form closes. Even if you do save, DoMenuItem is not the preferred method.

 

[LGDGlen]

hey @June7 thanks for the update.

The popup form is called as follows:

Private Sub btnAddConsignment_Click()
DoCmd.OpenForm "form-Consignment", , , , acFormAdd
End Sub

this creates a new record but doesn't actually add the record to the table till the user hits Save or Save/Exit on the popup form.

so the requery part in your above should that go like this as as you said those lines won't get run until the popup is closed:

Private Sub btnAddConsignment_Click()
DoCmd.OpenForm "form-Consignment", , , , acFormAdd
Me.Recordset.Requery
Me.Recordset.MoveLast
End Sub

 

[June7]

I did some major edits on previous post, probably while you were reading.

DoCmd.OpenForm "form-Consignment", , , , acFormAdd, acDialog

Strongly advise not to use spaces nor punctuation/special characters in naming convention, underscore is only exception.

 

[LGDGlen]

awesome thank you, its the acDialog part that pauses things thats good to know

i'll look into the naming of the forms etc thanks for the recommendation