UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Simple Shortcut Menu, Access 2013    
 
   
avincent61
post Jan 23 2020, 08:01 AM
Post#1



Posts: 11
Joined: 20-January 20



I'm running Access 2013 as a split DB. I'm a semi-self taught intermediate DB administrator and use these forums frequently, but haven't found my particular issue on here.

My goal: Prevent users from accidentally opening forms/reports etc in design/layout mode, but still allow them to right click on form data to cut/copy/paste.
Currently, I have set the "Shortcut Menu" option on each individual form/report to "No", but that prohibits any & all right-clicking for users. This was my temporary fix as bad things keep happening mad.gif .

I found a VBA code that should work, but I think I am missing a piece...possibly a Macro? I really don't know.

Hangup: After saving the code, I click debug and get this error: "Compile error: User-defined type not defined" (attached)
Ignore the sort functions in the code as I am going to remove this, I just wanted to see if the function would work.

This is probably a silly fix, but I can't figure out what I'm missing...Please help! confused.gif pullhair.gif

Attached File(s)
Attached File  ShorcutMenu_Code.txt ( 891bytes )Number of downloads: 1
Attached File  VBA_Code_Error.JPG ( 117.9K )Number of downloads: 4
 
Go to the top of the page
 
June7
post Jan 23 2020, 08:13 AM
Post#2



Posts: 1,222
Joined: 25-January 16
From: The Great Land


Why didn't you provide code as text in post instead of attachment?

Error means missing a library reference. Check your references in VBA editor - any show ERROR?

I don't get compile error.

This post has been edited by June7: Jan 23 2020, 08:15 AM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
GroverParkGeorge
post Jan 23 2020, 08:20 AM
Post#3


UA Admin
Posts: 36,771
Joined: 20-June 02
From: Newcastle, WA


First things first.

Welcome to UtterAccess.


Code is usually more helpful that screenshots for this kind of problem.

You can use the icon in the editor to wrap your code in code tags prior to posting it.
Attached File  CodeTags.PNG ( 23.55K )Number of downloads: 2


You might find it easier or quicker, though, to implement a shortcut add in offered, free, here on Dale Fye's websight.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
avincent61
post Jan 23 2020, 08:26 AM
Post#4



Posts: 11
Joined: 20-January 20



Sorry, first time posting...usually I just read

CODE
Sub CreateSimpleShortcutMenu()

  On Error Resume Next 'If menu with same name exists delete
  CommandBars("ShowDataShortcutMenu").Delete
  
  Dim cmb As CommandBar
  
  Set cmb = CommandBars.Add("ShowDataShortcutMenu", msoBarPopup, False, False)
      With cmb
          .Controls.Add(msoControlButton, 21, , , True).BeginGroup = True     'Cut
          .Controls.Add msoControlButton, 19, , , True    'Copy
          .Controls.Add msoControlButton, 22, , , True    'Paste
          .Controls.Add(msoControlButton, 4016, , , True).BeginGroup = True   'Sort Ascending
          .Controls.Add msoControlButton, 4017, , , True  'Sort Decending
          .Controls.Add(msoControlButton, 640, , , True).BeginGroup = True  'Filter By Selection
          .Controls.Add msoControlButton, 605, , , True   'Remove Filter/Sort

      End With
      
  Set cmb = Nothing
  
End Sub

This post has been edited by GroverParkGeorge: Jan 23 2020, 08:44 AM
Go to the top of the page
 
avincent61
post Jan 23 2020, 08:29 AM
Post#5



Posts: 11
Joined: 20-January 20



Ok clearly I'm not getting this right...here's the code


CODE
[/code]Sub CreateSimpleShortcutMenu()

  On Error Resume Next 'If menu with same name exists delete
  CommandBars("ShowDataShortcutMenu").Delete
  
  Dim cmb As CommandBar
  
  Set cmb = CommandBars.Add("ShowDataShortcutMenu", msoBarPopup, False, False)
      With cmb
          .Controls.Add(msoControlButton, 21, , , True).BeginGroup = True     'Cut
          .Controls.Add msoControlButton, 19, , , True    'Copy
          .Controls.Add msoControlButton, 22, , , True    'Paste
          .Controls.Add(msoControlButton, 4016, , , True).BeginGroup = True   'Sort Ascending
          .Controls.Add msoControlButton, 4017, , , True  'Sort Decending
          .Controls.Add(msoControlButton, 640, , , True).BeginGroup = True  'Filter By Selection
          .Controls.Add msoControlButton, 605, , , True   'Remove Filter/Sort

      End With
      
  Set cmb = Nothing
  
