Solved

Microsoft Access- FilterOn affects DoCmd.GoToRecord?

Posted on 2009-04-10
3
1,125 Views
Last Modified: 2013-11-28
Dear Experts,
  This is pretty strange...  I have a form with a subform.  The users can click on cmdDown or cmdUp to go thru the list of employees.  This works fine and always has.
  There is a checkbox that allows a filter to be enabled or not.  If the checkbox is checked, we enabled a filter and only show employees with violations.  If it's not checked, we turn the filter off to see all employees.  This works fine and always has.
  Now I'm trying to add the feature: If we have scrolled down the list of employees and then turn the filter on or off, I don't want to go back to the beginning of the list.  I want to stay on or around the employee that I had scrolled down to.  (the list is in alphabetical order)  See the code below.  THE CODE WORKS FINE WHEN THE CHECKBOX IS CHECKED (FILTER IS ON) BUT IT BREAKS WHEN THE CHECKBOX IS UNCHECKED (FILTER IS TURNED OFF).  WHY IS THIS?  Does the FilterOn property enable/disable DoCmd.GotoRecord?  The error I get is: run-time error 2105  You can't go to the specified record
Any help is appreciated!
Private Sub cmdDown_Click()

    On Error Resume Next 'if it tries to go beyond the eof

    DoCmd.GoToRecord , , acNext

End Sub
 

Private Sub cmdUp_Click()

    On Error Resume Next 'if it tries to go before the beginning of file

    DoCmd.GoToRecord , , acPrevious

End Sub
 

Private Sub chkShowOnlyTardyEmployees_AfterUpdate()

    If chkShowOnlyTardyEmployees = True Then

        glvarTemp = Me!FullName

        Me.FilterOn = True

        Me.Refresh

        Do While Me!FullName < glvarTemp

            DoCmd.GoToRecord , , acNext

        Loop

    Else

        glvarTemp = Me!FullName

        Me.FilterOn = False

        Me.Refresh

        Do While Me!FullName < glvarTemp

            DoCmd.GoToRecord , , acNext

        Loop

    End If    

End Sub

Open in new window

0
Comment
Question by:wilbur88
  • 2
3 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 24119175

instead of


        Do While Me!FullName < glvarTemp
            DoCmd.GoToRecord , , acNext
        Loop

use

       with me.recordsetclone
           .findfirst "[Fullname]='" & glvarTemp &"'"
           if not .nomatch then
                 me.bookmark=.bookmark
                 else
                 msgbox "Record not found"
           end if

      end with


0
 

Author Closing Comment

by:wilbur88
ID: 31569046
wonderful.  thanks so much.  do you have any idea what was wrong with my approach?  It worked when you checked the checkbox, but not when you cleared it...
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24120395
looks like the form lost track of the records, because the form records change
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
microsoft access - xml 10 29
Between formula (beginning of month) 11 18
Access MDB/PDF 21 32
Merge Statement 3 0
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

746 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

13 Experts available now in Live!

Get 1:1 Help Now