• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 135
  • Last Modified:

MS Access Is there a better way to remove the filter while in a form and stay on record then what I have

If I come into my form and it is filtered then I have the code below to remove the filter and go to the previous record. i have the same for a next record button. But with the way i have it now then the form gets a flashing as it is doing its thing through the coding and you even see it go back to the first record and then back to the bookmark and then to the previous record.

I truly would like for it to just remove the filter and go to the previous record smoothly. I know we all want something :)

So is there a way to make this look more pleasing to the eye?

I would like to keep it simple and smooth.  Thanks for the help!
Private Sub btnRecordSKUPrevious_Click()

      If Me.FilterOn = True Then
         GoTo RemoveFilter
         Else
  End If
  
         If Recordset.BOF Then
             msgbox "At The Beginning. Now Going To Last Record.", vbInformation, ""Information""
            Recordset.MoveLast
    Else
        Me.Recordset.MovePrevious
     End If
     Exit Sub
     
RemoveFilter:
 Dim intID As Variant
Dim rs As Object
intID = Me.SkuID
Me.FilterOn = False
Set rs = Me.RecordsetClone
With rs
.FindFirst "[SkuID]=" & intID
Me.Bookmark = .Bookmark
End With
rs.Close
If Recordset.EOF Then
   msgbox "At The End. Now Going To The First Record.", vbInformation, "Information"
    Recordset.MoveFirst
 Else
     Me.Recordset.MovePrevious
End If

 End Sub

Open in new window

0
Dustin Stanley
Asked:
Dustin Stanley
3 Solutions
 
Rey Obrero (Capricorn1)Commented:
try something like this

RemoveFilter:
 Dim intID As Variant, prevID as variant
Dim rs As Object
intID = Me.SkuID
prevID=Dmax("SkuID", "TableOrQueryName", "[SkuID] < " & intID)
Me.FilterOn = False
Set rs = Me.RecordsetClone
With rs
.FindFirst "[SkuID]=" & prevID
Me.Bookmark = .Bookmark
End With
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<So is there a way to make this look more pleasing to the eye?

I would like to keep it simple and smooth.  Thanks for the help!>>

 You always want to work with the recordset clone (which is another cursor or "pointer" into the forms recordset), then match.

  So first you want to do:

Set rs = Me.RecordsetClone
rs.bookmark = Me.Bookmark

 Now your "syncd" and can move forward/backward in the clone to determine which record you want to land on.

 Then removed the filter, and now do a find on the clone, or move first or last. Then match up again with Me.Bookmark = rs.bookmark.

Jim.
0
 
bfuchsCommented:
instead of using filter, try changing the forms record source to give just the desired record, then after selecting a record requery the form.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Dustin StanleyEntrepreneurAuthor Commented:
Ok so I had some extra coding in there that I honestly didn't need and I cleaned it up a bit. I based it off Reys Example and it seems to work really well except you can still see it go to Record 1 before it goes back to the bookmarked SkuID. How can I get it not to show this?
Could I use a loading screen or something to block this rapid flashing nonsense???

Thank you guys for the help!

Private Sub btnRecordSKUNext_Click()

 If Me.FilterOn = True Then
         Dim intID As Variant, NextID As Variant
           Dim rs As Object
            intID = Me.SkuID
             NextID = intID + 1
              Me.FilterOn = False
            Set rs = Me.RecordsetClone
        With rs
           .FindFirst "[SkuID]=" & NextID
             Me.Bookmark = .Bookmark
        End With
      Else
        Me.Recordset.MoveNext
       End If

    If Recordset.EOF Then
   msgbox "At The End. Now Going To The First Record.", vbInformation, "Information"
     Recordset.MoveFirst
    End If

 End Sub


Private Sub btnRecordSKUPrevious_Click()

      If Me.FilterOn = True Then
         Dim intID As Variant, prevID As Variant
           Dim rs As Object
            intID = Me.SkuID
             prevID = intID - 1
              Me.FilterOn = False
            Set rs = Me.RecordsetClone
        With rs
           .FindFirst "[SkuID]=" & prevID
             Me.Bookmark = .Bookmark
        End With
      Else
        Me.Recordset.MovePrevious
       End If

    If Recordset.BOF Then
      msgbox "At The Beginning. Now Going To Last Record.", vbInformation, "Information"
        Recordset.MoveLast
     End If

 End Sub

Open in new window

0
 
Dustin StanleyEntrepreneurAuthor Commented:
Ok this is what I came up with and it seems to work OK for me for now. It makes a Subform (sbfrmLoadingscreen) visible and then invisible during the remove filter. It still flashes some what but at least it says Loading.... when it is doing it and you don't see the steps or it going to the first record and then back. So it seems MENTALLY more pleasing to the user and not like the PC has been taken over ;)  The Subform is just a blank form that I made large enough to cover the PC screen and put an image control in it with a Logo and the word "Loading....."

Private Sub btnRecordSKUNext_Click()

 If Me.FilterOn = True Then
 Me.sbfrmLoadingScreen.Visible = True
         Dim intID As Variant, NextID As Variant
           Dim rs As Object
            intID = Me.SkuID
             NextID = intID + 1
              Me.FilterOn = False
            Set rs = Me.RecordsetClone
        With rs
           .FindFirst "[SkuID]=" & NextID
             Me.Bookmark = .Bookmark
        End With
        Me.sbfrmLoadingScreen.Visible = False
      Else
        Me.Recordset.MoveNext
       End If

    If Recordset.EOF Then
   msgbox "At The End. Now Going To The First Record.", vbInformation, "Information"
     Recordset.MoveFirst
    End If

 End Sub


Private Sub btnRecordSKUPrevious_Click()

      If Me.FilterOn = True Then
      Me.sbfrmLoadingScreen.Visible = True
         Dim intID As Variant, prevID As Variant
           Dim rs As Object
            intID = Me.SkuID
             prevID = intID - 1
              Me.FilterOn = False
            Set rs = Me.RecordsetClone
        With rs
           .FindFirst "[SkuID]=" & prevID
             Me.Bookmark = .Bookmark
        End With
        Me.sbfrmLoadingScreen.Visible = False
      Else
        Me.Recordset.MovePrevious
       End If

    If Recordset.BOF Then
      msgbox "At The Beginning. Now Going To Last Record.", vbInformation, "Information"
        Recordset.MoveLast
     End If

 End Sub

Open in new window

0
 
Dustin StanleyEntrepreneurAuthor Commented:
THANKS!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now