Use of Me in function

[Dudley]

I am trying to set up error checking to run on exiting records in two
sub-forms and one sub-sub-form. In each case the checks and the control names
are identical. To save writing three different functions, I was hoping to
write one which would run on the before update event on each form. However, I
get error "Invalid use of Me Keyword" on code:

Public Function fnErrorCheckTest()
If Len(Trim(Nz(Me.Type1), "")) <> 0 _
And Len(Trim(Nz(Me.Surname), "")) = 0 Then
fnErrorCheckTest = False
MsgBox "Surname missing"
Exit Function
Else
fnErrorCheckTest = True

End If
End Function

Is there a way round this or do I need to write three functions with full
reference to each control?

Thanks for any help.
Dudley

 

[Jack Leach]

Me refers to the current form... it cannot be used outside a form module.
One other way to do this would be to use a form argument in the function...

Public Function fnErrorCheckTest(frm As Form)
If Len(Trim(Nz(frm.Type1), "")) <> 0 _
And Len(Trim(frm.Surname), "")) = 0 Then
fnErrorCheckTest = False
MsgBox "Surname Missing"
Else
fnErrorCheckTest = True
End If
End Function

and call it from your form's code like so...

fnErrorCheckTest Me


hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Dudley]

Thanks for your help. However I get a compile error, 'Wrong number of
arguments or invalid property assignment', with Trim highlighted.

Can you advise.
Thanks
Dudley

 

[Jack Leach]

frm.Surname

This actually refers to the control itself rather than the value of the
control, but Access will generally get the Value if it thinks that's what
you're looking for. Try this instead:

frm.Surname.Value

(trim only has one argument, so I don't think that's the problem)...

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Jeff Boyce]

For what it's worth, I believe "Me" also works within Report modules...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Dudley]

This did not help, but the problem seems to be the position of the brackets
as Len(Trim(Nz(frm.Type1.Value, ""))) <> 0 (moving the bracket after Type1 to
the end) does seem to work.

However, after giving the error message it exited from the record, and I
would prefer the focus to stay on it so that the user can correct the error.
Is this possible with BeforeUpdate?

Thanks for your help.
Dudley

 

[Dudley]

That works fine. Thanks again for your help.
Dudley