How to get rid of sort/filter arrow in column header on Datasheets in MS2007?

[ncsthbell]

Working on converting some old MS2000 access applications to MS2007. I have many datasheets in the application and I have noticed that on all of them the column headers have a down arrow with sort/filter options. This arrow is covering up some of the letters on the column descriptions. I have built-in listboxes on my forms that allow the users to select the criteria they need so I do not need the drop down sort/filters on the columns. My users will be using 'Runtime Access' to run the application. I was told that it does not show the drop down arrow on the column header, but when I ran the access app in Runtime, the arrows are present. Is there some way to turn these off?
Thanks!!

[puppydogbuddy]

You can do it if you don't need the shortcut menu feature. Here is how thanks to Jeff Conrad aka the Access Junkie:

If you don't want to display these arrows, here's a quick workaround to disable them. Open the datasheet form in Design View and then open the Property Sheet for the form. Look for the property called Shortcut Menu on the Other or All tab of the Property Sheet. Change that property to No, save the form, and now switch back to Datasheet View. Access now removes the arrows from the top of the column headers.

[ncsthbell]

Thanks... this is exactly what I needed!

[ncsthbell]

I have lots of forms in my application if I have to do this for each form, would be time consuming. I see on the 'startup' options for the application there is a setting "allow default shortcut menus", I removed the checkbox from this option and the down arrows still how on the forms. I thought that this option handle it at an application level so I would not have to do each form.

[puppydogbuddy]

Unfortunately, you have to turn "ALL" shortcut menus off, and "Default" is close, but not quite the same as "ALL".

You should be able to get around having to manually open and change each form by using a vba code routine to loop the open forms, and change the property setting for the shortcut menus. In doing so, you need to keep the following in mind:

The forms collection only contains <<<open>>> forms, so you will need to code a routine that works around that limitation and opens each form in design view, changes the property setting, and then closes that form and moves to the next form. It is my understanding that in order to "persist" the changes to the form property settings, the change has to be made in design view.

[puppydogbuddy]

In Access 2000 or newer, you can use something like this:

Dim oForm As AccessObject
Dim oDatabase As Object
Dim strFrm As String
 
Set oDatabase = Application.CurrentProject
For Each oForm In oDatabase.AllForms
            strFrm = oForm.Name
            DoCmd.OpenForm strFrm, acDesign
            XXXX Code to change Shortcut menu property goes here
            DoCmd.Close acForm, strFrm, acSaveYes
Next oForm
 
oDatabase.Close
Set oDatabase = Nothing

[SamiFromFrance]

Thanks a lot to puppydogbuddy : I add that you can also keep shortcut menu by changing the property of the form on its Open event. Basically, you have to :
- turn off Shortcutmenu in Property menu of the form
- add the code below :

Private Sub Form_Open(Cancel As Integer)
    Me.ShortcutMenu = True
End Sub

Then the form in datasheetview mode will open without builtin sort/filter in headers and still keep its shortcut menus : magic, isn't it ?!!!

To my mind, this workaround is very important because builtin sort/filter functionnality is not reliable at all with numerous datas (>20000 records) : it is very slow (and doesn't show hourglass !) and it bugs with complex filters ('OR' condition for example).

[bamage]

SAMIFROMFRANCE your input was actually exactly what I needed, as I wanted to still have filter options available on the right-click menu but didn't want the user to see the column pulldown options.

@SamiFromFrance