Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem with Filter on Update (2000)

    I am having a problem with a form, which used to work without problems.
    I have a combo box which used to filter the data to show only members who attended the program selected in the combobox. Suddenly, this feature stopped working.

    This database was created in Access 97 and converted to Access 2000 some time ago. This form still seems to work fine in Access 97 but wll not work in Access 2000.

    The form that is not working is called F_Participation.
    I'm wondering if the problem might be associated with a "workaround" I devised when they told me in my Access class that there was no way to have the form open up already filtered to the last program I was working with. I have a form which runs in the background whenever this form is opened. I call that form F_Memory. F_Memory updates the single value in a table called T_Memory each time the combobox in my form F_Participation is updated.

    Here is the code for the After Update event for the FindProgram field in F_Participation:

    Private Sub FindProgram_AfterUpdate()

    [Forms]![F_Memory].[ProgramMem] = Me![FindProgram]
    DoCmd.ApplyFilter , "ProgramID = Forms!F_Participation!FindProgram"

    End Sub

    If I run this form in any version of the database in Access97 it still works perfectly - filters for the members who attended the selected program and remembers which program to open with next time. If I run it in any version of the database in Access 2000 the memory feature still works fine, that is the form opens with the last program selected before the form was closed. But the only way to get the members in F_Participation to filter properly is to close and reopen the form so the filtering is done by the memory feature. When a user changes the value in the FindProgram combobox, the list of members SHOULD - and did use to - change!

    Any clues how I can fix this so Access 2000 can understand it? I'd like to do it and still keep the memory feature, since we are generally working primarily with a single program at a time and it's nice to have the form open to that program.

    thanks,
    -cynthia

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Filter on Update (2000)

    Cynthia,
    change this line to:
    <pre>DoCmd.ApplyFilter , "ProgramID = '" & Forms!F_Participation!FindProgram & "'"</pre>

    Francois

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Filter on Update (2000)

    We're not quite there yet -
    I cut and pasted that line directly out of your message and replaced my original line. Now when I update the FindProgram combobox I now get an error message "Run-Time Error '2501': The ApplyFilter action was canceled."

    Is there something else I should try?

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Filter on Update (2000)

    Is ProgramID a number ?
    then try :

    <pre>DoCmd.ApplyFilter , "ProgramID = " & Forms!F_Participation!FindProgram
    </pre>

    Francois

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Filter on Update (2000)

    That works! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    And yes, ProgramID is a number (primary key autonumber in the table).
    Can you explain anything about why this changed between 97 and 2000 so I can understand what I need to do if I run into it in other databases (which I'm sure to since I go back and forth between the two all the time!)?
    Thank you very much,
    -cynthia

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Filter on Update (2000)

    I would if I could <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    The syntax is the same for Access 2000 and 97.
    Check the code in 97. Maybe something was going wrong at the converting to 2000?
    You have to pass a where condition.
    If the filtering field is a number you must past "ProgramID = 123"
    If the filtering field is a string you must past the string in quotes "Name = 'Francois' "
    That why the first line I give you didn't work. I was thinking that ProgramID was a string <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Francois

  7. #7
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Filter on Update (2000)

    Oh well! I think with your latest I at least understand it enough that I can fix it if it shows up again, even if it does baffle me!
    Funny thing - with my original code, when I convert it to 2000 it doesn't work. But then if I convert it back to 97, it works again!
    What quirks we run into.

    Thank you very much for your help!
    -cynthia

Posting Permissions

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