Option Group filtering

[Guest]

Hi

I have a 3 way option group whose buttons filter the forms recordset.

In prinicple it works fine but certain sequences of button presses cause an
un-trapable crash with the message:

The macro or function set to the BeforeUpdate or ValidationRule property for
this field is preventing "app name" from saving the data in the field.

The code is show below.

The option group is unbound and its default value is 1.

The fault can always be provoked by pressing either button 2 or 3, followed
by button 1. This seems to arm the form ready for a crash and pressing either
2 or3 causes instant failure.

I think that the fact that option 1 is for all the records where as options
2 and 3 apply a filter is significant but can't seen what i'm doing wrong.

NB The crash occurs instantly before any of the debug break points show any
lines of code being executed.

Thanks for any help.

Private Sub optBusiness_Filter_AfterUpdate()
On Error GoTo Err_optBusiness_Filter_AfterUpdate

If Me.optBusiness_Filter = 1 Then

DoCmd.ShowAllRecords
Me.txtGet_BU_Id.Requery

End If

If Me.optBusiness_Filter = 2 Then

DoCmd.ApplyFilter , "BU_Accepted_Date Is not Null and BU_Date_Sold
is null"
Me.Requery
Me.txtGet_BU_Id.Requery

End If

If Me.optBusiness_Filter = 3 Then

DoCmd.ApplyFilter , "BU_Accepted_Date is not null and BU_Date_Sold
Is Not Null"
Me.txtGet_BU_Id.Requery

End If

If Me.RecordsetClone.RecordCount > 0 Then

DoCmd.GoToControl "txtBU_Name"
Me.txtGet_BU_Id.Requery

End If

If Me.RecordsetClone.RecordCount = 0 Then

MsgBox "There are no records."
DoCmd.ShowAllRecords
Me.optBusiness_Filter = 1
Me.txtGet_BU_Id.Requery
DoCmd.GoToControl "txtBU_Name"

End If

Exit_optBusiness_Filter_AfterUpdate:
Exit Sub

Err_optBusiness_Filter_AfterUpdate:
MsgBox Err.Description
Resume Exit_optBusiness_Filter_AfterUpdate

End Sub

 

[manningfan]

I always find it safer to use a query behind a form. Using form
filtering is flaky at times.

Write a query where the filter is based off the value of the option
group, or write SQL on the "On Change" event of the option group that
changes depending on the option group selected and then set the
Form.RecordSource property to the SQL.

I hope that made sense, the more I read it the less I understood it!
;o)

 

[Guest]

I would do it a little differently:

Dim strFilter As String
Select Case Me.optBusiness_Filter
Case Is 1
strFilter = ""
Case Is 2
strFilter = "BU_Accepted_Date Is not Null and BU_Date_Sold is
null"
Case Is 3
strFilter = "BU_Accepted_Date is not null and BU_Date_Sold Is
Not Null"
End Select

If Len(strFilter) = 0 Then
Me.FilterOn = False
Me.Requery
Else
Me.Filter = strFilter
Me.FilterOn = True
End If

If Me.Recordset.RecordCount = 0 Then
MsgBox "There are no records."
Me.FilterOn = False
Me.Requery
Me.optBusiness_Filter = 1
End If

Me.txtBU_Name.SetFocus

Note: The form requery is only required after you set the Filter Off. When
you issue Me.FilterOn = True, a Requery occurs.
It is unnecessary to requery a control on a form that has just been requiried.

Notice I have used different commands. Some of the DoCmd methods sometimes
have trouble knowing what object they should be addressing. The above code
is less ambiguous.