Can't Go To Specific Record on Continuous Form in MS Access

Question

Searched and searched and cannot find a solution to this. I have a form with many continuous form subforms. When I change a value in, lets say FIELD_A on one of the subforms, I run calculations on several other subforms, then the focus returns to FIELD_A. However, during the calculations an update to the PARENT form happens, and needs to happen. So, when I return focus to the original subform, the first record on my subform has the focus. I need to then go to the record I was working on.

I've tried several options, but nothing works. However, if I set a DEBUG breakpoint at the line in the code where it moves to the specified record, then physically RUN the code from that line, it works! I've tried setting a wait period in there to no avail.

Here's a snippet of the code:

Call common.CalculateAllLoadTotals _
    (Me, Me.AffiliateID, Me.ClientID, Me.FacilityID, Me.ProposalRevision)

Me.Recordset.FindFirst "[AffiliateID] = '" & Me.AffiliateID & "'" & _
                 " AND [ClientID] = " & Me.ClientID & _
                 " AND [FacilityID] = " & Me.FacilityID & _
                 " AND [ProposalRevision] = " & Me.ProposalRevision & _
                 " AND [EquipmentID] = " & currItemID

I also tried this:

dim currRecord as Long
currRecord = Me.CurrentRecord

' >>> REST OF CODE HERE <<<

Call common.CalculateAllLoadTotals _
    (Me, Me.AffiliateID, Me.ClientID, Me.FacilityID, Me.ProposalRevision)

Me.Form.Recordset.Move = currRecord

As I said, the code works (either one) IF I pause it with a debug then continue executing. Strange.

The subform? It does. In the code above, you'll see reference to Me.ProposalRevision. That identifies the subform records that match the parent record. The "Me.EquipmentID" refers to the actual record I want to move to. The other fields are the other primary index fields. — MultiGuy
And you are running that code on the subform? And you are sure that currItemID is a value? — Fionnuala
That's correct. Like I say, if I interrupt the code with a breakpoint then simply run the code from the line in question, it works. The code works, it's just seemingly not firing on that line. In fact, no error happens, it executes and everything else is correct, I just doesn't move to the record. Stays at the top record. Weird, huh? — MultiGuy
BTW Have you compavted and repaired and decompiled recently? They often cure odd behaviour, though I am not sure that this is the case here. — Fionnuala

sarh sarh · Accepted Answer · 2015-06-19T19:54:56

Sorry that's will not be a complete answer but it is quite lot for a comment. Regarding your first solution - I'd advise you to try Me.Recordset.Requery to refresh current record in the main form without moving position.

Regarding you 2nd option - I'd advise to have a look at bookmarks - before update remember bookmark, make some calculations and then move position to the saved bookmark. Something like this -

  Dim rs As DAO.Recordset

  Set rs = Me.RecordsetClone
  rs.FindFirst "[MyPK]=" & Me!cmbFindByPK
  If Not rs.NoMatch Then
     If Me.Dirty Then
        Me.Dirty = False
     End If
     Me.Bookmark = rs.Bookmark
  End If
  Set rs = Nothing

taken from here Why clone an MS-Access recordset?

Can't Go To Specific Record on Continuous Form in MS Access

2 Answers