By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,165 Members | 1,089 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,165 IT Pros & Developers. It's quick & easy.

Invalid procedure call or argument

P: 34
Hi, I get this error message in one of my databases where I use the following code to create a command bar on a Report

Expand|Select|Wrap|Line Numbers
  1.  Dim cmb As CommandBar
  2.  
  3. Set cmb = Application.CommandBars.Add("MyCommandBar")
  4. cmb.Visible = True
  5.  
  6. Dim cbc As CommandBarControl
  7. Set cbc = cmb.Controls.Add(msoControlButton)
  8. cbc.Caption = "Button1"
  9. cbc.Style = msoButtonCaption
  10.  
  11. CommandBars("MyCommandBar").Controls("Button1").OnAction = "=MsgBox(""test!"")" 
I've put this code in a command button on a form, which also opens the report (the reason is that I need a command button on the report so that the user can close it without toolbars). Problem is that it works in one of my db's but not the other. I've checked the following references in both the DBs:
Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.1 Library
:-) VideoSoft vsFlex3 Controls
Microsoft Office 11.0 Object Library

Please advice..
Feb 14 '07 #1
Share this Question
Share on Google+
4 Replies


ADezii
Expert 5K+
P: 8,638
Hi, I get this error message in one of my databases where I use the following code to create a command bar on a Report

Expand|Select|Wrap|Line Numbers
  1.  Dim cmb As CommandBar
  2.  
  3. Set cmb = Application.CommandBars.Add("MyCommandBar")
  4. cmb.Visible = True
  5.  
  6. Dim cbc As CommandBarControl
  7. Set cbc = cmb.Controls.Add(msoControlButton)
  8. cbc.Caption = "Button1"
  9. cbc.Style = msoButtonCaption
  10.  
  11. CommandBars("MyCommandBar").Controls("Button1").OnAction = "=MsgBox(""test!"")" 
I've put this code in a command button on a form, which also opens the report (the reason is that I need a command button on the report so that the user can close it without toolbars). Problem is that it works in one of my db's but not the other. I've checked the following references in both the DBs:
Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.1 Library
:-) VideoSoft vsFlex3 Controls
Microsoft Office 11.0 Object Library

Please advice..
There is nothing wrong with your code. You are probably getting an Invalid Procedure Call or Argument because you are running this code when the CommandBar has already been created, thus generating Error 5. Just set an Error Trap for Err.Number = 5 and Exit the Procedure when this happens. Hope this helped.
Feb 14 '07 #2

NeoPa
Expert Mod 15k+
P: 31,494
There is nothing wrong with your code. You are probably getting an Invalid Procedure Call or Argument because you are running this code when the CommandBar has already been created, thus generating Error 5. Just set an Error Trap for Err.Number = 5 and Exit the Procedure when this happens. Hope this helped.
If ADezii's answer doesn't help (I don't know here) try using :
Expand|Select|Wrap|Line Numbers
  1. CommandBars("MyCommandBar").Controls("Button1").OnAction = "=MsgBox ""test!"""
for your last line.
The MsgBox() form of the command treats it as a function procedure rather than a SubRoutine procedure (Returns a value). Your Command Button is not designed to handle a returned value.
Feb 14 '07 #3

P: 34
Thank you very much, guys! ADezii, you were right. But now - how do I trap the error message? and how do I get the command button to show when the report is being opened?
Feb 22 '07 #4

ADezii
Expert 5K+
P: 8,638
How do I trap the error message?
  • The Trap is set in Code Line # 19:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub cmdTest_Click()
    2. On Error GoTo Err_cmdTest_Click
    3. Dim cmb As CommandBar
    4. Dim cbc As CommandBarControl
    5.  
    6. Set cmb = Application.CommandBars.Add("MyCommandBar")
    7. cmb.Visible = True
    8.  
    9. Set cbc = cmb.Controls.Add(msoControlButton)
    10.     cbc.Caption = "Button1"
    11.     cbc.Style = msoButtonCaption
    12.  
    13. CommandBars("MyCommandBar").Controls("Button1").OnAction = "=MsgBox(""test!"")"
    14.  
    15. Exit_cmdTest_Click:
    16.   Exit Sub
    17.  
    18. Err_cmdTest_Click:
    19.   If Err.Number = 5 Then        'Button already created
    20.     '...code if Button exists goes here
    21.   Else       'Generic Error Messaqge
    22.     MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
    23.   End If
    24.     Resume Exit_cmdTest_Click
    25. End Sub
How do I get the command button to show when the report is being opened?
  • The following code, in the Open() Event of your Report, will make the Command bar Visible then position it at the Top of the Window below any other Command bars already in position.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Report_Open(Cancel As Integer)
    2.   Application.CommandBars("MyCommandBar").Visible = True
    3.   Application.CommandBars("MyCommandBar").Position = msoBarTop
    4. End Sub
Dec 3 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.