DoCmd ApplyFilter performing funny

[Sue Compelling]

Hi

My Form (called Sales Estimates) is a split form (ie - Data Entry form on
top and datasheet below)

When the user types in the street name I want the filter to provide all
records also for that street name. My expression is:

Private Sub StreetName_AfterUpdate()
DoCmd.ApplyFilter , "[StreetName] = [Forms]![salesestimates]![StreetName]"
End Sub



Three wrong things are happening with this ie:

.... when I type Queen it comes back with all records in Queen (so far so good)

but when I clear the filter and then type Clarence

1) it comes back with all the Queen records
2) it clears all my sort requirements
3) the screen goes nuts and flickers (calculates) for about 10 secs

Help ...

TIA

 

[Mr. B]

Sue Compelling,

Try the following:

Dim strFilterVal as String
if Not IsNull(Me.StreetName) then
strFilterVal = Me.StreetName
Me.Filter = "StreetName = '" & strFilterVal & "'"
Me.FilterOn = True
Else
Me.FilterOn = False
End If

In the code above, I am assuming that the actual name of the control is
"StreetName". If that is not the actual name of the control where you are
typing in the street name, you would just use what ever the actual contol
name is.

Just my thoughts:
This is a case where having the text box control named the same as the field
name is confusing. That is why naming conventions are very effective.
Control names are easier to recogonize when the have something like
"txtStreetName" for the name of the control.

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

 

[Allen Browne]

Suggestions:

1. The cyclic reference could be the problem here, i.e. the filter depends
on the form, so after loading the records Access filters again, which ...
The example below avoids that by loading the value from the text box into a
string, and apply the literal value as the filter.

2. The text box where you enter the filter value needs to be unbound.
Otherwise you are saving the filter value in the current record. The example
below uses a text box that is not the same as a field name
(txtFilterStreet.)

3. Access has to save any edits to the current record before the filter can
be applied. Explicitly saving can avoid some weird errors.

4. However you are doing your sorting, perhaps you can use a similar
approach to the example below to set the form's OrderBy and OrderByOn
properties.

Private Sub StreetName_AfterUpdate()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If IsNull(Me.txtFilterStreet) Then
Me.FilterOn = False
Else
strWhere = "StreetName = """ & Me.txtFilterStreet & """"
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

HTH

 

[Sue Compelling]

Hi Mr B

Thanks for the txt tip and will change my naming conventions.

This has solved the ... not being able to refilter ... but the screen still
takes an unacceptable time to caluclate and stop flickering. Is there
anything I can do to stop this? The DB only has about 16,000 records (4,000
in the prime form and 11,000 linked child / master in the subform)

--
Sue Compelling

Sue Compelling,

Try the following:

Dim strFilterVal as String
if Not IsNull(Me.StreetName) then
strFilterVal = Me.StreetName
Me.Filter = "StreetName = '" & strFilterVal & "'"
Me.FilterOn = True
Else
Me.FilterOn = False
End If

In the code above, I am assuming that the actual name of the control is
"StreetName". If that is not the actual name of the control where you are
typing in the street name, you would just use what ever the actual contol
name is.

Just my thoughts:
This is a case where having the text box control named the same as the field
name is confusing. That is why naming conventions are very effective.
Control names are easier to recogonize when the have something like
"txtStreetName" for the name of the control.

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

Hi

My Form (called Sales Estimates) is a split form (ie - Data Entry form on
top and datasheet below)

When the user types in the street name I want the filter to provide all
records also for that street name. My expression is:

Private Sub StreetName_AfterUpdate()
DoCmd.ApplyFilter , "[StreetName] = [Forms]![salesestimates]![StreetName]"
End Sub



Three wrong things are happening with this ie:

... when I type Queen it comes back with all records in Queen (so far so good)

but when I clear the filter and then type Clarence

1) it comes back with all the Queen records
2) it clears all my sort requirements
3) the screen goes nuts and flickers (calculates) for about 10 secs

Help ...

TIA