Subform FilterOn = False; is locking up

[shanesullaway]

A2K

I am using the code below to filter a subform[subCusDet] on MainForm
[frmCustomers], (using a pop up form). If records are found I allow the
subform to be filtered and this part works fine. If no records are found I am
trying to set the FilterOn back to false. At this point I'm getting an error
that the filter failed and then the db locks up. Any thoughts as to why? I
use this same code on other forms, except that it's filtering the form it's
on and not using a pop up form, and it works fine.

Also, while I'm at it. I seem to recall reading that filtering is not real
reliable and can cause goofy problems. Is that true? Should I be doing this
using recordsets instead?

Below is my code. Thanks for any help you can provide,
Shane

Dim stFilter As String
stFilter = "CreatedBy='" & Me.cboEnteredBy.Column(1) & "'"

Forms![frmCustomers]![subCusDet].Form.Filter = stFilter
Forms![frmCustomers]![subCusDet].Form.FilterOn = True
Forms![frmCustomers]![subCusDet].Form.Refresh
Dim stRCount As String
stRCount = Forms![frmCustomers]![subCusDet].Form.RecordsetClone.RecordCount

If stRCount <= 0 Then
Eval ("Msgbox('NO MATCHING PROGRESS NOTES!@The filter you chose did not
produce a match!. " & _
"The Progress Notes List will be reset Showing All Progress Notes@@',0,'Amigo
Message System')")
Forms![frmCustomers]![subCusDet].Form.FilterOn = False
Forms![frmCustomers]![subCusDet].Form.Requery
End If

 

[Allen Browne]

If you succeeded in setting the form's Filter so it returns no records, it
should be possible to turn off its FilterOn again.

There Refresh/Requery is unnecessary. You might want to check that the combo
has a value, the 2nd column contains something other than a zero-length
string, and that the subform is not dirty. The example below does that.

If this still fails:
a) Could there be a filter on the main form as well? If so, the attempt to
remove the subform filter could fail:
http://allenbrowne.com/bug-02.html

b) Is there any code in the subform's events that could cause the problem?
For example, code in its Current event could be the issue.

c) Does the subform's Detail section go completely blank when this occurs?
It does if no new records can be added (e.g. AllowAdditions is off, or the
RecordSource is a read-only query.) There are some strange and inconsistent
things that happen here. The particular code you have should be okay, but
anything that tries to read a value from a control in the subform is likely
to fail:
http://allenbrowne.com/bug-06.html

Try:

