Need replacement of ME

[Guest]

Hi folks,

I have the following code which works great in any form. I tried to create
the same code in a module which could be called by any form but in vain. I
got the error message “Compile error: invalid use of Me keywordâ€. Could
anyone show me how to fix the problem?

Thanks in advance.

Tim.

Sub x()

Dim ctl As Control
Dim strName As String

For Each ctl In Me.Controls

MsgBox ctl.Name

Next ctl

End Sub

 

[Douglas J Steele]

You need someway of letting the code know what form you're trying to do this
for.

One approach is to pass a reference to the form:

Sub x(WhatForm As Form)

Dim ctl As Control
Dim strName As String

For Each ctl In WhatForm.Controls
MsgBox ctl.Name
Next ctl

End Sub

You call this routine as Call x(Me) or simply x Me

Another approach (less reliable) would be to refer to the current active
form:

Sub x()

Dim ctl As Control
Dim strName As String

For Each ctl In Screen.ActiveForm.Controls
MsgBox ctl.Name
Next ctl

End Sub

 

[Allen Browne]

Have the procedure receive an argument of type form, like this:

Function x(frm As Form)
...
For Each ctl in frm.Controls

You can then call it from any form like this:
Call x(Me)
or if you prefer:
Call x(Forms!Form1)

 

[Guest]

Hi Allen/Doug,

Thank you very much for your help to fix my problem. The code works great.

Thanks a lot.

Tim.

 

[David C. Holley]

Is the code in a FORM module or free standing? If its in a FORM module,
ME should work fine.

 

[Douglas J. Steele]

Tim's original question stated "I tried to create the same code in a module
which could be called by any form but in vain."

 

[David C. Holley]

thbbbt! on me, I missed that.