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
74 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 41899824
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 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 125 total points
ID: 41899854
<<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 4

Assisted Solution

by:bfuchs
bfuchs earned 125 total points
ID: 41899880
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

by:Dustin Stanley
ID: 41899955
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
ID: 41899972
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
ID: 41900023
THANKS!
0

Featured Post

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

705 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