access crashing after filter

  • Thread starter Thread starter riyaz.mansoor
  • Start date Start date
R

riyaz.mansoor

MS Access 2003 SP2

The following code runs on the AfterUpdate event on control cmbFilter

If cmbFilter.Value = "None" Then
fil = ""
ElseIf cmbFilter.Value = "Current" Then
fil = "B_Completed=0 AND B_Waived=0 AND B_Loss=0"
ElseIf cmbFilter.Value = "Completed" Then
fil = "B_Completed=1"
ElseIf cmbFilter.Value = "Waived" Then
fil = "B_Waived=1"
ElseIf cmbFilter.Value = "Loss" Then
fil = "B_Loss=1"
Else
Err.Raise 1024, "error"
End If

Set tRst = Me.Recordset
tRst.Filter = fil

The form is backed by a code generated Recordset. When 'Current' is
selected, MS Access crashes asking to sen d the error report to MS. On
debugging, I found that this method completes and then the FormCurrent
method also completes without error. But right after FormCurrent
completes, MS Access crashes.

I also suspect that the crash does not happend if a filter already
exists when 'Current' is called.
1) 'Completed' -> 'Current' ===> NO crash
2) 'None' -> 'Current' ===> CRASH

All the filtering fields are booleans (BIT on MS SQL Server to be
exact). What could i do?
Please help

Riyaz
 
Dim fil As String

If cmbFilter.Value = "None" Then
fil = ""
ElseIf cmbFilter.Value = "Current" Then
fil = "B_Completed=0 AND B_Waived=0 AND B_Loss=0"
ElseIf cmbFilter.Value = "Completed" Then
fil = "B_Completed=1"
ElseIf cmbFilter.Value = "Waived" Then
fil = "B_Waived=1"
ElseIf cmbFilter.Value = "Loss" Then
fil = "B_Loss=1"
Else
Err.Raise 1024, "error"
End If

Set tRst = Me.Recordset
tRst.Filter = fil

If tRst.RecordCount = 0 Then
cmbFilter.Value = "None"
tRst.Filter = ""
tStr = "No records to list. Reverting back to NO filter"
MsgBox tStr, vbInformation, TITLE
End If

Set Me.Recordset = tRst 'Crash here after Form_Current completes
successfully
Debug.Print tRst.RecordCount


Correction on my previous post. cmbFilter.AfterUpdate does NOT
complete. On the line highlighted above, the Form_Current method
executes successfully but upon returning from Form_Current, it crashes
on the same line. It does NOT reach the DEBUG statement.

Please help.

Riyaz
 
It is recccomended; that if Access crashes for you- that you keep your data
elsewhere
 
Susie said:
It is recccomended; that if Access crashes for you- that you keep your data
elsewhere
Click to expand...

It is on MS SQL Server as said in the original post.

Any help on the problem itself?

I have also found that if I change the SQL WHERE clause to have only
OR instead of AND, then it does not crash. What is going on?

Riyaz
 
For one thing, -1 is True and 0 is False, assuming that B_Completed,
B_Waived, and B_Loss are Yes/No fields.

Perhaps you have left out some code, but you don't seem to have declared
tRst.

One line of code is: Set tRst = Me.Recordset
Later, there is: Set Me.Recordset = tRst
In between, nothing affects either Me.Recordset or tRst. You define tRst as
Me.Recordset, then you define Me.Recordset as tRst, but to what end? The
filter and the recordset are two different things. Nothing in your posted
code affects the recordset. If you do have occasion to define Me.Recordset,
you can just use:
Me.Recordset = strRS (where strRS is a string that you use as a SELECT
statement). What is the intention of the Recordset code?
It may be helpful to set the code to break on all errors. If the code does
not reach the debug line, try adding debug lines elsewhere. For one thing,
make sure that tRst is what you expect (assuming you need it at all).
When I apply a filter I use:
Me.Filter = fil
Me.FilterOn = True

I am not quite familiar with the approach you have taken, but I expect that
you specifically need to apply the filter, not just define it.

On another note, Value is the default property for a combo box, so there is
no need to specify:
If cmbFilter = "None"
There is no harm to specifying Value, but you may as well simplify where you
can.
 