Dim stFilter As String
If Nz(Me.cboEnteredBy.Column(1), vbNullString) <> vbNullString Then
stFilter = "CreatedBy=""" & Me.cboEnteredBy.Column(1) & """"
End If
With Forms![frmCustomers]![subCusDet].Form
If .Dirty Then
.Dirty = False
End If
.Filter = stFilter
.FilterOn = True
If .RecordsetClone.RecordCount = 0 Then
MsgBox "no matches"
.FilterOn = False
End If
End With

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

A2K

I am using the code below to filter a subform[subCusDet] on MainForm
[frmCustomers], (using a pop up form). If records are found I allow the
subform to be filtered and this part works fine. If no records are found I
am
trying to set the FilterOn back to false. At this point I'm getting an
error
that the filter failed and then the db locks up. Any thoughts as to why? I
use this same code on other forms, except that it's filtering the form
it's
on and not using a pop up form, and it works fine.

Also, while I'm at it. I seem to recall reading that filtering is not real
reliable and can cause goofy problems. Is that true? Should I be doing
this
using recordsets instead?

Below is my code. Thanks for any help you can provide,
Shane

Dim stFilter As String
stFilter = "CreatedBy='" & Me.cboEnteredBy.Column(1) & "'"

Forms![frmCustomers]![subCusDet].Form.Filter = stFilter
Forms![frmCustomers]![subCusDet].Form.FilterOn = True
Forms![frmCustomers]![subCusDet].Form.Refresh
Dim stRCount As String
stRCount =
Forms![frmCustomers]![subCusDet].Form.RecordsetClone.RecordCount

If stRCount <= 0 Then
Eval ("Msgbox('NO MATCHING PROGRESS NOTES!@The filter you chose did not
produce a match!. " & _
"The Progress Notes List will be reset Showing All Progress
Notes@@',0,'Amigo
Message System')")
Forms![frmCustomers]![subCusDet].Form.FilterOn = False
Forms![frmCustomers]![subCusDet].Form.Requery
End If

 

[shanesullaway via AccessMonster.com]

Thank you for taking the time to reply, Mr. Browne,

The Refresh/Requery part wasn't in there originally. I kept working on this
trying to get something to work, so getting rid of that is no problem. I
will check into the other things that you have recommended.

Would using the subforms RecordSource be a better and more dependable way to
filter it? I remember reading something a while back, that someone was
saying something about how the Filter part of Access is unreliable. Is that
accurate?

Thanks again for taking the time to help,
Shane.

If you succeeded in setting the form's Filter so it returns no records, it
should be possible to turn off its FilterOn again.

There Refresh/Requery is unnecessary. You might want to check that the combo
has a value, the 2nd column contains something other than a zero-length
string, and that the subform is not dirty. The example below does that.

If this still fails:
a) Could there be a filter on the main form as well? If so, the attempt to
remove the subform filter could fail:
http://allenbrowne.com/bug-02.html

b) Is there any code in the subform's events that could cause the problem?
For example, code in its Current event could be the issue.

c) Does the subform's Detail section go completely blank when this occurs?
It does if no new records can be added (e.g. AllowAdditions is off, or the
RecordSource is a read-only query.) There are some strange and inconsistent
things that happen here. The particular code you have should be okay, but
anything that tries to read a value from a control in the subform is likely
to fail:
http://allenbrowne.com/bug-06.html

Try:

Dim stFilter As String
If Nz(Me.cboEnteredBy.Column(1), vbNullString) <> vbNullString Then
stFilter = "CreatedBy=""" & Me.cboEnteredBy.Column(1) & """"
End If
With Forms![frmCustomers]![subCusDet].Form
If .Dirty Then
.Dirty = False
End If
.Filter = stFilter
.FilterOn = True
If .RecordsetClone.RecordCount = 0 Then
MsgBox "no matches"
.FilterOn = False
End If
End With

A2K

[quoted text clipped - 36 lines]

Forms![frmCustomers]![subCusDet].Form.Requery
End If

 

[Allen Browne]

No. The claim that "the Filter part of Access is unreliable" is an
overstatement. The filters work, but there are specific cases where they
need to be avoided. The article helps you identify those.

Setting the form's RecordSource is fine also. Personally, I use that
approach where I don't want the user removing the filter, but use filters
where the user should have the flexibility to easily remove them.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for taking the time to reply, Mr. Browne,

The Refresh/Requery part wasn't in there originally. I kept working on
this
trying to get something to work, so getting rid of that is no problem. I
will check into the other things that you have recommended.

Would using the subforms RecordSource be a better and more dependable way
to
filter it? I remember reading something a while back, that someone was
saying something about how the Filter part of Access is unreliable. Is
that
accurate?

Thanks again for taking the time to help,
Shane.

If you succeeded in setting the form's Filter so it returns no records, it
should be possible to turn off its FilterOn again.

There Refresh/Requery is unnecessary. You might want to check that the
combo
has a value, the 2nd column contains something other than a zero-length
string, and that the subform is not dirty. The example below does that.

If this still fails:
a) Could there be a filter on the main form as well? If so, the attempt to
remove the subform filter could fail:
http://allenbrowne.com/bug-02.html

b) Is there any code in the subform's events that could cause the problem?
For example, code in its Current event could be the issue.

c) Does the subform's Detail section go completely blank when this occurs?
It does if no new records can be added (e.g. AllowAdditions is off, or the
RecordSource is a read-only query.) There are some strange and
inconsistent
things that happen here. The particular code you have should be okay, but
anything that tries to read a value from a control in the subform is
likely
to fail:
http://allenbrowne.com/bug-06.html

Try:

Dim stFilter As String
If Nz(Me.cboEnteredBy.Column(1), vbNullString) <> vbNullString Then
stFilter = "CreatedBy=""" & Me.cboEnteredBy.Column(1) & """"
End If
With Forms![frmCustomers]![subCusDet].Form
If .Dirty Then
.Dirty = False
End If
.Filter = stFilter
.FilterOn = True
If .RecordsetClone.RecordCount = 0 Then
MsgBox "no matches"
.FilterOn = False
End If
End With

A2K

[quoted text clipped - 36 lines]

Forms![frmCustomers]![subCusDet].Form.Requery
End If