Me doesn't work when sub moved to module 2

[waubain]

I am trying to use a module for the first time. This code, to clear all fields, works embedded in the form but not when I move it to the module. I changed Private to Public, but then I get an error message "improper use of Me". This makes sense since Me. is no longer the current form. I cannot figure out what to change it to. I tried [Forms]!Controls, but just a different error message.

I want to make this Sub available to other forms.

Private Sub ClearCriteria()

    Dim ctl As Control

    For Each ctl In Me.Controls
        Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acCheckBox
                If ctl.ControlSource = "" Then
                    ctl.Value = Null
                End If
            Case Else
        End Select
    Next ctl

End Sub

Thanks.

 

[PHV]

just a different error message
Well, which message ?????

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya waubain . . .

Pass the form to the routine as a [blue]Form Object[/blue].

[blue]Public Sub ClearCriteria([purple][b]frm[/b][/purple] As Form)

   Dim ctl As Control

   For Each ctl In [purple][b]frm[/b][/purple].Controls
      Select Case ctl.ControlType
         Case acTextBox, acComboBox, acListBox, acCheckBox
            If ctl.ControlSource = "" Then
               ctl.Value = Null
            End If
      End Select
   Next ctl

End Sub[/blue]

If your calling the routine from the form you want to null, its:

[blue]   Call ClearCriteria([purple][b]Me[/b][/purple])[/blue]

To call the routine from anywhere else (of course the form has to be open):

[blue]   Call ClearCriteria(Forms![purple][b]YourFormName[/b][/purple])[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MazeWorX]

Public Function ClearCriteria(frmForm As String)

    Dim ctl As Control
    For Each ctl In Forms(frmForm).Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox, acCheckBox
            If ctl.ControlSource = "" Then
                ctl.Value = Null
            End If
        Case Else
        End Select
    Next ctl
End Function

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[waubain]

PH:" The error was MS Access cannot find the reference form Controls". I knew that Controls was not a form name, so I was hoping to make myself look less dumb by not saying anything...lesson learned.

Aceman: That worked perfect. Thanks for the reply.

MazeWorX: I am still learning and I thought about trying a function, but what I read said to use a function when you want to return an answer, like make a calculation. This makes it less clear to me the difference between a Public Sub and a Function. Time to read more.

Thank you all.Regards,Waubain

 

[MajP]

A sub does not return a value

A function returns a value, but you can choose to either drop it or do something with it.

If you want you could make all routines functions. There are a couple of tricks though on how you call the sub or function.

assume you have a sub that takes two parmeters

public sub someSub(x as integer, y as integer)
'do something
end sub

To call this sub in code
someSub 4, 7
You can choose to use the call keyword but then you need ()
call someSub(4,7)

Assume you have a function
public function someFunction(x as integer, y as integer) as Integer
'do something
someFunction = ........ 'something to return
end sub

To call this function from code and drop the return value
someFunction x, y
To use the return value need ()
someVariable = someFunction(x,y)
To use the call keyword and drop the return value
call someFunction (x,y)


One big advantage of using a function and not a sub in Access, is in a form or report property. If it is a function you can put the name in the property. Example

onClick: =SomeFunction([field1],[field2])

Now you can hilite hundreds of controls and put this in the property. They would all fire this function on click. Why it has to be a function I have no idea.

 

[MazeWorX]

I like to be able to confirm that my function has completed successfully as Majp has suggested. So in this case i would call the function like this

Dim intClear as Integer

intClear = ClearCriteria("MyFormName")
'ClearCriteria returns 1 for True and 0 for false
If intclear = 1 then
'do something
Else
'do something else
End if

this is a simple example but i think you get the gist of it

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[TheAceMan1]

[blue] ... This makes it less clear to me the difference between a Public Sub and a Function.[/blue]

You already know the difference!

[blue]A Function is [purple]capable[/purple] of returning an answer.[/blue]

However ... there's no law in VBA that saids [purple]you have to use the return value[/purple]. In fact ... you can write code that completely avoids the return value. Example ...

[blue] Call [purple]FunctionName()[/purple][/blue]

... does not use the return value!

There are other instances in access where a function is required where you would normally use a subroutine. Macros for example (the macros window). If you use the [blue]RunCode[/blue] action, you can only specify a [blue]Function[/blue] ... the return value is disregarded! In the [blue]Action[/blue] property of a commandbar control ... a function is required ... again the return value is disregarded.

Can you see!?

[blue]Your Thoughts? . . .[/blue]


See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[waubain]

Thanks everyone for the lessons. They are very much appreciated.

I am a pharmacist that dispenses all the medications used in research at our teaching hospital. I left the Intensive Care Unit 6 months ago to take this position. I queried my research peers and to my surprise everyone is still using paper forms to track the in's and outs of research medication. I am trying to move my facility to a electronic version. All I know is a little bit of Access so I appreciate all the help on this forum. It is proving a much larger challenge that I first anticipated. Too many variations on how the same thing can be done. Probably why we are still using paper. Please let me know if I start asking too many questions. Sorry for the rant.
Waubain

 

[MazeWorX]

Waubain, people generally keep using what they are comfortable with. They either lack the vision or discipline to change what already works for them. Like the old saying 'cant see the trees for the forest'. As a developer you don't need to force it down their throats you just need to make it simple enough for them to use which generally means more work for you the developer. Yes there are usually several ways to do something you just need to decide which way works best for you. BTW there are never too many questions, i would hate to think of what this world would be like if people stopped asking questions so ask away

M

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>