For one thing, -1 is True and 0 is False, assuming that B_Completed,
B_Waived, and B_Loss are Yes/No fields.
Click to expand...

I have tried with "=0" said:
Perhaps you have left out some code, but you don't seem to have declared
tRst.
Click to expand...

Declared at the form module level
One line of code is: Set tRst = Me.Recordset
Later, there is: Set Me.Recordset = tRst
In between, nothing affects either Me.Recordset or tRst. You define tRst as
Me.Recordset, then you define Me.Recordset as tRst, but to what end? The
filter and the recordset are two different things. Nothing in your posted
code affects the recordset. If you do have occasion to define Me.Recordset,
you can just use:
Click to expand...

With regrads to above and below comments - I think you've
misunderstood. I'm using ADO recordsets generated from MS SQL Server
instance. MS Access cannot filter such Recordsets in the normal way -
ie using the form properties .Filter and .FilterOn

To filter such a Recordset - the recordset itself has to be filtered
and then re- set to the forms recordset. Just applying the filter on
the ADO recordset will not do. Hence:
Set tRst= Me.Recordset
tRst.Filter = "my filter" ' not enough to filter the form
Set Me.Recordset = tRst ' set the filtered recordset for the form

However, IF there is another way to do this, i'm very interested to
know :)

Me.Recordset = strRS (where strRS is a string that you use as a SELECT
statement). What is the intention of the Recordset code?
It may be helpful to set the code to break on all errors. If the code does
not reach the debug line, try adding debug lines elsewhere. For one thing,
make sure that tRst is what you expect (assuming you need it at all).
When I apply a filter I use:
Me.Filter = fil
Me.FilterOn = True
I am not quite familiar with the approach you have taken, but I expect that
you specifically need to apply the filter, not just define it.

On another note, Value is the default property for a combo box, so there is
no need to specify:
If cmbFilter = "None"
There is no harm to specifying Value, but you may as well simplify where you
can.
Click to expand...

MS Access is giving me so much trouble!

Riyaz
 
It isn't so much a case of not understanding as not knowing you are using
SQL server and an ADO recordset. Maybe I should have known that from the
code, but I didn't. It seems you are saying that tRst.Filter = "my filter"
results in the recordset tRst being redefined as a filtered recordset, so
that in the line Set Me.Recordset = tRst, Me.Recordset is now the filtered
recordset without specifically applying the filter.
My comments about error handling still apply. If you use error handling in
the procedure (or maybe set the code to break on all errors) you should be
able to find out what error is causing the crash. If the code isn't
reaching your Debug.Print line, why not try putting a Debug.Print line
elsewhere, such as before the line that is causing the crash to see if tRst
is what you expect?
 
It isn't so much a case of not understanding as not knowing you are using
SQL server and an ADO recordset. Maybe I should have known that from the
code, but I didn't. It seems you are saying that tRst.Filter = "my filter"
results in the recordset tRst being redefined as a filtered recordset, so
that in the line Set Me.Recordset = tRst, Me.Recordset is now the filtered
recordset without specifically applying the filter.
My comments about error handling still apply. If you use error handling in
the procedure (or maybe set the code to break on all errors) you should be
able to find out what error is causing the crash. If the code isn't
reaching your Debug.Print line, why not try putting a Debug.Print line
elsewhere, such as before the line that is causing the crash to see if tRst
is what you expect?
Click to expand...

from my second post.successfully
Debug.Print tRst.RecordCount

Also of note. When the crash message is displayed the form has
CORRECTLY updated to reflect the filtered results. Hence, it appears
its a problem inherent in Access.

I have already given up on this approach with Access

Riyaz
 
Hi,
Set tRst = Me.Recordset
tRst.Filter = fil and this
Set Me.Recordset = tRst 'Crash here after Form_Current completes
successfully
Debug.Print tRst.RecordCount
Click to expand...

This part of the code is unusual for filtering records. My guess is, it will
never work at all.

"Set Me.Recordset = tRst" if this line crash, means that the recordset is not
opened.
As we cannot see the complete code, we can't tell whether you have any code
in that event.
VB editor did not prompt you for a undefined ("tRst") variable? Did you
include "Option Explicit"?

