Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Location
    HOBART, Tasmania, Australia
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter applied that blocks whole recordset (Access 2002)

    Hi all,

    When I open a form using:

    DoCmd.OpenForm "FrmTemplates", acNormal, , "[ID] = " & Me.ID

    in VB the form opens with a filtered recordset of 0 items. The form only displays the record I'm after when I select the menu option Records->Filter->Filter excluding selection. Sort of suggests there is another filter overiding the filter I instigate when I open the form. BTW, when I open the form directly from the database window the same thing happens, but all the entries in the underlying query are viewable.

    What have I done?

    Thanks,

    Ryan

  2. #2
    New Lounger
    Join Date
    Feb 2003
    Location
    HOBART, Tasmania, Australia
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter applied that blocks whole recordset (Access 2002)

    addendum to above, when I open the form with no filter, remove filters using the records menu to reveal the whole recordset, switch to design view and view the form properties I find the following line in the filters property:

    ([ID] = 10) AND ((Not TblTemplates.ID=0))

    Where did this come from? I was viewing the record with [ID] = 10 but as to the expression right of the AND, I have no idea...

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

    Re: Filter applied that blocks whole recordset (Access 2002)

    Delete the filter from the filters property, try it again and see what happens. Check whether the filters property is the same as before with the AND in it.
    BTW what is the source of the form?

  4. #4
    New Lounger
    Join Date
    Feb 2003
    Location
    HOBART, Tasmania, Australia
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter applied that blocks whole recordset (Access 2002)

    I must have hit save after selecting Records->Filter->filter excluding selection. That explains the bit after AND. The current selection was [ID] = 0 (no record) and it put (Not TblTemplates.ID=0) into the filter property. Still getting the same problem tho no matter how I open the form. With or without a filter applied the form shows a filtered recordset of 1 item until I remove filters using the menu.

    Thanks for your assistance Pat

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

    Re: Filter applied that blocks whole recordset (Access 2002)

    I suspect that the following is happening. The instruction

    DoCmd.OpenForm "FrmTemplates", acNormal, , "[ID] = " & Me.ID

    assumes that you open FrmTemplates from a form that is positioned on a record with a valid ID. If that form is positioned on a new record that hasn't been saved yet, the ID will be empty, or if a default value of 0 has been set, ID will be 0. But since no record in the record source of FrmTemplates corresponds to this, it opens with an empty recordset. Setting Filter Excluding Selection would account for the "And Not(ID = 0)" part of the filter.

    Another possibility is that FrmTemplates has its DataEntry property set to Yes. This would open the form on a new record, regardless of the filter you try to set.

  6. #6
    New Lounger
    Join Date
    Feb 2003
    Location
    HOBART, Tasmania, Australia
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter applied that blocks whole recordset (Access 2002)

    Yes, I had the data entry set to "Yes". Thanks Hans, that's fixed the problem.

    Really appreciate the speedy responses :-)

Posting Permissions

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