calling a public function from a module

[jasn_78]

ok guys i really am a complete newbie at vba and programming in general i have found a public function on a website to reset any list or combo boxes on my form and i just need to figure out how to call this from a command button on any forms i want

the code is as follows

Public Function myReset(frm As Form)
Dim ctrl As Control
'For Each ctrl In Forms(frm)
For Each ctrl In frm
If ctrl.ControlType = acListBox Or ctrl.ControlType = acComboBox Then
ctrl.Value = Null
Else
End If
Next ctrl
End Function



any help would be great

 

[ajetrumpet]

i just need to figure out how to call this from a command button on any forms i want

If that code does in fact work, I would assume you can just do it like this:

Private Sub MyButton Click()

  Call myReset(me.name)

End Sub

Public Function myReset(frm As Form)
Dim ctrl As Control
'For Each ctrl In Forms(frm)
For Each ctrl In frm
If ctrl.ControlType = acListBox Or ctrl.ControlType = acComboBox Then
ctrl.Value = Null
Else
End If
Next ctrl
End Function

 

[Mike375]

I think I have just used call myReset

I have done it when converting a macro to code and the original macro has a runmraco (or a few) and the macros run by runmacro have been converted to a module.

I suspect it is the same result as Adam's answer.

The key point is that is between

Private Sub MyButton Click()



End Sub

 

[ajetrumpet]

Hey mike,

You wanna answer me one question? What in the world are you talking about!?

 

[jasn_78]

aje

i tried your idea and i am getting a type mismatch where u type in

Call myReset(me.name)

is myreset there the name of the function or the module? as the module is named modreports

and the function is myreset

so in other words do i make it say call mymods(me.myreset) ?

cause if you do that still doesnt work

sorry probably easy stuff just new at all this

 

[Mike375]

A macro is converted to code. The converted macro had runmacro actions. The macros that were run by runmacro have been converted to modules.

Thus the macro that was converted to code has in the middle (at the appropriate point) Call XYZ which is replacing or is the equivalent of RunMacro

 

[Mike375]

Call myReset() or Call myReset

Try either. I am not on a computer that has my data base. myReset() is for runcode in a macro. From memory when placed in code the () are left off and call myReset.

 

[Mike375]

PS Don't use the name of the module, it is the name of the function

 

[jasn_78]

ok guys so i have put this in my code

Private Sub cmdRESET_Click()
Call myReset

End Sub

and i get argument not optional message????

 

[Mike375]

I just checked mine and I have Call FunctionName

When a macro runs a function then FunctionName()


I have no idea if it makes any difference ( but I would think not) but I have macro that has runcode action and that has FunctionName() and in that function another is Call Whatevername......and it works

 

[ajetrumpet]

Jason,

Use this for the function:

Public Function myReset()

Dim ctrl As Control

For Each ctrl In screen.activeform
If typeof ctrl is ListBox Or typeof ctrl is ComboBox Then
ctrl.Value = Null
End If
Next ctrl

End Function

And use this for the click event:

Call myReset

For future reference, it's a good practice not to copy and paste code that you find on the internet.

 

[jasn_78]

thanks adam that worked so was itthat the end if and next where round the wrong way?

 

[Mike375]

For future reference, it's a good practice not to copy and paste code that you find on the internet.

Because I could have put it up

macro lover

 

[ajetrumpet]

thanks adam that worked so was itthat the end if and next where round the wrong way?

Nothing was out of order. The reference syntax was incorrect.

I doubt there is anything wrong with the code that you originally posted Matt, but that doesn't change the fact that the Visual Basic language (like all of them) has a million different "types" of syntax combinations that you can use for various commands and references.

And, most of those "different" ones all do the same thing, so it all boils down to what you know, not what is right.

 

[ChrisO]

G’day Jason.

To try and explain the situation we need to look at a few things here.

Strictly speaking ‘Public Function myReset(frm As Form)’ does not need to be a Function because it does not return a value so it could have been a Sub instead.

However that Function is expecting one argument to be passed to it. In this case the argument is a reference to a Form and not a Forms name which would be a string.

So the line of code: -

Call myReset(Me.Name)

was incorrect because Me.Name is a string and the Function is expecting a reference to a Form.

So: -

Call myReset(Me)

would have worked.

But if you wanted to make the Function a Sub you would need to call it as: -

myReset Me

and not: -

myReset (Me)

because the last line of code would also raise an error.

In this case, relying on the calling Form being the Active Form is valid but it will not always be the case if something other than a command button initiates the call.

