Right-Click form filtering in Access 2013

I have 2 applications.  One built from scratch with AC2010 and one legacy application  that actually consists of over 80 Access programs (some MDBs some ACCDBs) which were developed starting 15 years ago.

In the 2010 "from scratch" database, the built-in right-click sorting/filtering in forms works great.  In the legacy apps, sorting/filtering is only available in debug mode (Left-Shift) but is not available otherwise.

I've done hours and hours of research and trying various approaches, but to no avail.  Has anyone run into and solved similar problems.  The legacy apps are being run with Access 2013.  I've read what is on this forum (and MANY others).
-Bill-
bcreenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
Bill,

Are the legacy apps being run using the runtime version, or do the individual users all have their own copy of Access 2013?

Right click menus are not available without significant additional coding when using Access Runtime.

Dale
Nick67Commented:
In the 2010 "from scratch" database, the built-in right-click sorting/filtering in forms works great.  In the legacy apps, sorting/filtering is only available in debug mode (Left-Shift) but is not available otherwise.

That sounds like your legacy apps have code on them that cuts off right-click functionality at the knees.
Or monkeys with it.
Before the advent of the Ribbon this was done through CommandBar objects.
Do a CTRL-F for 'CommandBar' in the VBA code of a legacy app, and post up what you find.
If my guess is right, you'll probably find similar code all across you legacy apps

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bcreenAuthor Commented:
Dale and Nick, Thanks.  Each user has 2013 on their own workstation -- we tried runtime, but could never get totally satisfactory results.   Nick I suspect you're right, just don't know WHAT is disabling the filtering.... I set SOME Access options from code....so that could be it,

How do I "do a CTRL-F in VBA code" ? .... opening a VBA module and manually doing a CTRL-F only brings up a search dialog, of course, so I'm not sure what you're asking me to do.

Also, I am using vbWatchdog (error handling routines) and that is SKIPPED when I hold the shift key to open, as it is called in an autoexec macro.  So I'll try opening with Shift, making sure I can filter, then I'll run the autoexec and then retry filtering, to see if I "lost" the capability....
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Nick67Commented:
brings up a search dialog,
Yup.
Search the whole project for 'CommandBars'.
just don't know WHAT is disabling the filtering....
Maybe that was the original programmers intent, or maybe they just wanted custom right-click and didn't want that functionality there.

So I'll try opening with Shift, making sure I can filter, then I'll run the autoexec and then retry filtering, to see if I "lost" the capability....

Excellent!
bcreenAuthor Commented:
Duh!  Search project-wide for CommandBars found nothing.  I am the original developer -- many in Access97, then converted to 2003, then to 2007-2013 format.  This particular form was in an MDB which I converted to ACCDB a few days ago.
Tried bypassing EnableWatchDog and the code that sets Access options and opened the 'test' form as the default form -- still no filtering.  

Now trying to create a blank 2013 ACCDB and importing everything (except Switchboard, apparently) and having the autoexec bypassed and not setting any options via code, etc. . . . .OKAY ! in the just-now-created ACCDB, when I bypass the autoexec and do NOT set any Access option with code, the filtering WORKS!

I suspect that the ACCDB that's been "converted" thru at least 2 generations of Access, is not worth trying to save, if I can get everything put back together into the fresh accdb....

I'll take the next logical steps to see if I can find something in the autoexec and/or the vba code that sets options, which results in filtering going away.

Will post results later today or tomorrow.  Thanks for helping me "think out loud" and for your suggestions.
Nick67Commented:
I offer a lot of help on EE.
On the occasions that I ask for help, it's for helping me "think out loud" and for your suggestions.
Hope you get it cased.

Nick67
bcreenAuthor Commented:
members helped me get thinking along lines I hadn't considered before.  Thanks to all!
Nick67Commented:
Do you have access to pre-Ribbon Access?
Did you have a shortcut menu specified?
Ribbon Access may have the same startup options.
It of course looks different.
A2013 removed all support for commandbars, though.
What happens in A2013 when you bring forward an app that has commandbars specified is an unknown to me.

