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

[Deleted Bruce 182381]

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).

 

[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

 

[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.

 

[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.

 

[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.

 

[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

 

[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

 

[Ken Sheridan]

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?

A set has no intrinsic order. When we talk of the next record we are usually referring to the next record in the current sort order, which we can do here because that's what we want, regardless of what that sort order might be. However in another context we might want to go to a record in a sort order which differs from the current sort order, in which case we would do so on the basis of the values in the record in the way I described.

 

[MajP]

It also lags when scrolling through or clicking directly on any record. I thought it would be faster because recordset is in memory and ContractID is indexed?

This is likely having to do with conditional formatting, I doubt there is a code issue.

My solution works regardless of how you sort or filter the datasheet.

 

[MajP]

I do have CF primarily color coding values on that datasheet, concating suffix values to contract numbers, etc

My point is that I doubt any of the lagging has to do with identifying and moving to the "new" correct record. So you have to do some other troubleshooting. I would see what happens if you test it without CF.
You cannot upload a video unless you zip it as a file. To display you need to host it somewhere.

 

[cheekybuddha]

This UI looks familiar.

Is that you, Frank?

 

[cheekybuddha]

No, I bought the rights to his app because he quit software development, bought a tractor trailer, and drives cross country. I think he was fedup with what's going on with the software development industry, AI, and changed careers.

Is it still using a postgres backend?

 

[cheekybuddha]

I don't think it ever had Postgres

Interesting. Frank talked a lot about using it for the BE, but perhaps he was looking to implement it.

I'm still trying to wrap my head around this app. It looks like it was originally written for Spanish locale and he started changing it to English.

Shame UA is no more, he asked a lot of questions about it there.

 

[cheekybuddha]

I recently saw Daniel unfairly badmouthing AWF on his blog's evaluation of the various Access forums, and I brought it to @Jon's attention,

You're going a bit off topic here, but I think Daniel's entitled to his opinion.