Your code for filtering a recordset is incorrect. Please refer to Help in
Access.
 
You have the Debug.Print line after the line that is causing the problem.
My suggestion was that a Debug.Print line *before* the problem line of code
would be of potential value. Debug.Print after the code has failed will
suggest nothing about the cause for the failure.
What happens if you comment out the line that is causing the crash? If the
recordset has been filtered properly, what do you hope to accomplish by that
line of code?
I very much doubt this is an inherent problem with Access. The application
crashes at a line of code and you seem to be wondering what is wrong with
the application. I would suggest that when posting such a question you
should include from the beginning relevant information such as that you are
using an ADO recordset and SQL server, the definition of variables (we can't
see that tRst has been declared at the form module level, or what it is),
and information such as the fact that the recordset is filtered properly
before the crash. If the code compiled without problems, that is worth
mentioning too.
 
I do appreciate the efforts, but I do wish that people would reply
after reading the posts.
This part of the code is unusual for filtering records. My guess is, it will
never work at all.
Click to expand...

It DOES work. In case u missed, tRst is a ADO recordset from a MS SQL
Server instance.
"Set Me.Recordset = tRst" if this line crash, means that the recordset is not
opened.
As we cannot see the complete code, we can't tell whether you have any code
in that event.
VB editor did not prompt you for a undefined ("tRst") variable? Did you
include "Option Explicit"?
Click to expand...

As said above, tRst is declared at the module level.
Your code for filtering a recordset is incorrect. Please refer to Help in
Access.
Click to expand...

No, it is correct. It works fine - except that Access crashes when I
try to filter on multiple boolean fields. Please read the first post.

Riyaz
 
You have the Debug.Print line after the line that is causing the problem.
My suggestion was that a Debug.Print line *before* the problem line of code
would be of potential value. Debug.Print after the code has failed will
suggest nothing about the cause for the failure.
Click to expand...

This is exactly where it crashes, no need of a debug statement. As I
had said before, I debugged to find this. Also all the other variables
(inc. tRst) are correct.
What happens if you comment out the line that is causing the crash? If the
recordset has been filtered properly, what do you hope to accomplish by that
line of code?
Click to expand...

Comment out the line - NO crash, form not filtered. With ADO
recordsets, even if the recordset is filtered the form which is backed
by that recordset will NOT reflect the filtered records unless it is
forced to do so - with such a statement as in question.
I very much doubt this is an inherent problem with Access. The application
crashes at a line of code and you seem to be wondering what is wrong with
the application. I would suggest that when posting such a question you
Click to expand...

I am convinced that it is a problem with Access. As my original post
said - that bit of code works fine in some situations. Also, I can see
the form has been correctly filtered (from behind the Crash message)
even whenAccess crashes there. Hence that bit of code is correct and
working, but maybe Access cannot handle some obsecure sequence of
events that happened before which shows its ugly head (unfortunately)
at that line of code.

:) thats my observation.

Riyaz
 
Hi,
(e-mail address removed) wrote:
I do appreciate the efforts, but I do wish that people would reply
after reading the posts.
Click to expand...

That's not the point and it's not true. That's your assumption.
It DOES work. In case u missed, tRst is a ADO recordset from a MS SQL
Server instance.
As said above, tRst is declared at the module level.
Click to expand...

Yes, you have said that, we know.......period.
It's wrong to assume even that you have "declared" some code in the form's
module event and at the same time you want to use the "declared" recordset in
another event...that's a No No. Period.
Access by default, close a recordset after the event is over, thus your
recordset is close.
No, it is correct. It works fine - except that Access crashes when I
try to filter on multiple boolean fields. Please read the first post.

Riyaz
Click to expand...

There is a lot of differance between Form Filtering and Recordset Filtering.
Check Help again.

Until you can give some actual code, we can only guess.
 
Maybe if you knew the value of tRst you could identify why Set Me.Recordset
= tRst fails. That was my point about the debug statement. When I assemble
strings for filters and such I check the string as I go to be sure it is
producing the expected results. If I trap the error I can also identify the
problem, but the point is that I need to use something to show me the actual
results if I am having trouble with the code.
 
Back
Top