startup options
bcreenAuthor Commented:
I've NEVER used command bars, nor shortcut menus.
Nick67Commented:
Well, then I think we've exhausted the list of remediable causes.
If it's not done in code and not from a startup option, then I don't think there's a way to fix it in the existing apps.
Fresh files with existing objects imported may be needful


Here's a large mess of code to contemplate.  Hope it may prove useful
It uses VBA to kick out various database objects as text and pull them back into another file.
Which is something you are contemplating do en masse
I found it through Google and adapted it for my use.
It'll provide you with keywords to use in searches for similar functionality.

Private Sub SaveMDBObjectsAsText()
    Dim r As Reference
    DateTimeString = Format(Now(), "yyyymmddhhnn")
    Path = CurrentProject.Path & "\AS_TEXT_" & DateTimeString & "\"
    MkDir Path
    MkDir Path & "DAP\"
    MkDir Path & "form\"
    MkDir Path & "report\"
    MkDir Path & "macro\"
    MkDir Path & "module\"
    MkDir Path & "query\"
    Set app = New Access.Application
    'SaveDataAccessPagesAsText
    SaveFormsAsText
    SaveReportsAsText
    SaveModulesAsText
    SaveQueriesAsText
    SaveMDBBase
    'LoadDataAccessPagesFromText
    'LoadFormsFromText
    'LoadReportsFromText
    'LoadModulesFromText
    'LoadQueriesFromText
    On Error Resume Next
'    With app
'        With .CurrentProject
'            Path = .fullname
'        End With
'
'        For Each r In .References
'            With r
'                If Not .BuiltIn Then
'                    app.References.Remove r
'                End If
'            End With
'        Next r
'
'        For Each r In References
'            With r
'                If Not .BuiltIn Then
'                    app.References.AddFromGuid r.Guid, r.Major, r.Minor
'                End If
'            End With
'        Next r
'
'        .RunCommand acCmdCompileAndSaveAllModules
'        .CloseCurrentDatabase
'        .SysCmd 603, Path, Replace(Path, ".mdb", ".mde")
'        .Quit
'    End With
    Set app = Nothing
    MsgBox "All Done with Text Backup"
End Sub

Private Sub SaveDataAccessPagesAsText()
    Dim Filename$
    Dim Name$
    Dim DataAccessPage As AccessObject
    For Each DataAccessPage In CurrentProject.AllDataAccessPages
        Name = DataAccessPage.Name
        Filename = Path & "DAP\" & Name & ".txt"
        SaveAsText acDataAccessPage, Name, Filename
    Next DataAccessPage
End Sub

Private Sub SaveFormsAsText()
    Dim Filename$
    Dim Name$
    Dim Form As AccessObject
    For Each Form In CurrentProject.AllForms
        Name = Form.Name
        Filename = Path & "form\" & Name & ".txt"
        SaveAsText acForm, Name, Filename
    Next Form
End Sub

Private Sub SaveReportsAsText()
    Dim Filename$
    Dim Name$
    Dim Report As AccessObject
    For Each Report In CurrentProject.AllReports
        Name = Report.Name
        Filename = Path & "report\" & Name & ".txt"
        SaveAsText acReport, Name, Filename
    Next Report
End Sub

Private Sub SaveMacrosAsText()
    Dim Filename$
    Dim Name$
    Dim Macro As AccessObject
    For Each Macro In CurrentProject.AllMacros
        Name = Macro.Name
        Filename = Path & "macro\" & Name & ".txt"
        SaveAsText acMacro, Name, Filename
    Next Macro
End Sub

Private Sub SaveModulesAsText()
    Dim Filename$
    Dim Name$
    Dim Module As AccessObject
    For Each Module In CurrentProject.AllModules
        Name = Module.Name
        Filename = Path & "module\" & Name & ".txt"
        SaveAsText acModule, Name, Filename
    Next Module
End Sub

Private Sub SaveQueriesAsText()
    Dim Filename$
    Dim Name$
    Dim GetQueryNames As ADODB.Recordset
    Set GetQueryNames = CurrentProject.Connection.OpenSchema(adSchemaViews)
    With GetQueryNames
        Do While Not .EOF
            Name = .Fields("TABLE_NAME")
            Filename = Path & "query\" & Name & ".txt"
            SaveAsText acQuery, Name, Filename
            .MoveNext
        Loop
    End With
