Filtering a form causes a crash

[jonefer]

I have a combo box on a form that lists 3 types of callers

1. All
2. Anonymous
3. Specific Caller (LastName, FirstName MI)

I want the form to be able to filter by the various types above.

The following code seems like it should work, but causes Access to crash
(I'm using Access XP) --Any ideas why it crashes?

Select Case Me.CallerFilterBox

Case 0 'Anonymous'
Me.Filter = "[CallerID]= " & 0
Me.FilterOn = True

Case -1 'All Callers
msgbox "All"
Me.FilterOn = False

Case Else 'Specific Caller

Me.Filter = "[CallerID] = " & Me.CallerFilterBox
Me.FilterOn = True
End Select

 

[Allen Browne]

By "crash" do you mean it brings up an error message, or that "Access is
shut down by Windows; sorry for the inconvenience?"

If error message:
=============
1. Make sure the combo is unbound (nothing in its Control Source property).

2. Set the Format property of the combo to:
General Number
(I am assuming that CallerID = a Number type field.
Requires extra quotes if it is a Text type field)

3. Explicitly save the record before applying the filter.

4. Test the combo is not null.

You will end up with something like this:
Private Sub CallerFilterBox_AfterUpdate()
If Me.Dirty Then
Me.Dirty = False
End If

With Me.CallerFilterBox
If .Value = -1 Then
Me.FilterOn = False
ElseIf Not IsNull(.Value ) Then
Me.Filter = "[CallerID] = " & .Value
Me.FilterOn = True
End If
End With
End Sub

If a shut-down crash:
===============
There is probably a corruption in the database.
Post back for more info.

 

[Allen Browne]

Fair enough. Before (preferably) you import the stuff from the existing
database, decompile it. Close Access. Make a backup copy of the file.
Decompile the database by entering something like this at the command prompt
while Access is not running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"
Then compact:
Tools | Database Utilities | Compact

Then when you create the new database, before importing anything, uncheck
the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

And the explicit save is still recommended.

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

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

The code works flawlessly. Thanks.
However, I'm going to have to start a new database.
Something in it is corrupt. That's why it shuts down.

By "crash" do you mean it brings up an error message, or that "Access is
shut down by Windows; sorry for the inconvenience?"

If error message:
=============
1. Make sure the combo is unbound (nothing in its Control Source
property).

2. Set the Format property of the combo to:
General Number
(I am assuming that CallerID = a Number type field.
Requires extra quotes if it is a Text type field)

3. Explicitly save the record before applying the filter.

4. Test the combo is not null.

You will end up with something like this:
Private Sub CallerFilterBox_AfterUpdate()
If Me.Dirty Then
Me.Dirty = False
End If

With Me.CallerFilterBox
If .Value = -1 Then
Me.FilterOn = False
ElseIf Not IsNull(.Value ) Then
Me.Filter = "[CallerID] = " & .Value
Me.FilterOn = True
End If
End With
End Sub

If a shut-down crash:
===============
There is probably a corruption in the database.
Post back for more info.

I have a combo box on a form that lists 3 types of callers

1. All
2. Anonymous
3. Specific Caller (LastName, FirstName MI)

I want the form to be able to filter by the various types above.

The following code seems like it should work, but causes Access to
crash
(I'm using Access XP) --Any ideas why it crashes?

Select Case Me.CallerFilterBox

Case 0 'Anonymous'
Me.Filter = "[CallerID]= " & 0
Me.FilterOn = True

Case -1 'All Callers
msgbox "All"
Me.FilterOn = False

Case Else 'Specific Caller

Me.Filter = "[CallerID] = " & Me.CallerFilterBox
Me.FilterOn = True
End Select