Module / subform null check problem

[Joel Wiseheart]

For reasons I won't go into, I need to check for required
entries at a form level, not the table level.

I use the following code, which sits in a module for
generic use, to check for required fields at the form
level. It works by putting the word "Required" in the Tag
property on the form controls that are required, and
passing the form name as a variable to the function.

It also uses the caption, not the field name, for the
error mesage displayed to the user (The LEN function, -1,
is used to remove the ":" character at the end of the
label).

The module code is:

Public Function RequiredFieldCheck(frm As Form) As
Integer
'Variables: frm = Form to check for blank fields on.

Dim ctl As Control
Dim str As String

For Each ctl In frm.Controls
If IsNull(ctl) And ctl.Tag = "Required" Then
RequiredFieldCheck = True

str = Left$(ctl.Controls(0).Caption, Len
(ctl.Controls(0).Caption) - 1)

MsgBox ("The required field '" & str & "'
was left blank.")
ctl.SetFocus
Exit For
End If
Next

End Function

And is called by this code on the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
'Purpose: If record update was not cancelled, check for
required fields left blank.
On Error GoTo Error_Form_BeforeUpdate

If fDelete = False Then
Cancel = RequiredFieldCheck(Me)
End If

Exit_Form_BeforeUpdate:
Exit Sub

Error_Form_BeforeUpdate:
Call ErrorHandlerCustom(Me.Name, "Form_BeforeUpdate")
Resume Exit_Form_BeforeUpdate

End Sub

It works great. That is, if the blank field is on the main
form.

I have been having problems using this same module, if
it's checking for blank fields on a subform. If I use the
Call statement on the subform, and either pass (Me) or
(Forms!formname!subformobject.Form) as the form variable,
I get an error message when it is opened as part of the
main form, saying the form can't be found.

Is this just a problem with the syntax of how I am
referencing the form name passed to the function, or is
there a problem with the function itself? I feel like I'm
missing something obvious here.

Thanks!

 

[Allen Browne]

Joel, the concept looks fine.

Presumably you have some error handling in place here, as not all controls
have a Value, so can't be tested for IsNull().

Do the contols in the subform actually have attached labels?
Labels in a continuous form are sometimes in the Form Header section, so
ctl.Controls(0) would not apply.

 

[Andy Cole]

Joel

Change your Public function declaration to contain frm As Object

Public Function RequiredFieldCheck(frm As Object) As Integer

Then you can pass Me as the parameter in both the Main Form and the SubForm

I'd also change the following;

If IsNull(ctl) And ctl.Tag = "Required" Then
RequiredFieldCheck = True
.......
End If

To;

If ctl.Tag = "Required" Then
If Nz(ctl, "") = "" Then
RequiredFieldCheck = True
.......
End If
End If

This will prevent an error should the subform contain a control that doesn't
have a value property (example: a button) and only bothers to check controls
with the "Required" Tag setting. The Nz() also checks for Null and/or empty
rather than Null only (example: a user adds and then removes it)

HTH

Andy

 

[Allen Browne]

Andy, how would changing from the tightly-typed "form" to the more generic
"object" help?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Joel

Change your Public function declaration to contain frm As Object

Public Function RequiredFieldCheck(frm As Object) As Integer

Then you can pass Me as the parameter in both the Main Form and the

SubForm

 

[Andy Cole]

Allen

I felt that relaxing the typing may help as Joel had stated that passing Me
(as the subform reference) resulted in an error stating that the Form could
not be found. This, in combination with the revised code I suggested for
Joel's code;

If IsNull(ctl) And ctl.Tag = "Required" Then

to;

If ctl.Tag = "Required" Then
If Nz(ctl, "") = "" Then

would give a better chance of the idea working for both Main and Sub forms
(in my view). I suspect that checking the Tag value first would probably
remove the error that Joel reported but, if it didn't, relaxing the type to
Object would give an error that would help in identifying the 'real' cause.
Its something I've done previously when using Delphi.

Andy

 

[Allen Browne]

Okay, that's the opposite of my experience in Access.

The more precisely you can type a variable, the more likely you are to avoid
errors.

 

[Joel Wiseheart]

Yes, they have attached labels. The thing is, if I use
this code in a form, it works fine. There is a series of
text boxes with attached labels, some of which are
required, some of which are not. I put "Required" only in
the text boxes of required entries. The reason it checks
the label is so it displays this to the user (using the
DefectCodeAlpha field as an example):

The required field 'Defect Code Alpha' was left blank.

Instead of the uglier-looking:

The required field 'DefectCodeAlpha' was left blank.

Since the label says 'Defect Code Alpha:', I usen a Len
function to remove the leftmost character (the ":") for
display to the user.

But that's not the problem...

The code seems to work fine on a main form, which leads me
to believe that all of the fuctions, label references and
For Each-Next loops are working fine.

The quirky part is that I pass (Me) as a variable in the
Call statement from the main form, and it works fine. If I
have the same Call statement in the subform, and pass (Me)
to the function, it tells me "Form not found", when the
subform is opened as part of the main form.

Then I tried passing it from the Forms collection, but I
get the same message. In this scenario, the Main form is
called f_MRRHeader and the subform is called f_MRRDisp. I
tried passing (Forms!f_MRRHeader!f_MRRDisp.Form) from the
Call statement, residing on the subform, but get the
same "Form not found" message (Including the ".Form"
property got rid of the 'Type mismatch' error).

Anyway, I'll try changing the variable to 'Object' instead
of 'Form', and see if it helps.

Subform addressing is such a pain...Grrrr....

Thanks for the suggestion,
Joel

 

[Joel Wiseheart]

Okay, I got it now....

The problem was that I am using the same subform on
different main forms. Since the forms collection example
was referencing a specific form name, it wouldn't work on
the other forms.

To fix it, I referenced the Parent property of the current
form as follows:


Cancel = RequiredFieldCheck(Forms!(Me.Parent)!
sf_MRRDisp.Form)

I knew it was something obvious....

Thanks for pointing me in the right direction, and for the
Nz check suggestion!

Joel

-----Original Message-----
Yes, they have attached labels. The thing is, if I use
this code in a form, it works fine. There is a series of
text boxes with attached labels, some of which are
required, some of which are not. I put "Required" only in
the text boxes of required entries. The reason it checks
the label is so it displays this to the user (using the
DefectCodeAlpha field as an example):

The required field 'Defect Code Alpha' was left blank.

Instead of the uglier-looking:

The required field 'DefectCodeAlpha' was left blank.

Since the label says 'Defect Code Alpha:', I usen a Len
function to remove the leftmost character (the ":") for
display to the user.

But that's not the problem...

The code seems to work fine on a main form, which leads me
to believe that all of the fuctions, label references and
For Each-Next loops are working fine.

The quirky part is that I pass (Me) as a variable in the
Call statement from the main form, and it works fine. If I
have the same Call statement in the subform, and pass (Me)
to the function, it tells me "Form not found", when the
subform is opened as part of the main form.

Then I tried passing it from the Forms collection, but I
get the same message. In this scenario, the Main form is
called f_MRRHeader and the subform is called f_MRRDisp. I
tried passing (Forms!f_MRRHeader!f_MRRDisp.Form) from the
Call statement, residing on the subform, but get the
same "Form not found" message (Including the ".Form"
property got rid of the 'Type mismatch' error).

Anyway, I'll try changing the variable to 'Object' instead
of 'Form', and see if it helps.

Subform addressing is such a pain...Grrrr....

Thanks for the suggestion,
Joel

-----Original Message-----
Joel, the concept looks fine.

Presumably you have some error handling in place here,

as
not all controls