Problem with Filter on Update (2000)

Topic

New Reply

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

Reply | Quote

Replies

Cynthia,
change this line to:

DoCmd.ApplyFilter , "ProgramID = '" & Forms!F_Participation!FindProgram & "'"

Reply | Quote

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?

Reply | Quote

Is ProgramID a number ?
then try :

DoCmd.ApplyFilter , "ProgramID = " & Forms!F_Participation!FindProgram

Reply | Quote

That works!
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

Reply | Quote

I would if I could
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

Reply | Quote

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

Reply | Quote