End Sub[code]

This post has been edited by avincent61: Jan 23 2020, 08:37 AM
Go to the top of the page
 
avincent61
post Jan 23 2020, 08:36 AM
Post#6



Posts: 11
Joined: 20-January 20



June 7
I can get to the references window, but how would i know if there is an error? (attached)

GroverParkGeorge
Thank you...I'll re-read the help page at some point.
Attached File(s)
Attached File  VBA_Reference_Libraries.JPG ( 47.61K )Number of downloads: 1
 
Go to the top of the page
 
GroverParkGeorge
post Jan 23 2020, 08:44 AM
Post#7


UA Admin
Posts: 36,771
Joined: 20-June 02
From: Newcastle, WA


If there were a reference marked as "Missing" that would be one indication of a reference error.

I don't see that here.

Do you have Option Explicit set at the top of EVERY module?

Attached File  option_explicit_set.jpg ( 68.01K )Number of downloads: 1

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Jan 23 2020, 08:45 AM
Post#8


UA Admin
Posts: 36,771
Joined: 20-June 02
From: Newcastle, WA


You got it, but not before I fixed in the earlier post. Sorry for stepping on your toes.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
avincent61
post Jan 23 2020, 09:02 AM
Post#9



Posts: 11
Joined: 20-January 20



Grover

It looks like I do not.

Also, I checked "Microsoft Office 15.0 Object Library" and that did modify the shortcut menu, but the below error pops up first.

Attached File(s)
Attached File  Right_Click_Error.JPG ( 23.83K )Number of downloads: 1
 
Go to the top of the page
 
avincent61
post Jan 23 2020, 09:08 AM
Post#10



Posts: 11
Joined: 20-January 20



Not at all Sir.
I did it incorrectly again before clicking the correct button...*pushes the door marked pull* smirk.gif

I figured out why I got that second error. I manually entered "CommandBar" in the Shortcut Menu Bar field under the "Other" tab.
I deleted it and now the right-click function works perfectly! woohoo.gif

I did add the "Option Explicit" to the module as well. Should this be on all of my VBA modules?
This post has been edited by avincent61: Jan 23 2020, 09:09 AM
Go to the top of the page
 
GroverParkGeorge
post Jan 23 2020, 09:12 AM
Post#11


UA Admin
Posts: 36,771
Joined: 20-June 02
From: Newcastle, WA


Excellent. Congratulations on solving this problem.

YES, ALL modules need to have Option Explicit on. It is annoying beyond belief that Microsoft does not do that by default.

Here's how to enforce for all future development, but you'll still have to manually add it to existing modules.
Attached File  requireVariableset.jpg ( 83.27K )Number of downloads: 1


I think the reason I was once given was along the lines that if they did a lot of existing "legacy" Access databases might break, so I undertand why they don't.



--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
avincent61
post Jan 23 2020, 09:20 AM
Post#12



Posts: 11
Joined: 20-January 20



Thanks! I'll work on that right away.

And apparently I have not solved it...I think I may be missing a Macro.
The attached error message image from my previous reply went away, but then the shortcut menu didn't work again.
It seems like it's looking for the shortcut menu as a macro.
Attached File(s)
Attached File  Right_Click_Error.JPG ( 23.83K )Number of downloads: 1
 
Go to the top of the page
 
GroverParkGeorge
post Jan 23 2020, 09:25 AM
Post#13


UA Admin
Posts: 36,771
Joined: 20-June 02
From: Newcastle, WA


Not necessarily.

Try this next. The name of your accdb will appear where I dropped the white box on mine.
Attached File  compile.png ( 18.13K )Number of downloads: 0


