Highlighting Next Record After Current Record Filtered Out of Datasheet (1 Viewer)

[BlueSpruce]

1. I have users who periodically view a datasheet subform on the main dashboard that displays past due contracts.

2. Users drill down to certain contracts and decide if they want to forfeit the contract, or skip it and move on to the next contract. In the example below, the user decided to forfeit contract 36410:

3. When the Forfeit transaction is committed and the transaction popup closes, the popup's on close event fires the following code, requerying the forfeited, active counts, and amounts in the customer form.

Private Sub Form_Close()

 
   If CurrentProject.AllForms("frmCustomer").IsLoaded Then
        Forms!frmCustomer.frmCustomer_subContractsDetailsList.Requery
 
        Forms!frmCustomer!txtTotActive.Requery
        Forms!frmCustomer!txtTotRedeemedAmount.Requery
        Forms!frmCustomer!txtTotRedeemed.Requery
        Forms!frmCustomer!txtTotForfeited.Requery

    End If
 
    If CurrentProject.AllForms("frmFindCustomer").IsLoaded Then
        Forms!frmFindCustomer!LastContractNo.Requery
        Forms!frmFindCustomer!LastAmount.Requery
        Forms!frmFindCustomer!LastDateTime.Requery
 
        Forms!frmFindCustomer!txtContractNoFrom.Requery
        Forms!frmFindCustomer!txtBirthDateFrom.Requery
        Forms!frmFindCustomer!txtTransactionDate.Requery
 
        Forms!frmFindCustomer!TotNotPrinted.Requery
        Forms!frmFindCustomer!TotNotPrintedAmount.Requery
        Forms!frmFindCustomer!TotActive.Requery
        Forms!frmFindCustomer!TotActiveAmount.Requery
        Forms!frmFindCustomer!TotOverdue.Requery
        Forms!frmFindCustomer!TotOverdueAmount.Requery
        Forms!frmFindCustomer!TotRedeem.Requery
        Forms!frmFindCustomer!TotRedeemAmount.Requery
        Forms!frmFindCustomer!TotForfeited.Requery
        Forms!frmFindCustomer!TotForfeitedAmount.Requery
 
        Forms!frmFindCustomer!subDisplay.Requery
 
        Select Case Forms!frmFindCustomer!LastButtonPressed
   
          Case "NP"
             Forms!frmFindCustomer!TotalContractsByType = Forms!frmFindCustomer!TotNotPrinted
             Forms!frmFindCustomer!TotalAmountByType = Forms!frmFindCustomer!TotNotPrintedAmount
             Forms!frmFindCustomer!TotalContractsByType.ForeColor = vbRed

          Case "Active"
             Forms!frmFindCustomer!TotalContractsByType = Forms!frmFindCustomer!TotActive
             Forms!frmFindCustomer!TotalAmountByType = Forms!frmFindCustomer!TotActiveAmount
             Forms!frmFindCustomer!TotalContractsByType.ForeColor = vbRed

          Case "Overdue"
             Forms!frmFindCustomer!TotalContractsByType = Forms!frmFindCustomer!TotOverdue
             Forms!frmFindCustomer!TotalAmountByType = Forms!frmFindCustomer!TotOverdueAmount
             Forms!frmFindCustomer!TotalContractsByType.ForeColor = vbRed
 
          Case "redeem"
             Forms!frmFindCustomer!TotalContractsByType = Forms!frmFindCustomer!TotRedeem
             Forms!frmFindCustomer!TotalAmountByType = Forms!frmFindCustomer!TotRedeemAmount
             Forms!frmFindCustomer!TotalContractsByType.ForeColor = vbWhite
 
          Case "forfeit"
             Forms!frmFindCustomer!TotalContractsByType = Forms!frmFindCustomer!TotForfeited
             Forms!frmFindCustomer!TotalAmountByType = Forms!frmFindCustomer!TotForfeitedAmount
             Forms!frmFindCustomer!TotalContractsByType.ForeColor = vbWhite

          Case "transdate"
             Forms!frmFindCustomer!TotalContractsByType = DCount("transactionamount", "qryDashboardTotalsTraansactions", "TransDate = #" & Forms!frmFindCustomer!txtTransactionDate & "#")
             Forms!frmFindCustomer!TotalAmountByType = DSum("transactionamount", "qryDashboardTotalsTraansactions", "TransDate = #" & Forms!frmFindCustomer!txtTransactionDate & "#")
             Forms!frmFindCustomer!TotalContractsByType.ForeColor = vbWhite
 
        End Select
 
    End If

4. So when users close the contract form they just updated, it also closes the customer form and control returns to the requeried datasheet subform with the past due contracts so users can continue where they left off to select the next contract they want to drill down and possibly forfeit, and so on. I thought by bookmarking each contract they open and tracking the next contract 36414 in the recordset clone, if user forfeits current contract 36410, in the On Current event I could do an acCmdSelectRecord of the next contract I stored so the highlited record selector wouldn't move to contract 36414. However, it didn't work and the record selector resets to the first record after the datasheet is requeried. I then tried using recordset clone to track the record cursor's location and increment/decrement the cursor for the record selector, but no success

Option Compare Database
Option Explicit
 
