Form Filter Black Hole (1 Viewer)

sxschech

Registered User.
Local time
Today, 02:36
Joined
Mar 2, 2010
Messages
791
I'm using the following code to refresh the form when a button is clicked:
Code:
rs.FindFirst "Subject=" & Chr(34) & stSubject & Chr(34) & " AND [DateSent]=#" & dtDateSent & "#"
        Me.Bookmark = rs.Bookmark

It works well. I discovered a "black hole" scenario and would like to know how I can modify the code so that if the FindFirst fails, it will go to the next valid/available record (based on the applied filter) on the form rather than defaulting to the first valid record on the form.

For example, table has 100 rows, form is filtered and shows 30 rows. Current record is 19 of 30. If the current record no longer meets the criteria, the form now shows 1 of 29. Ideally, would like the form to be on 19 of 29. Since we don't know which one will become record 19, how can this be done? Would I need a before update to identify the current record in the recordset, do a move next and capture the id of that record?

Here is the situation, normally, when I'm using the filter by form option from the ribbon, I filter on something that will not change. Today, I needed to add another field to the filter to exclude those records that are complete. After I mark it as complete when done, the form refreshes and since the filter excludes the complete, the form refreshes to the first available record rather than the next available record. This causes a problem because the first available record, I was unable to complete and since my workflow has been that it returns to the current record, out of habit I start working on the "next" record, which it is not and then this record falls into the black hole because the method I'm using to show the status is a cycle, each click changes the status of the record (blank, to do, completed, blank...) so when I click to fix it, it ends up being marked as complete and disappears. If I don't remember which one it was, then I have no easy way to undo.
 

Ranman256

Well-known member
Local time
Today, 05:36
Joined
Apr 9, 2015
Messages
4,337
stop using findfirst and just filter ALL records to the criteria.
then every record is correct. you wont need to find them.

Code:
sub cboBox_Afterupdate()
If IsNull(cboBox) Then
  Me.FilterOn = False
Else
  Me.Filter = "[CCode]='" & cboBox & "'"
  Me.FilterOn = True
End If
end sub
 

sxschech

Registered User.
Local time
Today, 02:36
Joined
Mar 2, 2010
Messages
791
Thanks for responding. Not sure how that approach will solve my issue. Maybe tomorrow can post a screen shot if that would help. In the meantime, perhaps there is another approach that can show the updates without needing to do a full requery of the form which led to me using the Find First. Part of what the form does is edit data in Outlook such as changing flag status and categories (red, green, blue, etc) and then redownloads the data from a specific email folder. In order to see the updates change on the form I end up doing a requery which, without the find first resets the pointer to the first record. I am downloading the data because not all the fields I'm grabbing seemed to be available in the Link to Outlook method and also, at least on my computer, the link ran slower than simply downloading the data to a table.
 

sxschech

Registered User.
Local time
Today, 02:36
Joined
Mar 2, 2010
Messages
791
Thanks for the link, looks possible. I'll give it a try tomorrow.

I saw a similar example in one of my earlier searches except that in their NoMatch example it only put a message box saying there was no match while this one seems to include a recordset to "store the current location", hopefully that will do the trick.
 

sxschech

Registered User.
Local time
Today, 02:36
Joined
Mar 2, 2010
Messages
791
After a bit more searching, here is what I ended up with...

Code:
recno = Me.CurrentRecord
    Me.Requery
    rs.FindFirst "[Subject]=" & Chr(34) & stSubject & Chr(34) & " AND [DateSent]=#" & dtDateSent & "#"
    If rs.NoMatch Then
        MsgBox "The status " & intFlagType & " meets the criteria of the filter " & _
               "and is now excluded from view." & vbCrLf & "Form will display next " & _
               "filtered record.  In order to see the record that was just edited, " & _
               "please change or remove the existing filter.", vbOKOnly + vbInformation, "Black Hole Filter"
        
        If Me.Recordset.RecordCount > 0 Then
            If recno - 1 = Me.Recordset.RecordCount Then
                DoCmd.GoToRecord acDataForm, "frmCaltrainInbox", acGoTo, recno - 1
            Else
                DoCmd.GoToRecord acDataForm, "frmCaltrainInbox", acGoTo, recno
            End If
        End If
    Else
        Me.Bookmark = rs.Bookmark
    End If

bits and pieces from:
https://docs.microsoft.com/en-us/office/vba/api/access.form.currentrecord
https://www.tek-tips.com/viewthread.cfm?qid=501152
https://access-programmers.co.uk/forums/showthread.php?t=282269
 

sxschech

Registered User.
Local time
Today, 02:36
Joined
Mar 2, 2010
Messages
791
Revised NoMatch code in order to allow option to keep the record visible once it meets the filter criteria ... in other words, yank it back from the void.

Code:
If rs.NoMatch Then
        yesno = MsgBox("The status now meets the criteria of the filter " & _
               "and will be excluded from view." & vbCrLf & vbCrLf & "Form will display next " & _
               "filtered record." & vbCrLf & vbCrLf & "In order to see the record that was just edited, " & _
               "please change or remove the existing filter." & vbCrLf & vbCrLf & "Keep this record " & _
               "visible?", vbYesNo + vbInformation, "Black Hole Filter")
        If yesno = vbYes Then
            Me.Filter = Me.Filter & " OR (Subject=" & Chr(34) & stSubject & Chr(34) & " AND [DateSent]=#" & dtDateSent & "#)"
            Me.FilterOn = True
        End If
        If Me.Recordset.RecordCount > 0 Then
            If recno - 1 = Me.Recordset.RecordCount Then
                DoCmd.GoToRecord acDataForm, "frmCaltrainInbox", acGoTo, recno - 1
            Else
                DoCmd.GoToRecord acDataForm, "frmCaltrainInbox", acGoTo, recno
            End If
        End If
    Else
 

Users who are viewing this thread

Top Bottom