Results 1 to 9 of 9
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    ApplyFilter (A97 Sr2)

    Hi All,
    Whenever I have a Combo box to select a record for a form, I use the DoCmd.ApplyFilter command to display the selected record.
    Is this the most desirable way to do this?
    If there is a preferred way would you please let me know.
    Thanks,
    Pat

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ApplyFilter (A97 Sr2)

    It depends on how you want the form to behave. Applying a filter will reduce the recordset to the particular record you want to display and the user will not be able to navigate to any other record. There are other alternatives.

    One is to use a recordsetclone to find the selected record, then set the form's bookmark to match the bookmark in the recordsetclone. However, that leave the entire recordset accessible to the user unless you otherwise disable their ability to navigate.

    Another is to load the form with an empty recordset in the first place (i.e., use SELECT * FROM MyTable WHERE 1 = 2) and then change the form's recordsource to a SELECT statement that only returns the selected record after a choice is made in the combobox. I've found this approach to be faster in general than applying a filter. It has the added benefit of making the form load very quickly regardless of how complex a form it might be.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ApplyFilter (A97 Sr2)

    I'm not certain which is more desirable, but I use:

    <pre>Private Sub FindCourse_AfterUpdate()
    'Find the record that matches the control

    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[CourseNumber] = " & "'" & Me![FindCourse] & "'"
    Me.Bookmark = rs.Bookmark
    Me![FindCourse] = ""

    End Sub</pre>


    on the AfterUpdate event of the combobox. This does not "filter" the records, but merely finds/displays the matching one, which is what I want to do. In the example, my combobox is named FindCourse and is bound to a field named CourseNumber. You will need to change those to your combobox and field names if you choose to use it.

    HTH

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ApplyFilter (A97 Sr2)

    That looks like Access 200x code. I don't recall a clone method in A97.
    Charlotte

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ApplyFilter (A97 Sr2)

    Indeed, the Clone method doesn't exist in Access 97, but you can use Me.RecordsetClone instead.

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ApplyFilter (A97 Sr2)

    I apologize - I didn't check the version number and pulled Access 2000 code. This is what I use in Access 97:

    <pre>Sub FindCourse_AfterUpdate()
    'Find the record that matches the combo box

    Dim vFindCourse
    Dim vCriteria As String

    'build criteria from combo box
    vCriteria = "CourseNumber = '" & "'" & Me![FindCourse] & "'"
    'find and bookmark matching record
    Me.RecordsetClone.FindFirst vCriteria
    Me.Bookmark = Me.RecordsetClone.Bookmark
    'reset combo box
    Me!FindCourse = ""

    End Sub</pre>

    Thank you for catching that.

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: ApplyFilter (A97 Sr2)

    Thanks for your time guys.
    Pat

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: ApplyFilter (A97 Sr2)

    I seem to have run into a hitch with this approach - I built a reasonably complex form with a "memo" field as one of it's components. I also have some code for the BeforeUpdate event on the form to capture the date/time and the userid making the change. When we update the memo field and then try to navigate to another record, more often than not we get a Run-time error '3020' - Update or CancelUpdate without AddNew or Edit. This is occuring on A2k sr1 on several client PCs - I cannot duplicate it on my (presumably faster) PC.

    I think the cause is that the record has not been saved before the code tries to do the Me.Recordset.Clone, and that we may have to check to see if the form is dirty and force a save before we do the Clone. Has anyone else seen this kind of behavior?
    Wendell

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: ApplyFilter (A97 Sr2)

    I had this problem (Error 3020) constantly when upgraded fm ACC 97 to A2K whenever using RecordsetClone methods to navigate to new record on form, typically using combobox AfterUpdate events. The error would not occur when using built-in navigation buttons or GoToRecord commands. It did not have to be a memo field, error would occur when updating any field on form and not explicitly saving record before using combobox to move to a new record. So I quickly got in habit of adding "If Me.dirty then Me.Refresh" statements whenever using RecordsetClone for navigational purposes. I have not been successful in finding an explanation for this behavior in the MSKB or elsewhere.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •