?
Solved

MS Access Listbox Context Menu

Posted on 2012-12-21
7
Medium Priority
?
1,089 Views
Last Modified: 2012-12-24
On my listbox I would like to add a function to the Right Mouse Click.

Essentially I was it to say..."Remove Client from Visit"

On click run a Sub.
0
Comment
Question by:lrbrister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 48

Accepted Solution

by:
Dale Fye earned 1000 total points
ID: 38713977
You can create your own shortcut menu and assign that to the listbox ShortCutMenu property.  The following is a sample code segment:
Public Sub MenuClientVisit()

    Dim cbr As Object
    Dim cbrButton As Object

    On Error GoTo ProcError
    
    Set cbr = CommandBars.Add("ClientVisitShortcut", BarPopup, , True)

    With cbr

        Set cbrButton = cbr.Controls.Add(ControlButton, , , , True)
        With cbrButton
            .Caption = "&Remove Client from Visit"
            .Tag = "RemoveClientVisit"
            .OnAction = "=fnRemoveClientVisit()"
        End With

    End With

ProcExit:
    Exit Sub
ProcError:
    msgbox err.number & vbcrlf & err.desription, vbOKONLY, "ClientVisitMenu error"
    
End Sub

Open in new window

This code will create a commandbar named ClientVisitShortcut with a single item "Remove Client from Visit".  The key is that you then need to write the code for the action which will be taken when you actually select the "Remove Client from Visit" option.  This code must be in the form of a function (not a subroutine), but it does not have to return a value, and in this example, the function name would be "fnRemoveClientVisit"; and it must be placed in a public code module (not in a forms code module).

I couple this code with a subroutine which returns a boolean value that indicates whether the commandbar already exists.
Public Function CmdBarExists(BarName As String) As Boolean

    Dim intControls
    
    On Error Resume Next
    intControls = CommandBars(BarName).Controls.Count
    If Err.Number = 0 Then
        CmdBarExists = True
    Else
        CmdBarExists = False
    End If
    
End Function

Open in new window

Then, on the form where this listbox is located, add the following code to the Open event of the form.
Private Sub Form_Open

    'Check to see if the commandbar already exists, and respond appropriately
    If CmdBarExists("ClientVisitShortcut") = false Then Call MenuClientVisit
    me.lst_ClientVisits.ShortcutMenuBar = "ClientVisitShortcut"

End Sub

Open in new window

This will cause your application to check to see whether the commandbar already exists, and if not, it will create it.  It then assigns the ClientVisitShortcut commandbar to the rightclick event of lst_ClientVisits.
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1000 total points
ID: 38713992
No code version ... 5 minutes to create:

Easily create real, professional Windows menus, toolbars and Right Click menus.

This works in the A2003 (and prior) environment and MDB format.  And they will port (with MDB) into A2007/10 environment and show up on the Add-Ins Ribbon.

Everything you need is here - no code ... see this link:

http://www.jamiessoftware.tk/articles/menubars.html
       

mx
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38714044
Not sure about right-clicks in Access 2007/2010.

Why not just put this on a button?
So the user could just select the Record from the listbox, then click the button to "remove"...

The code would be something like this regardless:

Dim strSQL as string
strSQL ="DELETE * FROM YourTable WHERE ClientID=" & lstClient
Currentdb.execute strSQL, dbfailonerror
me.lstClient.requery


JeffCoachman
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 75
ID: 38714105
"Not sure about right-clicks in Access 2007/2010."

?
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 38714285
Right clicks (shortcut menus) are not a problem in 2007, but have not tested with a 2010 listbox.
0
 
LVL 75
ID: 38714313
All MenuBars (Menus, Toolbars & Right Click) port over to A2010 from A2003.

mx
0
 

Author Closing Comment

by:lrbrister
ID: 38718466
Sorry for late get back.

Tested both answers.

Thanks
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question