RunCode macro action from 'hybrid' Access web database error

Question

I have an Access web database

In this database, I have a web form with a button on it
In its on-click event macro, it calls RunMacro Macro1 (client-macro) which fires successfully
in Macro1, if I call RunCode Function Name: DoTest() it returns the error: "The function you entered can't be used in this expression... Error Number 2426 (or 2950 if I have an "=" symbol in front of my function)

This issue is easily reproduced by doing these steps:

Create a blank web database in MS Access
Add and save a table
Create a default form from that table and put a button on it

Create a new VBA module "Module1" and put the following function in it:

Public Function DoTest()
    MsgBox "Test function runs smoothly"
End Function

Create a client-macro "Macro1" with RunCode: Function Name: DoTest() (Note that IntelliSense recognizes the macro, and it runs fine from here)
Create the form's button's on-click macro event RunMacro: Macro Name: Macro1
Click the form's button from form-view to receive the error:

The function you entered can't be used in this expression.

You may have used a DoEvents, LBound, UBound, Spc, or Tab function in an expression.

You may have used an aggregate function, such as Count, in a design grid or in a calculated control or field.

Clicking "Ok" shows error number 2426 or 2950 depending on if you have an equals sign before your function name or not in the client-side macro's RunCode command.

I've tried all of the tips from this very similar question without any luck. Access seems to find the function fine, as replacing the function name with gibberish gives a very different error.

What the heck am I doing wrong?

In my actual web database which uses Access Services published to SharePoint 2010, I use an If IsClient() Then macro statement on the form's button's on-click event to ensure that the VBA is only running in the client mode, but that is not relevant to the error I'm receiving.

score 0 · Accepted Answer · answered Nov 30 '16 at 17:33

After some extra digging, I came across this post in another forum. Here, the author simply explains that what I'm trying to do doesn't work (when you would think that it should).

Because it's on another forum, I'll spell out the workaround that also worked for me (credit: jakedrew, UtterAccess post, Jun 9 2011). Basically, you need to use a client-form in addition to a client-macro as intermediate steps to get from your web form to VBA. I made this effective for my current application by doing the following:

I re-saved my function as a sub instead:

Public Sub DoTest(ByVal intArg As Integer)
    MsgBox "Test sub runs smoothly with argument = " & intArg
End Sub

Create a client-form (mine is named "frmVBA_Bridge") and create the following OnOpen event:

Private Sub Form_Open(Cancel As Integer)
    ' run your code using this command.  Note that if you don't have an argument, you won't include the part of this line following the comma
    Application.Run TempVars("SubName").Value, TempVars("Arg1").Value
    ' reset the "parameters"
    TempVars("SubName").Value = ""
    TempVars("Arg1").Value = 0
    DoCmd.Close acForm, Me.Name, acSaveYes
End Sub

The client-side macro "Macro1" now instead does:

OpenForm
  Form Name  frmVBA_Bridge
  View Form
  Where Condition
  Data Mode
  Window Mode  Hidden

The web form's button's on-click embedded macro gets changed to:

If  IsClient() = True Then
  SetTempVar
    Name SubName
    Expression ="DoTest"
  SetTempVar
    Name Arg1
    Expression = 100
  RunMacro
    Macro Name Macro1
End If

Quite a bit of a workaround for such a simple thing, but it seems to do the job!

RunCode macro action from 'hybrid' Access web database error

1 Answers1