Solved

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

Posted on 2016-11-23
6
27 Views
Last Modified: 2016-11-23
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
Comment
Question by:Dustin Stanley
6 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 250 total points
Comment Utility
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
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 125 total points
Comment Utility
<<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
 
LVL 3

Assisted Solution

by:bfuchs
bfuchs earned 125 total points
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:Dustin Stanley
Comment Utility
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
 

Author Comment

by:Dustin Stanley
Comment Utility
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
 

Author Closing Comment

by:Dustin Stanley
Comment Utility
THANKS!
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now