Change toolbar programmatically

[Don Barton]

I have a custom toolbar with an assortment of command icons; Save,
Find, Form Search, Seach by Selection, Sort, Form and Datasheet view -
to list a few. The most used are Form and Datasheet view. I want
Datasheet view icon to be available only when a filter has been
applied to the form.
The reason is when clients look up records, they are naturally
inclined to use datasheet view. When you have 25 people scrolling
though 1500 records at the same time, I believe, it causes the
database to become corrupted. This corruption happens about once a
week - way too much!
Is there a way to turn off the icon on my custom toolbar unless a
filter has been applied? (Hence a much smaller subset of records to
sort through).

Thanks

Don

PS. I also listed this in Access.toolbars group, but it showed low
activity, so I thought I might get an answer a little sooner here.

 

[Marshall Barton]

I have a custom toolbar with an assortment of command icons; Save,
Find, Form Search, Seach by Selection, Sort, Form and Datasheet view -
to list a few. The most used are Form and Datasheet view. I want
Datasheet view icon to be available only when a filter has been
applied to the form.
The reason is when clients look up records, they are naturally
inclined to use datasheet view. When you have 25 people scrolling
though 1500 records at the same time, I believe, it causes the
database to become corrupted. This corruption happens about once a
week - way too much!
Is there a way to turn off the icon on my custom toolbar unless a
filter has been applied? (Hence a much smaller subset of records to
sort through).

You can use the toolbar button's Enabled property to disable
the button:

With Application.CommandBars("toolbar")
.Controls("datasheet view").Enabled = False
End With

I don't permit this kind of filtering in my apps, but, in
newer versions of Access, you can use the Filter and/or
ApplyFilter events to manage/detect when the form's
filtering is changed.

You can also use the form's Dirty event to enable/disable
your save button to prevent users from excessive saves. It
may be unlikely, but this could be a source of the
corruption you are seeing.

The corruption issue is very worrisome. Either you have a
flakey network, a bad NIC, or you are doing something in
your code that is messing up your database.

One serious mistake a programmer can make is to set a bound
control's value in the form's Current event. If that's the
case, simple record navigation will dirty records and force
unecessary saves. If there are any memo fields in the
records, the chance of corruption dramatically increases as
multiple users can be trying to save the same record.

 

[Don Barton]

You can use thetoolbarbutton's Enabled property to disable
the button:

With Application.CommandBars("toolbar")
.Controls("datasheet view").Enabled = False
End With

I don't permit this kind of filtering in my apps, but, in
newer versions of Access, you can use the Filter and/or
ApplyFilter events to manage/detect when the form's
filtering is changed.

You can also use the form's Dirty event to enable/disable
your save button to prevent users from excessive saves. It
may be unlikely, but this could be a source of the
corruption you are seeing.

The corruption issue is very worrisome. Either you have a
flakey network, a bad NIC, or you are doing something in
your code that is messing up your database.

One serious mistake a programmer can make is to set a bound
control's value in the form's Current event. If that's the
case, simple record navigation will dirty records and force
unecessary saves. If there are any memo fields in the
records, the chance of corruption dramatically increases as
multiple users can be trying to save the same record.

Hi Marsh,
First, thanks for your suggestions as to turning off/on the datasheet
button, and regarding corruption. This is a serious issue, and the
time will come when I may lose the client. What's interesting is this
database started out as a single database (fe/be together) on a
network, with only a shortcut icon on the desktop with @ 25 users. We
used it 5 years like this, and had far fewer problems. It was only
since I cleaned up the code, split the database FE - BE, that I've had
more problems.
Regarding the corruption issues you mention:
There are memo fields used, as they are necessary, but I can delete at
least one and get away with a text field.
Re: Bound controls in the On Current event. I have 3 that are
referenced. Essentially, If controlA=Yes, then make visible Control B
and Control C. I don't thing this would cause corruption, would it?
I recently had a huge issue with part of the memo field disappearing
when using ">" forced caps in the field's Format property.
This might have been part of the cause for corruption as well. It has
been removed.

Also, when you say:

I don't permit this kind of filtering in my apps-

Are you talking about datasheet view? Or something else? I meet with
the users next week and want to review usage policy. I want them to
get away from scrolling for names, and instead use the Filter by Form
with expected use of the wildcard *, then my intention is to use the
datasheet with a small subset to narrow down the record(s).
Unfortunately, we often get multiple records entered for a single
event (drawing blood on patients with multiple docs ordering tests).

I have one other question, that I will ask directly,

Thanks

Don
PS, nice last name, are you British?

 

[Marshall Barton]

First, thanks for your suggestions as to turning off/on the datasheet
button, and regarding corruption. This is a serious issue, and the
time will come when I may lose the client. What's interesting is this
database started out as a single database (fe/be together) on a
network, with only a shortcut icon on the desktop with @ 25 users. We
used it 5 years like this, and had far fewer problems. It was only
since I cleaned up the code, split the database FE - BE, that I've had
more problems.

You had multiple people using a single copy of a combined
mdb??? I have never even tried that sort of arrangement,
because I think that's a great way to corrupt a file.

Regarding the corruption issues you mention:
There are memo fields used, as they are necessary, but I can delete at
least one and get away with a text field.

Memo fields are more sensitive than number a text fields,
but the common corruption occurs when two users edit the
same record at the same time. With optimistic locking, one
of them will get a warning about overwriting the other's
changes and, depending on your version of Access/Jet, there
is a more or less significant chance of the memo field
becoming corrupted. (I haven't checked this recently, but
the problem was easy to reproduce in A97)

Re: Bound controls in the On Current event. I have 3 that are
referenced. Essentially, If controlA=Yes, then make visible Control B
and Control C. I don't thing this would cause corruption, would it?

No, that is not a problem. What I was warning about is
setting the **value** of a bound control or a record source
field (dirtying the record and forcing a save),

I recently had a huge issue with part of the memo field disappearing
when using ">" forced caps in the field's Format property.
This might have been part of the cause for corruption as well. It has
been removed.

No, that won't cause corruption. Users might then edit the
truncated field and lose part of the field, but that isn't
corruption. Using the Format property on a text box bound
to a memo field is well known to limit the displayed
characters to 255. So will many query operations such as
DISTINCT, GROUP BY, ORDER BY and UNION. None of these have
been reported to cause corruption.

Also, when you say:
Are you talking about datasheet view? Or something else?

I remove a menus and tool bars so those capabilities are
unavailable, Instead I create a purpose built search form
to filter/search records by rewriting the a form's record
source query or by using the Open Form/Report method's
WhereCondition argument. I tried using the filter property
when it first came out in A95, but ran into so many problems
that I almost never use it anymore.

I meet with
the users next week and want to review usage policy. I want them to
get away from scrolling for names, and instead use the Filter by Form
with expected use of the wildcard *, then my intention is to use the
datasheet with a small subset to narrow down the record(s).
Unfortunately, we often get multiple records entered for a single
event (drawing blood on patients with multiple docs ordering tests).

I strongly recommend filtering the records before loading a
form's data. That's why I use a search form to build the
filter and then open the form (i.e. almost never load all
records for any form).

I have one other question, that I will ask directly,

PS, nice last name, are you British?

Not British, not since the Declaration of Independence
anyway. I have a distant ancestor that has a castle in
Scotland though ;-)

I did live in Hampton-in-Arden (near Birmingham) for a
couple of years if that means anything ;-)