Hope that makes some sense.

Regards,
Chris.

 

[Moniker]

Jasn,

The problem was you had this:

Private Sub cmdRESET_Click()
    
    Call myReset

End Sub

And the code it was calling was setup like this:

Public Function myReset([COLOR="Red"]frm As Form[/COLOR])
    
    Dim ctrl As Control
    'For Each ctrl In Forms(frm)
    For Each ctrl In frm
        If ctrl.ControlType = acListBox Or ctrl.ControlType = acComboBox Then
            ctrl.Value = Null
        Else
        End If
    Next ctrl

End Function

The part I put in red there is a parameter. It is a required argument. Hence, you were getting the error "Argument not optional". A required argument is something that a function is expecting to use while running. An optional argument is just like what it sounds like -- it's optional -- but before I confuse you, just know that most functions require at least one argument, so if you have just one argument, it's almost always required. (Note that there's no need for this to be a function -- it returns nothing -- so it should be a sub, but I don't want to go off track and the function will work fine.)

So what's happening here? You have a function called myReset. In order for myReset to know what to do, it has to know what you want to reset. The "what you want to reset" part is the argument. Therefore, you pass the argument to the function (in this case, you were passing the form argument) that is the control you want work with.

The provided example passes an entire form object (not the best solution for your situation, by I won't go into that now), and you then search that entire object for controls that are combo boxes or listboxes. That is what this code is doing:

Dim ctrl As Control
    'For Each ctrl In Forms(frm)
    For Each ctrl In frm
        If ctrl.ControlType = acListBox Or ctrl.ControlType = acComboBox Then
            ctrl.Value = Null
        Else
        End If
    Next ctrl

(BOLD: English translation)

Dim ctrl As Control: Set a variable named ctrl to be of the control type of object. A control is a textbox, a label, listbox, etc. Most things you can put on a form are a control of one type or another.

For each ctrl In Forms(frm): For each control in the forms collection where the form name is the variable frm, which is the argument passed to the function, do something.

If ctrl.ControlType = acListBox Or ctrl.ControlType = acComboBox Then: If the control on the form is a listbox or a combobox, then do something.

ctrl.Value = Null: If the control type is a listbox or a combobox, set its value to NULL.

Else: Not needed here.

Next ctrl: Move on to the next control on the form frm (the passed argument) until you've cycled through all the controls on the form frm.

Hopefully, that makes a little more sense.

 

[Mike375]

Moniker and Chris

Are there any potential problems with Call FunctionName within code?

I ask because I am going through a copy of my data base and converting macro to code.....and I am doing Call FunctionName for the macros in runmacro that have been converted to modules.

I would sure hate to get to the other end and find Call FunctionName was a problem

Are there any problems if the amount of typing/pages is large. I have put the code from a couple of converted macros within code and all worked OK. But it sure generates some writing. Can that cause any problems. I have not seen any problems, all works fine....but the look of it makes you wonder????

 

[ChrisO]

There are not very many subtle errors when calling Subs or Functions because most will be picked up very early during compiling and testing.

There are people that would disagree with me on this, that’s their prerogative or course, but I would prefer not to use the Call word. It is often used to ‘Call’ a Function and sink the unused return value when in fact the function should have been a Sub in the first place. Functions always return a value and even if undeclared they will return Empty.

So, apart from those times that a procedure must be a Function, even though its return value will not be used, then use a Sub instead.

If there is any real subtle area when calling Functions or Subs it is in passing and receiving the arguments by reference, which is the default in VBA. Changes made to the arguments will reflect back to the caller. This may or may not be the intention so I would suggest passing by value if that option is available to you under the circumstances.

 

[Mike375]

Chris,

Thanks for that. So do you think it would be better to put the code "in the code" rather than calling a function, that is a converted macro. I have done a bit of that on a small scale an all was OK.

I know what you mean about "not very many subtle errors". The red writing jumps up.

So far, if Debug/Compile is clear, then all has worked

I get the feeling that the "conversion process" puts a lot of writing down that is not needed. This is most noticeable with macro conditions.

 

[Zaeed]

I would suggest that you read a few books and get some basic coding skills under your belt, and not use the convert macro to code ability. It will give you a lot of junk code.

As I see it (although it may be different for access), you should be only putting functions into modules that are going to be called in multiple locations. For example, this clear control function, that is not specific to one form, it can be used on multiple forms. If your refering to say a Command Buttons click code, that should not go in a function, since it is specific to the form, and not used elsewhere.