Subform modifies main form's recordsource - Crash!

[hufflehuffle]

Hi!

I am still working on the problem to use filter-by-form in a subform.

To do this, I have been advised to set the recordsource to an INNER
JOIN of the main form's records and the sub form's records.

Now I use the "ApplyFilter" event in the subform to put the
WHERE-clause in the .filter property to modify the main form's
recordsource (so all the records in the main form, where no subform
records are present, are omitted).

Now I have two problems:

- Sometimes the ApplyFilter event in the subform is simpy not fired!
This does not seem to be reproducable. If I set a breakpoint in the
event, it suddenly works again. (A2K)

- After changing the main form's record source, when returning to the
design view of the form, Access regularly crashes with "Access
encountered a problem when changing views. Access is closing."

Does anybody have an idea on this? Should I unload the form and have
another form reload it with the modified record source? Can I hide any
records with no suform records another way?

I am at a loss. This should be a functionality Access should be able to
provide natively.

Best regards,
Andreas

 

[Rob Oldfield]

I think you'll need to supply some detail on exactly what your code looks
like.

 

[MacDermott]

You mentioned you're using A2K -
do you have all the service packs on it?
They can be critical for developers!

 

[hufflehuffle]

I think you'll need to supply some detail on exactly what your code looks
like.

Okay, here we go. The recordsource for the main form is a query. I go
through the querydefs and modify the sql statement accordingly. Then in
the AppyFilter statement of the main form (which always gets fired) I
set the recordsource to the query.

I had a Me.Parent.Requery in the procedure after the last line, to no
avail. I tried setting the recordsource to the SQL statement directly
(opposed to the QueryDef solution) - same problem.

I'll look for the ServicePacks, though.

Thanks!
Andreas
------clip

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)

Dim qd As QueryDef
Dim qdIndex As Integer
qdIndex = 0
For Each qd In CurrentDb.QueryDefs
If qd.Name = "QRY_TestergebnisseJOINSamplesFilter" Then
Exit For
End If
qdIndex = qdIndex + 1
Next

If (ApplyType = 1) And (Me.Parent.RecordSource <>
"QRY_TestergebnisseJOINSamplesFilter") Then
qd.SQL = "SELECT DISTINCT TblTesteingabe.* FROM TblTesteingabe
INNER JOIN TblSample ON
[TblTesteingabe].[ID]=[TblSample].[TestErgebnisseID] WHERE (" &
Me.Filter & ");"
End If
End Sub

 

[Rob Oldfield]

I think you'll need to supply some detail on exactly what your code looks
like.

Okay, here we go. The recordsource for the main form is a query. I go
through the querydefs and modify the sql statement accordingly. Then in
the AppyFilter statement of the main form (which always gets fired) I
set the recordsource to the query.

I had a Me.Parent.Requery in the procedure after the last line, to no
avail. I tried setting the recordsource to the SQL statement directly
(opposed to the QueryDef solution) - same problem.

I'll look for the ServicePacks, though.

Thanks!
Andreas
------clip

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)

Dim qd As QueryDef
Dim qdIndex As Integer
qdIndex = 0
For Each qd In CurrentDb.QueryDefs
If qd.Name = "QRY_TestergebnisseJOINSamplesFilter" Then
Exit For
End If
qdIndex = qdIndex + 1
Next

If (ApplyType = 1) And (Me.Parent.RecordSource <>
"QRY_TestergebnisseJOINSamplesFilter") Then
qd.SQL = "SELECT DISTINCT TblTesteingabe.* FROM TblTesteingabe
INNER JOIN TblSample ON
[TblTesteingabe].[ID]=[TblSample].[TestErgebnisseID] WHERE (" &
Me.Filter & ");"
End If
End Sub

Not sure what's causing the issue as yet, but a couple of points:

Why loop through the querydefs to grab the one you want? Why not just
use...

set qd=currentdb.querydefs("QRY_TestergebnisseJOINSamplesFilter")

And why the criterion in the If that compares the parent's recordsource to
QRY_TestergebnisseJOINSamplesFilter? Isn't it permanently set as that?

Anyway... some more questions:

How are you actually applying the filter? Through Access's built-in
buttons?
Is it failing just for specific values, or does it sometimes work and
sometimes not for the same filter? (assuming no changes in the underlying
data)
Is the error you're quoting the exact text?
What is the record source of the subform? It's just linked via a standard
parent-child relationship?

 

[hufflehuffle]

Rob,

first of all thank you for taking your time to help me. I appreciate
that.

Why loop through the querydefs to grab the one you want? Why not just
use...

set qd=currentdb.querydefs("QRY_TestergebnisseJOINSamplesFilter")

This is a simple one - I didn' know it's possible Found the code on
the net and used it unquestionedly. I will change that, of course.

And why the criterion in the If that compares the parent's recordsource to
QRY_TestergebnisseJOINSamplesFilter? Isn't it permanently set as that?

No. If the filter is removed (ApplyType = 0) in the ApplyFilter event
of the main form I set the RecordSource back to the unfiltered table.
Thought it would cause problems to modify an active recordsource.

Anyway... some more questions:

How are you actually applying the filter? Through Access's built-in
buttons?

Yes. I built my own commandbar (because I hide all the Access buttons),
but only with native Access buttons.

Is it failing just for specific values, or does it sometimes work and
sometimes not for the same filter? (assuming no changes in the underlying
data)

No, it is failing reproducably with the same filter settings and the
same data.

Is the error you're quoting the exact text?

No, it isn't, as I am using the German version of Access.

What is the record source of the subform? It's just linked via a standard
parent-child relationship?

The record source is the unfiltered table. When switching to the
filtered query, I don't modify the recordsource of the subform as
well... May this be a problem?

Best regards,
Andreas

 

[Rob Oldfield]

So, if it's failing consistently for a specific filter, then is it working
consistently for another filter? (...if so, then I'd say that it must be
some other feature of that filter that's causing the crash - another
query/recordset having zero records of similar....)

And a minor point - rather than using ApplyType=0 or 1, you're better off
using the constants acApplyFilter and acShowAllRecords. It doesn't really
matter, but ApplyType=acApplyFilter is easier to read.