Filter OPtions

[bdm1]

I created an option group to filter records. Option 1 to view all records and Option 2 to view just the active members. The active member field is a yes/no data type.

When I select the Active Member option I get a message that the previous operation was canceled. I'm missing something somewher but can't figure it out. Below is my code...Help!

Private Sub FilterOptions_AfterUpdate()
' Apply or remove the filter for the option the user chose.

If FilterOptions = 2 Then
Me.Filter = "Active = '-1'"
Me.FilterOn = True ' Apply the filter.
Else
Me.FilterOn = False ' Remove the filter.
End If

End Sub

 

[RickSpr]

I suspect that the operation that was canceled was an update to the record being displayed before you change the filter. If it was updated, the record normally wouldn't have been saved yet when the user clicks the radio button.

Before you test the option group, add the following code:

    If Me.Dirty Then RunCommand acCmdSaveRecord

Even better would be to prompt the user whether to save:

    If Me.Dirty Then
        Select Case MsgBox("There are unsaved changes to the current record. " _
            & "Do you want to save them?" & vbCrLf & vbCrLf _
            & "- Click Yes to save the record and continue." & vbCrLf _
            & "- Click No to discard the changes and continue." & vbcrlf _
            & "- Click Cancel to resume editing this record.", _
            vbQuestion Or vbYesNoCancel)
            Case vbYes
                RunCommand acCmdSaveRecord
            Case vbNo
                Me.Undo
            Case vbCancel
                Exit Sub
        End Select
    End If

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[bdm1]

I Rick, I do not think this is the issue causing the problem as I have tested the filter option before adding/editing any records. I will try your suggestion. Perhaps quirky access requires this script. Will let you know if successful...Thanks much

 

[RickSpr]

Access only seems "quirky" until you understand what it's doing. It really is quite predictable. Don't just try stuff until something works and you don't know why, or you'll never develop that understanding.

If you believe the "operation canceled" message occurs even when there are no pending changes, let's verify that. (Sometimes there can be pending changes as a side effect of something else, and you don't realize it.)

Try temporarily setting the form's Record Selector property to Yes, if it's currently No. The bar at the left of the form displays a triangle icon if there are no pending changes, or a pencil icon if there are. If the "operation canceled" message occurs even when the bar shows the triangle, then my guess was simply wrong. OTOH, if the pencil icon appears even when you haven't edited the record, then I'm right and there are record changes happening that you don't expect.

What is the exact message you're getting? Is it "Runtime Error 2001: You canceled the previous operation"? What version of Access are you using?

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[bdm1]

The triangular icon is the on displayed. I inserted the "Dirty" code and I am still getting the error message:

Run-time error 2001: you canceled the previous operation.

I've never encountered this before, but then again I have not attempted to filter on a yes/no data type before. I suspect that has something to do with it but for the life of me I can't figure where the conflict lies.....

 

[RickSpr]

No, filtering on the yes/no isn't a problem. In fact, I have a personal application in which I do this exact thing, except that my flag is 'yes' for inactive members instead of active.

What statement is the error occurring on?

By the way, is the FilterOptions group bound to any field? If it is, then by clicking the radio button you're editing the record and immediately applying the filter, which is a special case of what I suggested before.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[pfcak]

I was having the same problem (error 2001) when I was dynamically building a filter for a form and applying it. I had several text fields on the form which I used to build the criteria and noticed that I did not get the error when I used certain ones but did for others. Turns out that I was not using the right syntax for the data type that I was filter on. I had single quotes (') being put around the my date criteria instead of (#) [I'm used to T-SQL syntax and forgot about the Access syntax]. Once I corrected that, bye-bye error 2001.

So to make a long story short, in your code you use a Yes/No data type which is like a number, so try removing the single quotes around the '-1'.
Before: Me.Filter = "Active = '-1'"
After : Me.Filter = "Active = -1"