Filter by Form with sub form crash

[mikieb]

Hi

I'm having problems with filter by form causing access 2003 to shut down.

Looking at the Northwind database the same thing happens.

If a main form has "new additions" setting to no and has sub forms

filter by form is used and the data serched on the main fom returns null (
eg some one filters Michal insted of Michael, access crashes.

My own forms are linked to queries which results in not allowing "new
additions" so this has the same effect in access shutting down.

any adivce would be great

 

[Allen Browne]

Mike, I just tried this in Access 2003 SP3. It didn't crash, so I think
something else is going on here.

Firstly, I have an Invoice main form, with Invoice Details in the subform. I
set AllowAdditions to No in the main form, and then assigned a filter that
evaluates to False for all records, like this:
Private Sub Command6_Click()
If Me.Dirty Then Me.Dirty = False
Me.Filter = "(False)"
Me.FilterOn = True
End Sub
The result was that the entire main form goes blank as expected, but no
crash.

I suspect there is another bug being triggered in your case, and it may be a
bug related to the AccessField data type. This is the type Access uses for
fields in the record source of a form, that are not actually controls. You
can circumvent that bug by using the name of controls in the Link Master
Fields and Link Child Fields of your form.

Can I suggest this sequence:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Then compact the database:
Tools | Database Utilities | Compact/Repair
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Open the main form in design view.
Right-click the edge of the subform control, and choose Properties.
Clear the Link Main Fields and Link Child Fields properties.
Save. Close the main form.

3. Open the subform in design view.
Add a text box for the foreign key field (i.e. whatever field was nmaed in
Link Child Fields.) Set its Visible property to No if you wish. Save. Close.

4. Open the main form in design view, and enter the text box names in the
Link Main Fields and Link Child Fields again. Save. Close.

4. 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"

5. Open Access (holding down the Shift key if you have any startup code),
and compact again. Twice.

6. Open the code window, and choose Compile from the Debug menu. Fix any
bugs, and repeat until it compiles okay.

 

[mikieb]

Hi Allen

Many thanks for your advice, I will look into this, I'm afraid I'm trying to
acheive the results I need using macro's as its along time ago that I did vb
at college.

Is it possible to not run a filter if the reurn is null using a macro?

I presume this code achieves this someting similar

Me.Dirty Then Me.Dirty = False
Me.Filter = "(False)"
Me.FilterOn = True

It may be of interest to you that I mirrored possibly? the same problem
using the Northwind database, Customer orders form, using filter by form,
querying Company name "xzzx" it returns the message "Acces has stopped
working" and closes.

The second part of your reply I work through

again, many thanks for your time

Mike

 

[mikieb]

Hi Allen

you gave me afew ideas and I did try

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
If Me.Filter = "(false)" Then Me.FilterOn = False Else Me.FilterOn = True
End Sub

the query the form is based has numerical and text fields this produced
variable results on filtering, some times it worked other times it crashed
(possibly worse when queried on text fields"), I suspect there is, as you say
another underlying problem, unless my code is nonsense?

will work through the rest of your reply in the next few days

again thanks Mike

 

[Allen Browne]

Replies in-line.

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

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

Many thanks for your advice, I will look into this, I'm afraid I'm trying
to
acheive the results I need using macro's as its along time ago that I did
vb
at college.

Is it possible to not run a filter if the reurn is null using a macro?

It is possible with a macro (but probably easier in code.) After your
ApplyFilter (or whatever) action, use the Condition Column to test whether
the form has zero records, e.g.:
[Forms].[Form1].[RecordsetClone].[RecordCount] = 0
and take whatever action you need, such as removing the filter.

Attempting to do this in a macro in Access 2007 may generate a "Sandbox
mode" error.

Of course, this won't prevent the crash, because it still goes through the
case where there are no records.

It may be possible to use DLookup() with the same criteria as the proposed
filter to see if there are any matches, and apply the filter only if there
is at least one matching record. For help with DLookup(), see:
http://allenbrowne.com/casu-07.html

I presume this code achieves this someting similar

Me.Dirty Then Me.Dirty = False
Me.Filter = "(False)"
Me.FilterOn = True

That's just an example. The first line saves any changes: since Access has
to do this before it can apply the filter, I find it helps to be explicit
about it. (It has the effect of processing and clearning a bunch of pending
messages in the queue before attempting to do the next thing.)

A filter string is essentially a WHERE condition from a query. In the end,
it is somehing that evaluates to True (include the record) or False (exclude
the record), for each record. The expression "(False)" doesn't depend on the
data in the record, so it evaluates to False for all records, hence all
records are excluded. (Actually, the WHERE condition could evaluate to Null
too, but that excludes the record.)

The third line just turns the filter on.

It may be of interest to you that I mirrored possibly? the same problem
using the Northwind database, Customer orders form, using filter by form,
querying Company name "xzzx" it returns the message "Acces has stopped
working" and closes.

I doubt that Northwind has a control in the subform for the foreign key, so
you may be able to crash Northwind using the same approach. The AccessField
problem doesn't always occur: I don't have the conditions nailed down
completely, but it tends to be when using a multi-table query as the source
for the subform.

The second part of your reply I work through

If you are still stuck after you work through this, please indicate what
version of Access you are using, and what service pack. (It's on the Help |
About screen.) Also, which Windows you use.

 

[mikieb]

Hi Allen

I have been working through this for a few hours and have tried not using
"name autocorrect" which is now unchecked. Did not hold much hope that my
simple code attempts would solve this, as the crash seems to be variable.

In the end working at the solution from the ground up seems to have done the
trick.
I started by creating a new simpilfied main form with a basic sub form
(Access 2003 using vista)
tried filter by form and data that would return zero result and it worked.

next I tried importing my sub forms and it still worked fine and even added
a bit more complication by linking a query to the main form and still no
crash, well its late and I'm sure it will probably crash in the moring but I
seem to have a solution.

I did try trouble shootiing the problem in this way the other day, but kept
my main form when rebuilding.

it would take a day or so to sort out the forms which are causing problems
so will work through it.

This database has slowly evolved over some years, I think possibly through 3
versions of Access and several verions of windows, perhaps like me it needs a
rethink.

your advice has been very helpful, will look further in to improving my use
of vb.

--
thanks Mike

Replies in-line.

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

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

Many thanks for your advice, I will look into this, I'm afraid I'm trying
to
acheive the results I need using macro's as its along time ago that I did
vb
at college.

Is it possible to not run a filter if the reurn is null using a macro?

It is possible with a macro (but probably easier in code.) After your
ApplyFilter (or whatever) action, use the Condition Column to test whether
the form has zero records, e.g.:
[Forms].[Form1].[RecordsetClone].[RecordCount] = 0
and take whatever action you need, such as removing the filter.

Attempting to do this in a macro in Access 2007 may generate a "Sandbox
mode" error.

Of course, this won't prevent the crash, because it still goes through the
case where there are no records.

It may be possible to use DLookup() with the same criteria as the proposed
filter to see if there are any matches, and apply the filter only if there
is at least one matching record. For help with DLookup(), see:
http://allenbrowne.com/casu-07.html

I presume this code achieves this someting similar

Me.Dirty Then Me.Dirty = False
Me.Filter = "(False)"
Me.FilterOn = True

That's just an example. The first line saves any changes: since Access has
to do this before it can apply the filter, I find it helps to be explicit
about it. (It has the effect of processing and clearning a bunch of pending
messages in the queue before attempting to do the next thing.)

A filter string is essentially a WHERE condition from a query. In the end,
it is somehing that evaluates to True (include the record) or False (exclude
the record), for each record. The expression "(False)" doesn't depend on the
data in the record, so it evaluates to False for all records, hence all
records are excluded. (Actually, the WHERE condition could evaluate to Null
too, but that excludes the record.)

The third line just turns the filter on.

It may be of interest to you that I mirrored possibly? the same problem
using the Northwind database, Customer orders form, using filter by form,
querying Company name "xzzx" it returns the message "Acces has stopped
working" and closes.

I doubt that Northwind has a control in the subform for the foreign key, so
you may be able to crash Northwind using the same approach. The AccessField
problem doesn't always occur: I don't have the conditions nailed down
completely, but it tends to be when using a multi-table query as the source
for the subform.

The second part of your reply I work through

If you are still stuck after you work through this, please indicate what
version of Access you are using, and what service pack. (It's on the Help |
About screen.) Also, which Windows you use.

 

[Allen Browne]

Good news.