It should throw an error and highlight one line. Which line is that?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Jan 23 2020, 09:29 AM
Post#14


UA Admin
Posts: 36,771
Joined: 20-June 02
From: Newcastle, WA


Maybe this post will help a bit.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
avincent61
post Jan 23 2020, 09:32 AM
Post#15



Posts: 11
Joined: 20-January 20



It compiles without error then is grayed out.
Attached File(s)
Attached File  Compile.JPG ( 38.96K )Number of downloads: 0
Attached File  Compile_Menu.JPG ( 66.27K )Number of downloads: 0
 
Go to the top of the page
 
GroverParkGeorge
post Jan 23 2020, 09:34 AM
Post#16


UA Admin
Posts: 36,771
Joined: 20-June 02
From: Newcastle, WA


Good. So that means there are no syntax errors but something is still not right.

Let's add error handling to the sub and see what that tells us.

First step is to put a break point in the code and step through it a line at a time until it fails.


--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
avincent61
post Jan 23 2020, 09:46 AM
Post#17



Posts: 11
Joined: 20-January 20



The Macros window pops up.
Not that I'm super experienced, but this isn't what usually occurs when breaking and then pressing F5.

Attached File(s)
Attached File  Breakpoint.JPG ( 60.48K )Number of downloads: 0
 
Go to the top of the page
 
GroverParkGeorge
post Jan 23 2020, 09:54 AM
Post#18


UA Admin
Posts: 36,771
Joined: 20-June 02
From: Newcastle, WA


Maybe. thumbup.gif

We are making progress because it means that your code is making a call to the name of an object which Access assumes to be a macro of VBA procedure.

So, that is what the earlier error message was saying.

It looks to me like the culprit is "CommandBar"


So, let's see what happens if you change this line:

Dim cmb As CommandBar

to this:

Dim cmb As Office.CommandBar

BTW: I know I'm putting you through a bunch of stuff instead of just putting up a solution. I am of the belief that the solutions you develop yourself are more meaningful in the long run.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
avincent61
post Jan 23 2020, 10:08 AM
Post#19



Posts: 11
Joined: 20-January 20



Believe me Grover, I appreciate the step-by-step. When I started the overhaul of this DB, the tech that my company hired used the same techniques. It really helped me grasp and retain things.
I should have thought to break and step, but I don't use it often enough to remember it in times like this.

Ok, changed to "Dim cmb As Office.CommandBar"

-Macro window still pops up
-Error still pops up on right-click
-custom shortcut menu does not execute (standard shortcut menu runs instead)

Found similar code that has this other line "Dim newMenu As CommandBarControl"
Would this make a difference?

CODE
[/code]
Sub CreateSimpleShortcutMenu()
      Dim newMenu As CommandBarControl
Dim cmb As CommandBar
  On Error Resume Next 'If menu with same name exists delete
  CommandBars("ShowDataShortcutMenu").Delete

    CommandBars("ShowDataShortcutMenu").Delete

    Set cmb = CommandBars.Add("ShowDataShortcutMenu", msoBarPopup, False, False)
    With cmb
          .Controls.Add(msoControlButton, 21, , , True).BeginGroup = True     'Cut
          .Controls.Add msoControlButton, 19, , , True    'Copy
          .Controls.Add msoControlButton, 22, , , True    'Paste
          .Controls.Add(msoControlButton, 4016, , , True).BeginGroup = True   'Sort Ascending
          .Controls.Add msoControlButton, 4017, , , True  'Sort Decending
          .Controls.Add(msoControlButton, 640, , , True).BeginGroup = True  'Filter By Selection
          .Controls.Add msoControlButton, 605, , , True   'Remove Filter/Sort

      End With
      
  Set cmb = Nothing
  
End Sub[code]

This post has been edited by avincent61: Jan 23 2020, 10:15 AM
Go to the top of the page
 
avincent61
post Jan 23 2020, 10:57 AM
Post#20



Posts: 11
Joined: 20-January 20



Solution was found in this video:

Custom Shortcut Menu VBA

I was close, but this way is a little easier.
Also, I unchecked the "Office" reference.

Thanks for trying.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    21st February 2020 - 10:43 AM