Calling Public Function in a form

[wchernock]

I am trying to call a Public function from the VBCode executed on a cmdButton click.

I have a startMetricsDatabase (standard module) with the following code:
Option Compare Database
Public CurrentForm As String
Public PreviousForm As String
Public NextForm As String
Public CurrentXID As String
Public CurrentClarityID As String

Public Function VariableInit()

CurrentForm = "Frm-Login"
PreviousForm = "Frm-Login"
NextForm = "Frm-Login"
CurrentXID = xwvc03i
CurrentClarityID = 999999

MsgBox "Variables have been set", vbOKOnly

End Function

Public Function ShowPubVar()

MsgBox "CurrentForm = " & CurrentForm, vbOKOnly
MsgBox "PreviousForm = " & PreviousForm, vbOKOnly
MsgBox "NextForm = " & NextForm, vbOKOnly
MsgBox "CurrentXID = " & CurrentXID, vbOKOnly
MsgBox "CurrentClarityID = " & CurrentClarityID, vbOKOnly

End Function


Within my form, I have a cmdButton with the following code:

Private Sub cmd_Login_Click()
On Error GoTo Err_cmd_Login_Click

Call VariableInit
Call ShowPubVar
.
.
.

The code errors out with a compile error: Sub or Function not defined. Is my standard module built incorrectly?

 

[GohDiamond]

I have duplicated your efforts as you've indicated in a module, created a simple form with a button and clicked on it. It works fine in MS office Access 2003 with all the latest updates - Visual Basic 6.4.9972. What version are you using?

My advice would be to open the "immediate window" (in VBA editor under the View menu or use CTRL+G) and then call the function to see if it works there. you may have a simple mispelling somewhere between the form and the module.

I've heard from many users on this forum that Access can be very picky about syntax and although I haven't found it to matter in the test I just ran, you may want to add the () to the name of the function you are calling. The VBA editor will correct the script if the () is not necessary.

Check for spelling... when I called the function mispelled, it gave me a MS VBA warning dialog box saying Compile Error: Sub or Function not Defined

Hope that helps!
Cheers,
Goh

 

[boblarson]

I
I've heard from many users on this forum that Access can be very picky about syntax and although I haven't found it to matter in the test I just ran, you may want to add the () to the name of the function you are calling. The VBA editor will correct the script if the () is not necessary.

Check for spelling... when I called the function mispelled, it gave me a MS VBA warning dialog box saying Compile Error: Sub or Function not Defined

Okay, here's a quick rundown -

1. I ran the same code and didn't get the sub or function defined error so if you created your button's code by manually typing the words Private Sub cmd_Login_Click() then you messed up because Access doesn't like you creating your own events. It handles those so you have to select them from the list and don't try to create them.

2. When calling a function or sub in a standard module, you do not need to include the () when you are running them unless they are encapsulating input parameters that you have to input, but you don't even have to (and it won't let you) put the () in when you have the function or sub with those parameters on the left side of an equals sign or with no keyword in front. For example, if you had a function of:
Public Sub MyTest(strInput1 As String, strInput2 As String)
When you go to run it you would call it thus:

MyTest "mystringvaluehere", "mystringvaluehere"

and NOT MyTest("mystringvaluehere","mystringvaluehere")

as it will error out on you because it is on the left side of an equals sign. However, if you use the keyword CALL (which isn't necessary but you can use it) you would then need to put the parens, but ONLY if there are input parameters.

If you have a function that returns a value:
Public Function MyTest(strString1 As String, strString2 As String) As String

you should set something equal to it (although again it isn't necessary but it would be pretty pointless if it returned a value, to not use it:

strAnotherString = MyTest("stringvalue1", "stringvalue2")


3. Within Queries and control sources, functions (you can't call subs from there) should be called by using the ().

Now, I may have missed some nuances with it but hopefully that helps a bit.

And by the way the functions seem to be working and the undefined function may be that you have, as mentioned, typed in the event instead of selecting or it could be that you have the module named the same as the function (the module should never have the same name as a function or sub in it).

 

[wchernock]

Thanks.

I did create a new DB and cut and paste my code in and it worked. I may have something corrupt. My thought is to delete and rebuild the form.


By the way, I am running
Access 2003 (11.6566.8132) SP2
VB 6.3 Version 9972 VBA Retail 6.4.9972 Forms3 11.0.6650

 

[GohDiamond]

Thanks Bob, your explanation even clears up a lot of salient details that I appreciate learning as well.

Cheers!
Goh

 

[avincent61]

Okay, here's a quick rundown -

1. I ran the same code and didn't get the sub or function defined error so if you created your button's code by manually typing the words Private Sub cmd_Login_Click() then you messed up because Access doesn't like you creating your own events. It handles those so you have to select them from the list and don't try to create them.

2. When calling a function or sub in a standard module, you do not need to include the () when you are running them unless they are encapsulating input parameters that you have to input, but you don't even have to (and it won't let you) put the () in when you have the function or sub with those parameters on the left side of an equals sign or with no keyword in front. For example, if you had a function of:
Public Sub MyTest(strInput1 As String, strInput2 As String)
When you go to run it you would call it thus:

MyTest "mystringvaluehere", "mystringvaluehere"

and NOT MyTest("mystringvaluehere","mystringvaluehere")

as it will error out on you because it is on the left side of an equals sign. However, if you use the keyword CALL (which isn't necessary but you can use it) you would then need to put the parens, but ONLY if there are input parameters.

If you have a function that returns a value:
Public Function MyTest(strString1 As String, strString2 As String) As String

you should set something equal to it (although again it isn't necessary but it would be pretty pointless if it returned a value, to not use it:

strAnotherString = MyTest("stringvalue1", "stringvalue2")


3. Within Queries and control sources, functions (you can't call subs from there) should be called by using the ().

Now, I may have missed some nuances with it but hopefully that helps a bit.

And by the way the functions seem to be working and the undefined function may be that you have, as mentioned, typed in the event instead of selecting or it could be that you have the module named the same as the function (the module should never have the same name as a function or sub in it).

Here it is 2022, and you saved my day.
Thanks!