Filter Buttons

[Guest]

I have a form that lists data from a table in Continuous Forms view. On the
form are two buttons that apply the "Filter On" and "Filter Off" functions to
the data field the user has the cursor placed over. Recently I have been
encountering a problem where the user, when they click on the "Filter Off"
button, Microsoft Access encounters an "error" and closes. (then it asks the
user to save, compact and repair, etc) The only remarkable thing that strikes
me is that I have admin priveleges and the users have a "Team Member"
privelege profile with limited access to the database. Is there some
setting/change that could be causing this to happen? Is the code I have
running on the "Click" event poorly executed? Below is the code I am using
for each button: TIA

For Filter On Button:
Private Sub Command25_Click()
Screen.PreviousControl.SetFocus
DoCmd.RunCommand acCmdFilterBySelection
End Sub

For Filter Off Button:
Private Sub Command26_Click()
DoCmd.RunCommand acCmdRemoveFilterSort
End Sub

 

[Allen Browne]

The basic idea is okay, Antonio.

Before Access can apply or remove a filter, it must save any edits that are
currently in progress. We have found it worthwhile explicitly saving before
trying to apply any filter, so you might add this line first:
If Me.Dirty Then RunCommand acCmdSaveRecord

You could also improve the code by adding error handling. This would handle
the cases where the record cannot be saved, or there is no previous control.

As an alternative for your Filter Off button, you could try setting the
form's FilterOn property to No:
Private Sub Command26_Click()
If Me.FilterOn Then
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
Me.FilterOn = False
Else
Beep
End If
End Sub

However, I suspect that the crashes are due to another issue, such as:
- Multiple users all in the same mdb at once, when it needs to be split:
http://allenbrowne.com/ser-01.html

- Multiple versions causing a problem that would be solved by a decompile.

- A problem with subforms in Access 2002 or 2003 where the subform does not
have a text box for the field named in LinkChildFields.

Post back if you need more info on those issues.

 

[Guest]

Mr Browne, thank you so much for the response.

The db is split into a front and back end (did this some time ago after a
corruption).

I added "If Me.Dirty Then RunCommand acCmdSaveRecord" to both buttons, but
same behavior.

I tried to do the "decompile" and I had interesting results.
I added (based on two different suggestions from a Google search)

"C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE" /decompile
"C:\Kazimiera\K2.1.mdb"
AND
"C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE"
"C:\Kazimiera\K2.1.mdb" /decompile

to two different,new, short cuts I created and the first time it took my
5.89 mb db and shrunk it down to 4.2mb. When I did the compile, save, and
compact and repair it went back up to 5.13mb.
The second time I tried it it did the same thing, resting at 5.12mb....but
with no change.

Form is by itself, the buttons are in the form header with no subforms
attached to it.

I am not sure about the error handling you mentioned earlier, but the thing
that strikes me is only users without admin priveleges get the error message
and booted. I tried changing the permissions for that paticular forms table,
query, form and sub form to that of an admin user, but no change. However,
when I took a regular user and added them to the admin group, they no longer
received the crash. The buttons themselves have been around and in use for
some time so I dont really know how to explain this.
All the same, I do appreciate your help.

 

[Allen Browne]

Okay, so your debugging suggests that the issue is related to priviliges in
JET (i.e. Access security).

If that is so, it might be worth checking the version of msjet40.dll on the
users' machines. The file is typically in windows\system32, and when you
right-click and check the Version tab in Properties you should see
4.0.8xxx.0. The xxx digits don't matter, but if you don't see the 8, they
need to apply SP8 for JET 4 from:
http://support.microsoft.com/gp/sp

Other than that, I don't know what to suggest. Perhaps someone else has a
suggestion to post?

Presumably you have also turned off the typical culprits, such as Name
AutoCorrect (Tools | Options | General), and the SubdatasheetName property
in these tables.

 

[Guest]

Mr. Browne,
The auto correct is off. I just learned about the subdatasheetname and set
it from "Auto" to "None"...but, still encountering the problem.
I do appreciate all the help. I learned alot just from the posting. Which
makes me ask, Did I do the whole decompile thing correct?
Insert the code into the short cut.
Open the short cut.
Compile the code.
Then compact and repair?

 

[Allen Browne]

The shortcut probably worked. I prefer to use the Command prompt.

Reading back through the thread again:
- What version of Access is this?
- Does the form have a subform?
There is another issue that crashes A2002 and 2003 that relates to subforms.

Alternatively, are you using subqueries anywhere (in the Filter statement,
or in the RecordSource of the forms)? These sometimes crash Access (all
recent versions.)

 

[Guest]

How does one use a command prompt to run a decompile?
(does this involve executing the decompile command from a text file?)

The computer I am building on is Access 2002. However, there are one or two
computers with Office/Access 2000 that access the database. Most others are
Windows XP with Access 2002 (although I foudn one XP machine with
Office/Access 200 loaded on it) . The front end is distributed among 15 or so
computers with the back end saved on our server. I believe the file format
for this particular database is Access 2000. (when I click on Tools, Database
Utilities, Convert Database, I am given the option of converting to Access
97 and Access 2002. The option to convert to Access 2000 is ghosted)

There is a main form with a tab control. There are two different "tabs" with
one form on each of them (does this make them a subform?) and then, thats
it...no subforms attached to the individual form on each tab. The forms are
in "Continuous Forms" format with the filter buttons located in the form
header.

Each of the forms located in the tabs are based on a single query with the
data table being the only source for the query.

 

[Allen Browne]

Decompile must be run while your Access database is not open.

The Command prompt (DOS prompt in the old days) depends on your version of
Windows. Typically it's under:
Start | Programs | Accessories

You can store the entire text for the decompile in a text file if you wish.
Or you can drag msaccess.exe from the Windows Explorer into the command
prompt window, type /Decompile, and then drag your mdb into the window.

That may not be the easiest solution, but hey--I'm an old DOS guy!
And I like to see the error that gets returned from this kind of process if
something doesn't work as expected.

 

[Guest]

Before trying this,
In a front end back end environment, does the database have to be completely
vacant before I can do this? (Do I have to ask everyone to log off?) or does
this decompile only affect the front end that is on my computer?
(Funny you mention being an old DOS guy, I was in Alice Springs for a while
and was told the very same thing...though not being familiar with a non
windows environment I didn't quite understand what that meant at the time)

 

[Allen Browne]

In a split database, you need to decompile the front end (since that is
where the code is), and compact/repair the back end (since that's where the
data is.)