Applying a filter with code

[Geoff D]

I'm trying to make an Access 2003 database work in 2007. One feature that
doens't work in 2007 involves applying a filter to the list of sites.
There's a button which opens a form where you pick from the list of sites.
This is the code for the button ...

Private Sub Find_Click()
DoCmd.OpenForm "Select Site Name", , , , , A_DIALOG
If IsNull([Forms]![Select Site Name]![site name]) Then
GoTo EndProc
End If
DoCmd.ApplyFilter , "[Site Name] Like [Forms]![select site name]![site
name]"
EndProc:
DoCmd.Close A_FORM, "Select Site Name"
End Sub

I put out the [site name] with MsgBox after the form, and this is correct,
but the filter only works the first time. Subsequent attempts only display
the first filter.

This is causing me some frustration! Advice would be most welcome.

Geoff

 

[ErezM via AccessMonster.com]

hi
it's strange that you say it used to work in access 2003, cause the DIALOG
opening of the select form means the execution doesnt return to your code
until the form is closed,
which means [Forms]![select site name]![site name] doesnt exist anymore, when
your code looks for it's value

move the filtering (and checking for nulls) to the dialog form, and on the
way, change the filtering mechanism to:

Forms![The Name Of The Calling Form].Filter="[site name]='" & [whatever] &
"'"
Forms![The Name Of The Calling Form].FilterOn=True

it's faster than DoCmd operations, and you can set it to other objects, not
just the active one.

good luck
Erez

I'm trying to make an Access 2003 database work in 2007. One feature that
doens't work in 2007 involves applying a filter to the list of sites.
There's a button which opens a form where you pick from the list of sites.
This is the code for the button ...

Private Sub Find_Click()
DoCmd.OpenForm "Select Site Name", , , , , A_DIALOG
If IsNull([Forms]![Select Site Name]![site name]) Then
GoTo EndProc
End If
DoCmd.ApplyFilter , "[Site Name] Like [Forms]![select site name]![site
name]"
EndProc:
DoCmd.Close A_FORM, "Select Site Name"
End Sub

I put out the [site name] with MsgBox after the form, and this is correct,
but the filter only works the first time. Subsequent attempts only display
the first filter.

This is causing me some frustration! Advice would be most welcome.

Geoff