End Sub

Private Sub SaveMDBBase()
    Dim Filename$
    Dim Name$
    Name = Replace(CurrentProject.Name, CurrentProject.Path, "")
    Filename = Path & Name
    SaveAsText 6, "", Filename
    app.OpenCurrentDatabase Filename
End Sub

Private Sub LoadDataAccessPagesFromText()
    Dim Filename$
    Dim Name$
    Dim DataAccessPage As AccessObject
    For Each DataAccessPage In CurrentProject.AllDataAccessPages
        Name = DataAccessPage.Name
        Filename = Path & "DAP\" & Name & ".txt"
        app.LoadFromText acDataAccessPage, Name, Filename
    Next DataAccessPage
End Sub

Private Sub LoadFormsFromText()
On Error GoTo myerr
    Dim Filename$
    Dim Name$
    Dim Form As AccessObject
    For Each Form In CurrentProject.AllForms
        Name = Form.Name
        Filename = Path & "form\" & Name & ".txt"
        app.LoadFromText acForm, Name, Filename
    Next Form
Exit Sub
myerr:
MsgBox Err.Number & " " & Err.Description & " " & Name
End Sub

Private Sub LoadReportsFromText()
On Error GoTo myerr
    Dim Filename$
    Dim Name$
    Dim Report As AccessObject
    For Each Report In CurrentProject.AllReports
        Name = Report.Name
        Filename = Path & "report\" & Name & ".txt"
        app.LoadFromText acReport, Name, Filename
    Next Report
Exit Sub
myerr:
MsgBox Err.Number & " " & Err.Description & " " & Name
End Sub

Private Sub LoadModulesFromText()
On Error GoTo myerr
    Dim Filename$
    Dim Name$
    Dim Module As AccessObject
    For Each Module In CurrentProject.AllModules
        Name = Module.Name
        Filename = Path & "module\" & Name & ".txt"
        app.LoadFromText acModule, Name, Filename
    Next Module
Exit Sub
myerr:
MsgBox Err.Number & " " & Err.Description & " " & Name
End Sub

Private Sub LoadMacrosFromText()
On Error GoTo myerr
    Dim Filename$
    Dim Name$
    Dim Macro As AccessObject
    For Each Macro In CurrentProject.AllMacros
        Name = Macro.Name
        Filename = Path & "macro\" & Name & ".txt"
        app.LoadFromText acMacro, Name, Filename
    Next Macro
Exit Sub
myerr:
MsgBox Err.Number & " " & Err.Description & " " & Name
End Sub

Private Sub LoadQueriesFromText()
On Error GoTo myerr
    Dim Filename$
    Dim Name$
    Dim GetQueryNames As ADODB.Recordset
    Set GetQueryNames = CurrentProject.Connection.OpenSchema(adSchemaViews)
    With GetQueryNames
        Do While Not .EOF
            Name = .Fields("TABLE_NAME")
            Filename = Path & "query\" & Name & ".txt"
            app.LoadFromText acQuery, Name, Filename
            .MoveNext
        Loop
    End With
Exit Sub
myerr:
MsgBox Err.Number & " " & Err.Description & " " & Name
End Sub

Open in new window


Beware that with some linked tables this will lose the Access-side PK information that you input via the dialog box when creating a linked table.  You wind up with read-only tables until that information can be re-established.

I built a GUI of my own to do that, but the guts of the code that does that work is
Private Sub cmdReCreate_Click()
Dim db As Database
Set db = CurrentDb
Dim qdf As QueryDef
Dim SQL As String
SQL = "CREATE UNIQUE INDEX _PK_" & Me.cboTables.Value & " ON " & Me.cboTables.Value & " ([" & Me.lstFields.Value & "]);"
Set qdf = db.CreateQueryDef("", SQL)
qdf.Execute
MsgBox "Index _PK_" & Me.cboTables.Value & " was created!"
End Sub

Open in new window


The form has a combobox that lists all the tables (primarily SQL Server Views) that would lose their PK when exported/imported.
The AfterUpdate of the combobox populates a listbox with the field names of the selected table.
The code then creates a PK on the selected combox table with the selected listbox field.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.