Private Function DisplayContract()
 
  If Me.CurrentRecord = 1 Then
        crId = Me.CurrentRecord
    'First record
   ElseIf Me.CurrentRecord = Me.RecordsetClone.RecordCount Then
    'Last record
        crId = Me.CurrentRecord - 2
   Else
    'not first or last
        crId = Me.CurrentRecord - 1
   End If
 
   Dim intContractNo As Variant
   Dim intCustID As Integer
 
   intContractNo = Me.ContractNo
   intCustID = Me.CustomerSIID
 
   DoCmd.OpenForm "frmCustomer"
   With Forms!frmCustomer
      !txtPrimaryKey = intCustID
      .Requery
   End With
 
   gReturnToFormName = "frmCustomer"
   DoCmd.OpenForm "frmContract", WindowMode:=acDialog, OpenArgs:=intContractNo
 
End Function


Private Function CheckForEnterKey(KeyCode As Integer)
   If KeyCode = vbKeyReturn Then
      DisplayContract
   End If
End Function


Private Sub Form_Current()

    DoCmd.RunCommand acCmdSelectRecord
    Me!RowHighlight = Me!RefContractNo
 
End Sub

Private Sub Form_Load()

'      Call CreateRightClickMenu
Application.CommandBars("Form Datasheet Subcolumn").Enabled = True
DoCmd.RunCommand acCmdSelectRecord

End Sub


Private Sub ContractNo_KeyDown(KeyCode As Integer, Shift As Integer)
   If KeyCode = vbKeyReturn Then
      DisplayContract
   End If

End Sub

Private Sub ContractStatus_KeyDown(KeyCode As Integer, Shift As Integer)
   CheckForEnterKey KeyCode

End Sub

Private Sub DaysInterestDue_KeyDown(KeyCode As Integer, Shift As Integer)
   CheckForEnterKey KeyCode

End Sub

Private Sub DisplayComment_Click()
    'SendKeys "{F2}"
   ' DoCmd.RunCommand acCmdZoomBox
    DoCmd.OpenForm "frmPopUpDisplayContractComments", , , "Contractno = " & Me.ContractNo & ""
 
End Sub


Private Sub txtContractNo_KeyDown(KeyCode As Integer, Shift As Integer)
   CheckForEnterKey KeyCode

End Sub

Private Sub txtReedemedAmount_KeyDown(KeyCode As Integer, Shift As Integer)
   CheckForEnterKey KeyCode

End Sub

 

[CJ_London]

perhaps capture the recordset.absoluteposition property value and on return set it

dim currRec as long

currRec=recordset.absoluteposition

... do stuff

recordset.absoluteposition=currRec

if you have deleted what was in the absolute position, it should go to the next record

you'll need code if the absolute position was the last record otherwise you will get an error

You don't appear to have a scrollbar, but if you do you may also have to adjust the scroll position if the form has been scrolled (use get/set scrollinfo API)

 

[MajP]

I think I could get my head spinning trying to move the postion.
I think I would add these properties to my form
IsFirstRecord ' boolean
IsLastRecord 'boolean
CurrentID '
PreviousID ' ID of previous reocord or something like 0 is you are at first record
FollowingID. ' ID of next record or something like 0 if you are at last

Then on the current event set these properties
When you return you know everything you need to move to the correct record.

 

[MarkK]

You can also use Recordset.PercentPosition instead of .AbsolutePosition.
• doesn't fail if you remove the last row,
• might move the current record pointer up (like if you remove the last row).

 

[BlueSpruce]

perhaps capture the recordset.absoluteposition property value and on return set it

dim currRec as long

currRec=recordset.absoluteposition

... do stuff

recordset.absoluteposition=currRec

if you have deleted what was in the absolute position, it should go to the next record

you'll need code if the absolute position was the last record otherwise you will get an error

You don't appear to have a scrollbar, but if you do you may also have to adjust the scroll position if the form has been scrolled (use get/set scrollinfo API)

@CJ_London, But am I not capturing the absolute record position with this function below?

@MajP
... I think I would add these properties to my form
IsFirstRecord ' boolean
IsLastRecord 'boolean
CurrentID '
PreviousID ' ID of previous reocord or something like 0 is you are at first record
FollowingID. ' ID of next record or something like 0 if you are at last

Then on the current event set these properties
When you return you know everything you need to move to the correct record.

But isin't the function below doing the same thing you suggested?

Private Function DisplayContract()
 
  If Me.CurrentRecord = 1 Then
        crId = Me.CurrentRecord
    'First record
   ElseIf Me.CurrentRecord = Me.RecordsetClone.RecordCount Then
    'Last record
        crId = Me.CurrentRecord - 2
   Else
    'not first or last
        crId = Me.CurrentRecord - 1
   End If
 
   Dim intContractNo As Variant
   Dim intCustID As Integer
 
   intContractNo = Me.ContractNo
   intCustID = Me.CustomerSIID
 
   . . .

Private Sub Form_Current()<br><br>    DoCmd.RunCommand acCmdSelectRecord<br>    Me!RowHighlight = Me!RefContractNo<br> <br>End Sub<br><br>Private Sub Form_Load()<br><br>'      Call CreateRightClickMenu<br>Application.CommandBars("Form Datasheet Subcolumn").Enabled = True<br>DoCmd.RunCommand acCmdSelectRecord<br><br>End Sub

ugh, there's a glitch on this iphone when adding code in the code block

 

[BlueSpruce]

You can also use Recordset.PercentPosition instead of .AbsolutePosition.
• doesn't fail if you remove the last row,
• might move the current record pointer up (like if you remove the last row).

PercentPosition, or PresentPostion?