Requery resets mainform to first record

[kvest]

Hello,
I have a form [frm paymentsentry] that has a subform launched from a button to enter in payments or fees. When the users closes that subform, I have the mainform requery using [Forms]![frm PaymentsEntry].Requery on the On Click event. The problem is that then the mainform is reset back to the first record, even if they were entering in on the 100th.

I found thread702-1067302 which mentions a similiar problem and obtained and modified this code to try an accomplish this:

Dim rst As DAO.Recordset, pkName As String, pkHold

pkName = "BookingNumber"
pkHold = Me(pkName)

[Forms]![frm PaymentsEntry].Requery

Set rst = Me.RecordsetClone
rst.FindFirst "[" & pkName & "] = '" & pkHold & "'"
Me.Bookmark = rst.Bookmark

Set rst = Nothing

DoCmd.Close acForm, "sfrmPaymentEntryPopup"

When I run this, it tells me there is No current record. When I check the record where the entry was made, the entry is there. The final post on that thread was that he made some modifications to it since he was running it from a subform. I have done what I can, but dont know what else needs modified.

 

[Remou]

You are running this code in a form called sfrmPaymentEntryPopup, which is a popup form (rather than a subform), and the code applies to a form called [frm PaymentsEntry], right?

If so, you need to say something like:

Dim rst As DAO.Recordset, pkName As String, pkHold
   
   pkName = "BookingNumber"
   pkHold = [Forms]![frm PaymentsEntry](pkName)
   
   [Forms]![frm PaymentsEntry].Requery
   
   Set rst = [Forms]![frm PaymentsEntry].RecordsetClone
   rst.FindFirst "[" & pkName & "] = '" & pkHold & "'"
   [Forms]![frm PaymentsEntry].Bookmark = rst.Bookmark
   
   Set rst = Nothing

DoCmd.Close acForm, "sfrmPaymentEntryPopup"

 

[kvest]

Remou,
You are correct. Sorry I didnt make that clear. The code works now, in that the main form {frm PaymentsEntry) stays as the current record. The embeded (display only past payment history subform (sfrm PaymentEntry)) is not requeried to show the latest entry done via the popup form. If the user moves to the next record and then back, the latest transaction is visible.

This is much better than having to search for the record as they had to before. Sorry if I am not conveying the information you need to explain what I have setup. I assume that perhaps the reference to the main form should be changed to requery the embeded subform. I will try that and see what happens.
Here is a star for your help!!!

 

[Remou]

Kvest
I think I have it now. If, as I think, you are not making changes to the main form, only adding or editing the subform, you are going the long way around. Try this:

[s]Dim rst As DAO.Recordset, pkName As String, pkHold

   pkName = "BookingNumber"
   pkHold = [Forms]![frm PaymentsEntry](pkName)[/s]
   
   [Forms]![frm PaymentsEntry].[i][Subform Control Name][/i].Requery

   [s]Set rst = [Forms]![frm PaymentsEntry].RecordsetClone
   rst.FindFirst "[" & pkName & "] = '" & pkHold & "'"
   [Forms]![frm PaymentsEntry].Bookmark = rst.Bookmark

   Set rst = Nothing[/s]

DoCmd.Close acForm, "sfrmPaymentEntryPopup"

 

[kvest]

Remou,
It works...AWESOME!!! Thanks a ton.

[Forms]![frm PaymentsEntry].[BookingNumber].Requery