Solved Highlighting Next Record After Current Record Filtered Out of Datasheet

[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 past due contracts datasheet so users can continue where they left off and select the next contract they want to drill down and possibly forfeit. Contract 36410 that was just forfeited no longer appears on the past due datasheet. I thought by bookmarking each contract users drill down to and tracking the next contract in the recordset would highlight the next contract on the list if users forfeit current contract. However, it didn't work and the record selector resets back to the first record after the datasheet is requeried. I then tried using CurrentRecord, incrementing/decrementing the record cursor, and DoCmd.RunCommand acCmdSelectRecord _ Me!RowHighlight = Me!RefContractNo in On Current event, as seen in the func below, 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?

 

[MarkK]

PercentPosition, or PresentPostion?

Spoiler Alert: One of those is a property of a Recordset.

 

[CJ_London]

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

Suggest try it and see

 

[BlueSpruce]

Suggest try it and see

Will do whenever I get back home from hospital. Meantime, I continue brainstorming with the help of Chatty on my iphone.

 

[Ken Sheridan]

It would help greatly if there were a column or combination of columns which determines the sort order of the rows returned in the form. I can't see any obvious candidate, so let's assume therefore that the table includes a column SortColumn of Long Integer data type by which the form's query is ordered. Firstly you'd declare a Public variable of Long Integer data type in the declarations area of a standard module in the database:

Public lngSortColumn As Long

Then in the form's Current event procedure assign the next SortColumn value to the variable:

' assign next SortColumn value to public variable
lngSortColumn = Nz(DMin("SortColumn", "Contracts", "SortColumn > " & Me.SortColumn),0)

After you've requeried the form you'd then navigate to the row with the stored SortColumn value:

' navigate to row with next SortColumn value by synchronizing bookmarks
With Me.RecorsetClone
    .FindFirst "SortColumn = " & lngSortColumn
    If Not .NoMatch Then
         Me.Bookmark= .Bookmark
    End If
End With

If you prefer you could use a TempVars rather than a public variable.

 

[BlueSpruce]

It would help greatly if there were a column or combination of columns which determines the sort order of the rows returned in the form. I can't see any obvious candidate...

The record source for the datasheet subform is this query, which first sorts by ContractStatus=1 ascending, (Active contracts only for this datasheet), then last payment date ascending so users can see oldest contracts first. then the other sort orders seen in the SQL statement. I don't see the sort order as being relevant to moving the highlighted record selector to the next or previous records displayed on the datasheet. If I tell it to remember the next, previous record before users update status of CurrentRecord to where it no longer qualifies for the filters criteria, then it should move to whatever value I choose from the variables that store the ContractID.

SELECT tblContract.ContractStatusSIID, tblContract.ContractNo, tblContract.ContractDate, tblContract.CustomerSIID, tlkpContractStatus.Español AS ContractStatus, tlkpContractType.Español AS ContractType, tblContract.ContractAmount, tblContract.MaturityDate, IIf([tblContract].[ContractTypeSIID]=1 And [tblContract].[ContractStatusSIID]<=2,Date()-[ContractDate],Null) AS DaysInterestDue, getInterestDue([ContractDate],Date(),[ContractAmount],[tblContract!InterestRateSIID],[intpymtstatus]) AS [Interest Owed], Round([ContractAmount]+[Interest Owed]) AS [Reedemed Amount], qryGetMaxExtension.MaxExtension, GetDisplayDescription([tblContract!contractno],0) AS Artículos, tblContract.comment AS Comentario, [lastname] & ", " & [firstname] AS [Nombre del Cliente], IIf([tblContract!ContractStatusSIID]=2,[Reedemed Amount]," ") AS txtReedemedAmount, tblContract.ContractItemTypeSIID, tlkpContractItemType.DisplayDescription
FROM tblCustomer INNER JOIN ((((tblContract INNER JOIN tlkpContractStatus ON tblContract.ContractStatusSIID = tlkpContractStatus.ContractStatusSIID) INNER JOIN tlkpContractType ON tblContract.ContractTypeSIID = tlkpContractType.ContractTypeSIID) INNER JOIN qryGetMaxExtension ON tblContract.ContractNo = qryGetMaxExtension.ContractNo) INNER JOIN tlkpContractItemType ON tblContract.ContractItemTypeSIID = tlkpContractItemType.ContractItemTypeSIID) ON tblCustomer.CustomerSIID = tblContract.CustomerSIID
ORDER BY tblContract.ContractStatusSIID, IIf([tblContract].[ContractTypeSIID]=1 And [tblContract].[ContractStatusSIID]<=2,Date()-[ContractDate],Null) DESC;

 

[Ken Sheridan]

I don't see the sort order as being relevant to moving the highlighted record selector to the next or previous records displayed on the datasheet.

As far as determining which is the row to navigate to following the Requery the sort order enables you to determine this independently of the datasheet per se. The expression for the DMin function call to encompass all of the columns you've used would be tricky though. A simpler way, therefore. would be to move to the row matching the current row in the form's RecordsetClone, then call the recordset's MoveNext method and grab whatever values you need to navigate to the next row by synchronizing bookmarks. The Requery will change the bookmarks so don't be tempted to grab the clone's bookmark before the Requery.

 

[BlueSpruce]

As far as determining which is the row to navigate to following the Requery the sort order enables you to determine this independently of the datasheet per se. The expression for the DMin function call to encompass all of the columns you've used would be tricky though. A simpler way, therefore. would be to move to the row matching the current row in the form's RecordsetClone, then call the recordset's MoveNext method and grab whatever values you need to navigate to the next row by synchronizing bookmarks. The Requery will change the bookmarks so don't be tempted to grab the clone's bookmark before the Requery.

But if I store next record's ContractID prior to user updating CurrentRecord's status to Forfeited, which no longer qualifies for the datasheets filter of Actives only, then moving directly to the stored ContractID would happen regardless of any or no sort order, right?

 

[arnelgp]

the question is how do you Requery when a record is updated?
you should only requery the Recordset (not the Form), so that the
recordselector stays on it's position.

 

[BlueSpruce]

the question is how do you Requery when a record is updated?
you should only requery the Recordset (not the Form), so that the
recordselector stays on it's position.

Regardless of what I requery, if I stored the next ContractID I want to position on, it should directly go to wherever that row is located on the sheet, right?

 

[arnelgp]

yes it will.
most of the time RecordsetClone will fail, so better use Recordset:

With Me.Recordset
    .FindFirst "ContactID = " & lngNextContactID
    Me.Bookmark = .Bookmark
End With

 

[BlueSpruce]

yes it will.
most of the time RecordsetClone will fail, so better use Recordset:

With Me.Recordset
    .FindFirst "ContactID = " & lngNextContactID
    Me.Bookmark = .Bookmark
End With

I will test using rs. I'm curious why rs clone fails sometimes, but not always?

 

[BlueSpruce]

... you should only requery the Recordset (not the Form), so that the
recordselector stays on it's position.

So because the user updated the CurrentRecord's status, it no longer qualifies for the datasheet's filter criteria, it disappeared from the datasheet and the record pointer got lost, or disoriented, and automatically resetted to first record, even though i saved next recordID and told it to postion on that record?

 

[MajP]

Works for me I think.

Option Compare Database
Option Explicit

Private CurrentID As Long
Private nextID As Long
Private FirstRecord As Boolean
Private LastRecord As Boolean

Private Sub Form_Current()
    Me.txtLink = Me.OrderID
   ' for formatting
  End Sub

Public Function LaunchDetails()
  Dim rs As DAO.Recordset
  SetRecordData Me.OrderID
  DoCmd.OpenForm "frmOrderDetails", , , "OrderID = " & Me.OrderID, , acDialog
  Me.Requery
  HiliteNext
End Function

Public Sub SetRecordData(CurrentID As Long)
  Dim rs As Recordset
  Dim RC As Long
  Dim AP As Long
 
  Set rs = Me.RecordsetClone
  rs.FindFirst "OrderID = " & CurrentID
  AP = Me.Recordset.AbsolutePosition
  RC = rs.RecordCount
 
  FirstRecord = (AP = 0)
  LastRecord = (AP = RC - 1)
  If Not rs.EOF Then
    If LastRecord Then
      rs.MovePrevious
      nextID = rs!OrderID
    Else
      rs.MoveNext
      nextID = rs!OrderID
    End If
  End If
  MsgBox "Current " & CurrentID & " NextID " & nextID & vbCrLf & " LastRecord " & LastRecord & vbCrLf & " First Record " & FirstRecord

End Sub
Public Sub HiliteNext()
  Dim rs As DAO.Recordset
  Set rs = Me.Recordset
  rs.FindFirst "orderID = " & CurrentID
  If rs.NoMatch Then rs.FindFirst "OrderID = " & nextID
End Sub

I saved info on First Record but not used.

To use change the status to Exclude and remove it from the datasheet

 

[BlueSpruce]

Works for me I think.

Option Compare Database
Option Explicit

Private CurrentID As Long
Private nextID As Long
Private FirstRecord As Boolean
Private LastRecord As Boolean

Private Sub Form_Current()
    Me.txtLink = Me.OrderID
   ' for formatting
  End Sub

Public Function LaunchDetails()
  Dim rs As DAO.Recordset
  SetRecordData Me.OrderID
  DoCmd.OpenForm "frmOrderDetails", , , "OrderID = " & Me.OrderID, , acDialog
  Me.Requery
  HiliteNext
End Function

Public Sub SetRecordData(CurrentID As Long)
  Dim rs As Recordset
  Dim RC As Long
  Dim AP As Long
 
  Set rs = Me.RecordsetClone
  rs.FindFirst "OrderID = " & CurrentID
  AP = Me.Recordset.AbsolutePosition
  RC = rs.RecordCount
 
  FirstRecord = (AP = 0)
  LastRecord = (AP = RC - 1)
  If Not rs.EOF Then
    If LastRecord Then
      rs.MovePrevious
      nextID = rs!OrderID
    Else
      rs.MoveNext
      nextID = rs!OrderID
    End If
  End If
  MsgBox "Current " & CurrentID & " NextID " & nextID & vbCrLf & " LastRecord " & LastRecord & vbCrLf & " First Record " & FirstRecord

End Sub
Public Sub HiliteNext()
  Dim rs As DAO.Recordset
  Set rs = Me.Recordset
  rs.FindFirst "orderID = " & CurrentID
  If rs.NoMatch Then rs.FindFirst "OrderID = " & nextID
End Sub

I saved info on First Record but not used.

To use change the status to Exclude and remove it from the datasheet
View attachment 121065

View attachment 121066

I imagined it would work with rs clone, and without having to use bookmark. I tested arnel's code and it also works.

With Me.Recordset
    .FindFirst "ContactID = " & lngNextContactID
    Me.Bookmark = .Bookmark
End With

Thank you all for helping me with this, I'm back from hospital recovering.