Validation of subform

[dsc2bjn]

I have created a form and wish to validate entries within the form and a
subform.

I have placed the form validation code as part of the "Close" button.

Example:
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New Entry)]![OCD]
is Null")) Then
MsgBox "A Value for 'Estimated Completion Date' is Required.", vbOKOnly,
"Required Value Missing"
intIsError = 1
End If


I now wish to validate entries on the subform. I tried calling the
"[subForm]", but get an error saying basically the method does not exist.

How can I validate the subform entries?

Also, I wish to spell check the form and subform. I can spell check the
form, but the subform is ignored.

Dim ctlSpell As Control
Dim frm As Form

Set frm = Screen.ActiveForm

DoCmd.SetWarnings False

For Each ctlSpell In frm.Controls

If TypeOf ctlSpell Is TextBox Then

If Len(ctlSpell) > 0 Then

With ctlSpell

'only spell check the text boxes with a value of true in
their tag property.
If .Tag = "True" Then

.SetFocus
.SelStart = 0
.SelLength = Len(ctlSpell)

DoCmd.RunCommand acCmdSpelling

End If

End With

End If

End If

Next


MsgBox "Spell check complete.", vbInformation + vbOKOnly, "Finished."
DoCmd.SetWarnings True
DoCmd.Close
End If

 

[Jeff Boyce]

If you are saying that you'd like Access to check on the record you are
entering in a subform, have you tried putting your validation code in that
subform (*actually, you'd put it in the form you are using as a subform*)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[dsc2bjn]

If the validation is taking place when a user clicks a button on the parent
form, how would putting the code on the subform be executed?

If you are saying that you'd like Access to check on the record you are
entering in a subform, have you tried putting your validation code in that
subform (*actually, you'd put it in the form you are using as a subform*)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have created a form and wish to validate entries within the form and a
subform.

I have placed the form validation code as part of the "Close" button.

Example:
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New Entry)]![OCD]
is Null")) Then
MsgBox "A Value for 'Estimated Completion Date' is Required.",
vbOKOnly,
"Required Value Missing"
intIsError = 1
End If


I now wish to validate entries on the subform. I tried calling the
"[subForm]", but get an error saying basically the method does not exist.

How can I validate the subform entries?

Also, I wish to spell check the form and subform. I can spell check the
form, but the subform is ignored.

Dim ctlSpell As Control
Dim frm As Form

Set frm = Screen.ActiveForm

DoCmd.SetWarnings False

For Each ctlSpell In frm.Controls

If TypeOf ctlSpell Is TextBox Then

If Len(ctlSpell) > 0 Then

With ctlSpell

'only spell check the text boxes with a value of true
in
their tag property.
If .Tag = "True" Then

.SetFocus
.SelStart = 0
.SelLength = Len(ctlSpell)

DoCmd.RunCommand acCmdSpelling

End If

End With

End If

End If

Next


MsgBox "Spell check complete.", vbInformation + vbOKOnly, "Finished."
DoCmd.SetWarnings True
DoCmd.Close
End If

 

[dsc2bjn]

I take it you are saying that I should have some over event (On Lost Focus)
trigger the validation of the subform and only validate the parent form when
they click the button to exit the record.

You have to realize that each is a separate action. If you move from the
main form to the subform, changes on the main form are saved first and then
you are on the subform. Similarly when you move back to the main form from
the subform, your subform changes are saved before you are actually on the
main form again.

If you validate subform entries on the main form then you can't cancel the
updates of the subform as they have already happened. If the validation
failed then you would need to remove the subform entry with a delete query.

--

Thanks,

Bob Larson
Access MVP
Administrator, Access World Forums
Utter Access VIP

Free Access Tutorials and Resources: http://www.btabdevelopment.com

If the validation is taking place when a user clicks a button on the
parent
form, how would putting the code on the subform be executed?

If you are saying that you'd like Access to check on the record you are
entering in a subform, have you tried putting your validation code in
that
subform (*actually, you'd put it in the form you are using as a
subform*)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have created a form and wish to validate entries within the form and a
subform.

I have placed the form validation code as part of the "Close" button.

Example:
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![OCD]
is Null")) Then
MsgBox "A Value for 'Estimated Completion Date' is Required.",
vbOKOnly,
"Required Value Missing"
intIsError = 1
End If


I now wish to validate entries on the subform. I tried calling the
"[subForm]", but get an error saying basically the method does not
exist.

How can I validate the subform entries?

Also, I wish to spell check the form and subform. I can spell check
the
form, but the subform is ignored.

Dim ctlSpell As Control
Dim frm As Form

Set frm = Screen.ActiveForm

DoCmd.SetWarnings False

For Each ctlSpell In frm.Controls

If TypeOf ctlSpell Is TextBox Then

If Len(ctlSpell) > 0 Then

With ctlSpell

'only spell check the text boxes with a value of
true
in
their tag property.
If .Tag = "True" Then

.SetFocus
.SelStart = 0
.SelLength = Len(ctlSpell)

DoCmd.RunCommand acCmdSpelling

End If

End With

End If

End If

Next


MsgBox "Spell check complete.", vbInformation + vbOKOnly,
"Finished."
DoCmd.SetWarnings True
DoCmd.Close
End If

 

[Jeff Boyce]

If you are using a standard main form/subform construction, there is NO
activity in the form that is your subform UNTIL it gets the focus. Why
would you be clicking a button in the main form when its data entered in the
subform that needs validation?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

If the validation is taking place when a user clicks a button on the
parent
form, how would putting the code on the subform be executed?

If you are saying that you'd like Access to check on the record you are
entering in a subform, have you tried putting your validation code in
that
subform (*actually, you'd put it in the form you are using as a
subform*)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have created a form and wish to validate entries within the form and a
subform.

I have placed the form validation code as part of the "Close" button.

Example:
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![OCD]
is Null")) Then
MsgBox "A Value for 'Estimated Completion Date' is Required.",
vbOKOnly,
"Required Value Missing"
intIsError = 1
End If


I now wish to validate entries on the subform. I tried calling the
"[subForm]", but get an error saying basically the method does not
exist.

How can I validate the subform entries?

Also, I wish to spell check the form and subform. I can spell check
the
form, but the subform is ignored.

Dim ctlSpell As Control
Dim frm As Form

Set frm = Screen.ActiveForm

DoCmd.SetWarnings False

For Each ctlSpell In frm.Controls

If TypeOf ctlSpell Is TextBox Then

If Len(ctlSpell) > 0 Then

With ctlSpell

'only spell check the text boxes with a value of
true
in
their tag property.
If .Tag = "True" Then

.SetFocus
.SelStart = 0
.SelLength = Len(ctlSpell)

DoCmd.RunCommand acCmdSpelling

End If

End With

End If

End If

Next


MsgBox "Spell check complete.", vbInformation + vbOKOnly,
"Finished."
DoCmd.SetWarnings True
DoCmd.Close
End If

 

[dsc2bjn]

There is data in both the form and subform which must be validated before a
record is considered complete enough to be entered into the database.

I had this as two separate input forms, but the client didn't like that
approach.

If you are using a standard main form/subform construction, there is NO
activity in the form that is your subform UNTIL it gets the focus. Why
would you be clicking a button in the main form when its data entered in the
subform that needs validation?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

If the validation is taking place when a user clicks a button on the
parent
form, how would putting the code on the subform be executed?

If you are saying that you'd like Access to check on the record you are
entering in a subform, have you tried putting your validation code in
that
subform (*actually, you'd put it in the form you are using as a
subform*)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have created a form and wish to validate entries within the form and a
subform.

I have placed the form validation code as part of the "Close" button.

Example:
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![OCD]
is Null")) Then
MsgBox "A Value for 'Estimated Completion Date' is Required.",
vbOKOnly,
"Required Value Missing"
intIsError = 1
End If


I now wish to validate entries on the subform. I tried calling the
"[subForm]", but get an error saying basically the method does not
exist.

How can I validate the subform entries?

Also, I wish to spell check the form and subform. I can spell check
the
form, but the subform is ignored.

Dim ctlSpell As Control
Dim frm As Form

Set frm = Screen.ActiveForm

DoCmd.SetWarnings False

For Each ctlSpell In frm.Controls

If TypeOf ctlSpell Is TextBox Then

If Len(ctlSpell) > 0 Then

With ctlSpell

'only spell check the text boxes with a value of
true
in
their tag property.
If .Tag = "True" Then

.SetFocus
.SelStart = 0
.SelLength = Len(ctlSpell)

DoCmd.RunCommand acCmdSpelling

End If

End With

End If

End If

Next


MsgBox "Spell check complete.", vbInformation + vbOKOnly,
"Finished."
DoCmd.SetWarnings True
DoCmd.Close
End If

 

[Jeff Boyce]

As I mentioned, standard Access main form/subform construction requires a
record in the main form before a subform record can be entered. I still
don't have enough information to tell if your main form/subform is standard.

One advantage to using the standard approach is that you can validate the
main form's record (i.e., the "one" side of a one-to-many relationship)
before attempting to add a subform record (the "many" side). Of course,
this assumes that your data is well-normalized and organized in a pair of
tables related one-to-many. Is this a fair assumption?

(still) More information, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

There is data in both the form and subform which must be validated before
a
record is considered complete enough to be entered into the database.

I had this as two separate input forms, but the client didn't like that
approach.

If you are using a standard main form/subform construction, there is NO
activity in the form that is your subform UNTIL it gets the focus. Why
would you be clicking a button in the main form when its data entered in
the
subform that needs validation?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

If the validation is taking place when a user clicks a button on the
parent
form, how would putting the code on the subform be executed?

:

If you are saying that you'd like Access to check on the record you
are
entering in a subform, have you tried putting your validation code in
that
subform (*actually, you'd put it in the form you are using as a
subform*)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have created a form and wish to validate entries within the form
and a
subform.

I have placed the form validation code as part of the "Close"
button.

Example:
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![OCD]
is Null")) Then
MsgBox "A Value for 'Estimated Completion Date' is Required.",
vbOKOnly,
"Required Value Missing"
intIsError = 1
End If


I now wish to validate entries on the subform. I tried calling the
"[subForm]", but get an error saying basically the method does not
exist.

How can I validate the subform entries?

Also, I wish to spell check the form and subform. I can spell check
the
form, but the subform is ignored.

Dim ctlSpell As Control
Dim frm As Form

Set frm = Screen.ActiveForm

DoCmd.SetWarnings False

For Each ctlSpell In frm.Controls

If TypeOf ctlSpell Is TextBox Then

If Len(ctlSpell) > 0 Then

With ctlSpell

'only spell check the text boxes with a value of
true
in
their tag property.
If .Tag = "True" Then

.SetFocus
.SelStart = 0
.SelLength = Len(ctlSpell)

DoCmd.RunCommand acCmdSpelling

End If

End With

End If

End If

Next


MsgBox "Spell check complete.", vbInformation + vbOKOnly,
"Finished."
DoCmd.SetWarnings True
DoCmd.Close
End If

 

[dsc2bjn]

The main form contains general information related to each record (the one).
The subform contains information at a lower level (the many).

The main form must contain items such as routing number, title, and source
to be considered a record. If any of these are Null, I want the user to
enter a value before they are allowed to save the data to the database and
close the form.

The subform must contain items related to the "steps" taken such as action
plan and estimated completion date. If these are Null, I want the user to
enter a value before they are allowed to save the data to the database and
close the subform.

My thought was to have the entire form/subform validated, when the user
clicks a button (Save & Close). Right now only the parent is validated.


I tried adding validation and referencing the subform within the parent, but
I get a messaging saying it can't find the form (subform).

If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![Deficiency Source] = 'SCAMPI' and [Forms]![frmOther Weakness
Corrective Actions (New Entry)]![ProcessAreas] is Null")) Then
MsgBox "A Value for 'Process Area' is Required.", vbOKOnly, "Required
Value Missing"
intIsError = 1
End If
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New Entry)]![OCD]
is Null")) Then
MsgBox "A Value for the weakness ' Estimated Completion Date' is
Required.", vbOKOnly, "Required Value Missing"
intIsError = 1
End If
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![Status] is Null")) Then
MsgBox "A Value for 'Status' is Required.", vbOKOnly, "Required Value
Missing"
intIsError = 1
End If
If (Eval("[Forms]![Other Corrective Action Plan
subform1]![CorrectiveActionPlanItem] is Not Null and [Forms]![Other
Corrective Action Plan subform1]![OCD] is Null")) Then
MsgBox "A Value for 'Corrective Action Plan Estimated Completion Date.",
vbOKOnly, "Required Value Missing"
intIsError = 1
End If

As I mentioned, standard Access main form/subform construction requires a
record in the main form before a subform record can be entered. I still
don't have enough information to tell if your main form/subform is standard.

One advantage to using the standard approach is that you can validate the
main form's record (i.e., the "one" side of a one-to-many relationship)
before attempting to add a subform record (the "many" side). Of course,
this assumes that your data is well-normalized and organized in a pair of
tables related one-to-many. Is this a fair assumption?

(still) More information, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

There is data in both the form and subform which must be validated before
a
record is considered complete enough to be entered into the database.

I had this as two separate input forms, but the client didn't like that
approach.

If you are using a standard main form/subform construction, there is NO
activity in the form that is your subform UNTIL it gets the focus. Why
would you be clicking a button in the main form when its data entered in
the
subform that needs validation?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

If the validation is taking place when a user clicks a button on the
parent
form, how would putting the code on the subform be executed?

:

If you are saying that you'd like Access to check on the record you
are
entering in a subform, have you tried putting your validation code in
that
subform (*actually, you'd put it in the form you are using as a
subform*)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have created a form and wish to validate entries within the form
and a
subform.

I have placed the form validation code as part of the "Close"
button.

Example:
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![OCD]
is Null")) Then
MsgBox "A Value for 'Estimated Completion Date' is Required.",
vbOKOnly,
"Required Value Missing"
intIsError = 1
End If


I now wish to validate entries on the subform. I tried calling the
"[subForm]", but get an error saying basically the method does not
exist.

How can I validate the subform entries?

Also, I wish to spell check the form and subform. I can spell check
the
form, but the subform is ignored.

Dim ctlSpell As Control
Dim frm As Form

Set frm = Screen.ActiveForm

DoCmd.SetWarnings False

For Each ctlSpell In frm.Controls

If TypeOf ctlSpell Is TextBox Then

If Len(ctlSpell) > 0 Then

With ctlSpell

'only spell check the text boxes with a value of
true
in
their tag property.
If .Tag = "True" Then

.SetFocus
.SelStart = 0
.SelLength = Len(ctlSpell)

DoCmd.RunCommand acCmdSpelling

End If

End With

End If

End If

Next


MsgBox "Spell check complete.", vbInformation + vbOKOnly,
"Finished."
DoCmd.SetWarnings True
DoCmd.Close
End If

 

[Jeff Boyce]

You mention trying to do the validation from the parent form. It seems
you've already decided "how".

Take a look at "expressions" in Access HELP for a way to refer to controls
on a subform.

(and I still believe you'd have more luck if you'd set up the validation of
the subform data IN the subform itself).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

The main form contains general information related to each record (the
one).
The subform contains information at a lower level (the many).

The main form must contain items such as routing number, title, and source
to be considered a record. If any of these are Null, I want the user to
enter a value before they are allowed to save the data to the database and
close the form.

The subform must contain items related to the "steps" taken such as action
plan and estimated completion date. If these are Null, I want the user to
enter a value before they are allowed to save the data to the database and
close the subform.

My thought was to have the entire form/subform validated, when the user
clicks a button (Save & Close). Right now only the parent is validated.


I tried adding validation and referencing the subform within the parent,
but
I get a messaging saying it can't find the form (subform).

If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![Deficiency Source] = 'SCAMPI' and [Forms]![frmOther Weakness
Corrective Actions (New Entry)]![ProcessAreas] is Null")) Then
MsgBox "A Value for 'Process Area' is Required.", vbOKOnly, "Required
Value Missing"
intIsError = 1
End If
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New Entry)]![OCD]
is Null")) Then
MsgBox "A Value for the weakness ' Estimated Completion Date' is
Required.", vbOKOnly, "Required Value Missing"
intIsError = 1
End If
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![Status] is Null")) Then
MsgBox "A Value for 'Status' is Required.", vbOKOnly, "Required Value
Missing"
intIsError = 1
End If
If (Eval("[Forms]![Other Corrective Action Plan
subform1]![CorrectiveActionPlanItem] is Not Null and [Forms]![Other
Corrective Action Plan subform1]![OCD] is Null")) Then
MsgBox "A Value for 'Corrective Action Plan Estimated Completion
Date.",
vbOKOnly, "Required Value Missing"
intIsError = 1
End If

As I mentioned, standard Access main form/subform construction requires a
record in the main form before a subform record can be entered. I still
don't have enough information to tell if your main form/subform is
standard.

One advantage to using the standard approach is that you can validate the
main form's record (i.e., the "one" side of a one-to-many relationship)
before attempting to add a subform record (the "many" side). Of course,
this assumes that your data is well-normalized and organized in a pair of
tables related one-to-many. Is this a fair assumption?

(still) More information, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

There is data in both the form and subform which must be validated
before
a
record is considered complete enough to be entered into the database.

I had this as two separate input forms, but the client didn't like that
approach.


:

If you are using a standard main form/subform construction, there is
NO
activity in the form that is your subform UNTIL it gets the focus.
Why
would you be clicking a button in the main form when its data entered
in
the
subform that needs validation?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

If the validation is taking place when a user clicks a button on the
parent
form, how would putting the code on the subform be executed?

:

If you are saying that you'd like Access to check on the record you
are
entering in a subform, have you tried putting your validation code
in
that
subform (*actually, you'd put it in the form you are using as a
subform*)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have created a form and wish to validate entries within the form
and a
subform.

I have placed the form validation code as part of the "Close"
button.

Example:
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![OCD]
is Null")) Then
MsgBox "A Value for 'Estimated Completion Date' is Required.",
vbOKOnly,
"Required Value Missing"
intIsError = 1
End If


I now wish to validate entries on the subform. I tried calling
the
"[subForm]", but get an error saying basically the method does
not
exist.

How can I validate the subform entries?

Also, I wish to spell check the form and subform. I can spell
check
the
form, but the subform is ignored.

Dim ctlSpell As Control
Dim frm As Form

Set frm = Screen.ActiveForm

DoCmd.SetWarnings False

For Each ctlSpell In frm.Controls

If TypeOf ctlSpell Is TextBox Then

If Len(ctlSpell) > 0 Then

With ctlSpell

'only spell check the text boxes with a value
of
true
in
their tag property.
If .Tag = "True" Then

.SetFocus
.SelStart = 0
.SelLength = Len(ctlSpell)

DoCmd.RunCommand acCmdSpelling

End If

End With

End If

End If

Next


MsgBox "Spell check complete.", vbInformation + vbOKOnly,
"Finished."
DoCmd.SetWarnings True
DoCmd.Close
End If

 

[dsc2bjn]

I tried setting up the validation within the subform without any luck.
Perhaps my approach was wrong.

I tried using the On Close, the On Lost Focus, Got Focus, and the After
Update for a specific control to trigger the validation of the subform.

Private Sub Form_GotFocus()
If (Eval("[Forms]![Other Corrective Action Plan subform1]![OCD] Is Null"))
Then
MsgBox "A Value for 'Corrective Action Estimated Completion Date' is
Required.", vbOKOnly, "Required Value Missing"

End Sub

You mention trying to do the validation from the parent form. It seems
you've already decided "how".

Take a look at "expressions" in Access HELP for a way to refer to controls
on a subform.

(and I still believe you'd have more luck if you'd set up the validation of
the subform data IN the subform itself).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

The main form contains general information related to each record (the
one).
The subform contains information at a lower level (the many).

The main form must contain items such as routing number, title, and source
to be considered a record. If any of these are Null, I want the user to
enter a value before they are allowed to save the data to the database and
close the form.

The subform must contain items related to the "steps" taken such as action
plan and estimated completion date. If these are Null, I want the user to
enter a value before they are allowed to save the data to the database and
close the subform.

My thought was to have the entire form/subform validated, when the user
clicks a button (Save & Close). Right now only the parent is validated.


I tried adding validation and referencing the subform within the parent,
but
I get a messaging saying it can't find the form (subform).

If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![Deficiency Source] = 'SCAMPI' and [Forms]![frmOther Weakness
Corrective Actions (New Entry)]![ProcessAreas] is Null")) Then
MsgBox "A Value for 'Process Area' is Required.", vbOKOnly, "Required
Value Missing"
intIsError = 1
End If
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New Entry)]![OCD]
is Null")) Then
MsgBox "A Value for the weakness ' Estimated Completion Date' is
Required.", vbOKOnly, "Required Value Missing"
intIsError = 1
End If
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![Status] is Null")) Then
MsgBox "A Value for 'Status' is Required.", vbOKOnly, "Required Value
Missing"
intIsError = 1
End If
If (Eval("[Forms]![Other Corrective Action Plan
subform1]![CorrectiveActionPlanItem] is Not Null and [Forms]![Other
Corrective Action Plan subform1]![OCD] is Null")) Then
MsgBox "A Value for 'Corrective Action Plan Estimated Completion
Date.",
vbOKOnly, "Required Value Missing"
intIsError = 1
End If

As I mentioned, standard Access main form/subform construction requires a
record in the main form before a subform record can be entered. I still
don't have enough information to tell if your main form/subform is
standard.

One advantage to using the standard approach is that you can validate the
main form's record (i.e., the "one" side of a one-to-many relationship)
before attempting to add a subform record (the "many" side). Of course,
this assumes that your data is well-normalized and organized in a pair of
tables related one-to-many. Is this a fair assumption?

(still) More information, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

There is data in both the form and subform which must be validated
before
a
record is considered complete enough to be entered into the database.

I had this as two separate input forms, but the client didn't like that
approach.


:

If you are using a standard main form/subform construction, there is
NO
activity in the form that is your subform UNTIL it gets the focus.
Why
would you be clicking a button in the main form when its data entered
in
the
subform that needs validation?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

If the validation is taking place when a user clicks a button on the
parent
form, how would putting the code on the subform be executed?

:

If you are saying that you'd like Access to check on the record you
are
entering in a subform, have you tried putting your validation code
in
that
subform (*actually, you'd put it in the form you are using as a
subform*)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have created a form and wish to validate entries within the form
and a
subform.

I have placed the form validation code as part of the "Close"
button.

Example:
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![OCD]
is Null")) Then
MsgBox "A Value for 'Estimated Completion Date' is Required.",
vbOKOnly,
"Required Value Missing"
intIsError = 1
End If


I now wish to validate entries on the subform. I tried calling
the
"[subForm]", but get an error saying basically the method does
not
exist.

How can I validate the subform entries?

Also, I wish to spell check the form and subform. I can spell
check
the
form, but the subform is ignored.

Dim ctlSpell As Control
Dim frm As Form

Set frm = Screen.ActiveForm

DoCmd.SetWarnings False

For Each ctlSpell In frm.Controls

If TypeOf ctlSpell Is TextBox Then

If Len(ctlSpell) > 0 Then

With ctlSpell

'only spell check the text boxes with a value
of
true
in
their tag property.
If .Tag = "True" Then

.SetFocus
.SelStart = 0
.SelLength = Len(ctlSpell)

DoCmd.RunCommand acCmdSpelling

End If

End With

End If

End If

Next


MsgBox "Spell check complete.", vbInformation + vbOKOnly,
"Finished."
DoCmd.SetWarnings True
DoCmd.Close
End If

 

[dsc2bjn]

If I open the subform by itself and use the following code, it works fine.
Private Sub Form_AfterUpdate()
If (Eval("[Forms]![Other Corrective Action Plan subform1]![OCD] Is Null"))
Then
MsgBox "A Value for 'Corrective Action Estimated Completion Date' is
Required.", vbOKOnly, "Required Value Missing"
End If
End Sub

The same code executed within the main form returns an error stating Access
can't find the form Other Corrective Action Plan subform1.

You mention trying to do the validation from the parent form. It seems
you've already decided "how".

Take a look at "expressions" in Access HELP for a way to refer to controls
on a subform.

(and I still believe you'd have more luck if you'd set up the validation of
the subform data IN the subform itself).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

The main form contains general information related to each record (the
one).
The subform contains information at a lower level (the many).

The main form must contain items such as routing number, title, and source
to be considered a record. If any of these are Null, I want the user to
enter a value before they are allowed to save the data to the database and
close the form.

The subform must contain items related to the "steps" taken such as action
plan and estimated completion date. If these are Null, I want the user to
enter a value before they are allowed to save the data to the database and
close the subform.

My thought was to have the entire form/subform validated, when the user
clicks a button (Save & Close). Right now only the parent is validated.


I tried adding validation and referencing the subform within the parent,
but
I get a messaging saying it can't find the form (subform).

If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![Deficiency Source] = 'SCAMPI' and [Forms]![frmOther Weakness
Corrective Actions (New Entry)]![ProcessAreas] is Null")) Then
MsgBox "A Value for 'Process Area' is Required.", vbOKOnly, "Required
Value Missing"
intIsError = 1
End If
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New Entry)]![OCD]
is Null")) Then
MsgBox "A Value for the weakness ' Estimated Completion Date' is
Required.", vbOKOnly, "Required Value Missing"
intIsError = 1
End If
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![Status] is Null")) Then
MsgBox "A Value for 'Status' is Required.", vbOKOnly, "Required Value
Missing"
intIsError = 1
End If
If (Eval("[Forms]![Other Corrective Action Plan
subform1]![CorrectiveActionPlanItem] is Not Null and [Forms]![Other
Corrective Action Plan subform1]![OCD] is Null")) Then
MsgBox "A Value for 'Corrective Action Plan Estimated Completion
Date.",
vbOKOnly, "Required Value Missing"
intIsError = 1
End If

As I mentioned, standard Access main form/subform construction requires a
record in the main form before a subform record can be entered. I still
don't have enough information to tell if your main form/subform is
standard.

One advantage to using the standard approach is that you can validate the
main form's record (i.e., the "one" side of a one-to-many relationship)
before attempting to add a subform record (the "many" side). Of course,
this assumes that your data is well-normalized and organized in a pair of
tables related one-to-many. Is this a fair assumption?

(still) More information, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

There is data in both the form and subform which must be validated
before
a
record is considered complete enough to be entered into the database.

I had this as two separate input forms, but the client didn't like that
approach.


:

If you are using a standard main form/subform construction, there is
NO
activity in the form that is your subform UNTIL it gets the focus.
Why
would you be clicking a button in the main form when its data entered
in
the
subform that needs validation?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

If the validation is taking place when a user clicks a button on the
parent
form, how would putting the code on the subform be executed?

:

If you are saying that you'd like Access to check on the record you
are
entering in a subform, have you tried putting your validation code
in
that
subform (*actually, you'd put it in the form you are using as a
subform*)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have created a form and wish to validate entries within the form
and a
subform.

I have placed the form validation code as part of the "Close"
button.

Example:
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![OCD]
is Null")) Then
MsgBox "A Value for 'Estimated Completion Date' is Required.",
vbOKOnly,
"Required Value Missing"
intIsError = 1
End If


I now wish to validate entries on the subform. I tried calling
the
"[subForm]", but get an error saying basically the method does
not
exist.

How can I validate the subform entries?

Also, I wish to spell check the form and subform. I can spell
check
the
form, but the subform is ignored.

Dim ctlSpell As Control
Dim frm As Form

Set frm = Screen.ActiveForm

DoCmd.SetWarnings False

For Each ctlSpell In frm.Controls

If TypeOf ctlSpell Is TextBox Then

If Len(ctlSpell) > 0 Then

With ctlSpell

'only spell check the text boxes with a value
of
true
in
their tag property.
If .Tag = "True" Then

.SetFocus
.SelStart = 0
.SelLength = Len(ctlSpell)

DoCmd.RunCommand acCmdSpelling

End If

End With

End If

End If

Next


MsgBox "Spell check complete.", vbInformation + vbOKOnly,
"Finished."
DoCmd.SetWarnings True
DoCmd.Close
End If

 

[Jeff Boyce]

The reason why it works when you use the subform itself, but doesn't when
you use the subform embedded in the main form is probably because it is
embedded in the main form!

The path Access needs to follow to find the control is through the main form
to the subform control and into the subform's controls. The Expression
(check Access HELP) might look something like:

Forms!YourMainForm!YourSubformControl.Form!YourSubform'sControl'sName

Regards

Jeff Boyce
Microsoft Office/Access MVP

If I open the subform by itself and use the following code, it works fine.
Private Sub Form_AfterUpdate()
If (Eval("[Forms]![Other Corrective Action Plan subform1]![OCD] Is Null"))
Then
MsgBox "A Value for 'Corrective Action Estimated Completion Date' is
Required.", vbOKOnly, "Required Value Missing"
End If
End Sub

The same code executed within the main form returns an error stating
Access
can't find the form Other Corrective Action Plan subform1.

You mention trying to do the validation from the parent form. It seems
you've already decided "how".

Take a look at "expressions" in Access HELP for a way to refer to
controls
on a subform.

(and I still believe you'd have more luck if you'd set up the validation
of
the subform data IN the subform itself).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

The main form contains general information related to each record (the
one).
The subform contains information at a lower level (the many).

The main form must contain items such as routing number, title, and
source
to be considered a record. If any of these are Null, I want the user
to
enter a value before they are allowed to save the data to the database
and
close the form.

The subform must contain items related to the "steps" taken such as
action
plan and estimated completion date. If these are Null, I want the user
to
enter a value before they are allowed to save the data to the database
and
close the subform.

My thought was to have the entire form/subform validated, when the user
clicks a button (Save & Close). Right now only the parent is
validated.


I tried adding validation and referencing the subform within the
parent,
but
I get a messaging saying it can't find the form (subform).

If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![Deficiency Source] = 'SCAMPI' and [Forms]![frmOther Weakness
Corrective Actions (New Entry)]![ProcessAreas] is Null")) Then
MsgBox "A Value for 'Process Area' is Required.", vbOKOnly,
"Required
Value Missing"
intIsError = 1
End If
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![OCD]
is Null")) Then
MsgBox "A Value for the weakness ' Estimated Completion Date' is
Required.", vbOKOnly, "Required Value Missing"
intIsError = 1
End If
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![Status] is Null")) Then
MsgBox "A Value for 'Status' is Required.", vbOKOnly, "Required
Value
Missing"
intIsError = 1
End If
If (Eval("[Forms]![Other Corrective Action Plan
subform1]![CorrectiveActionPlanItem] is Not Null and [Forms]![Other
Corrective Action Plan subform1]![OCD] is Null")) Then
MsgBox "A Value for 'Corrective Action Plan Estimated Completion
Date.",
vbOKOnly, "Required Value Missing"
intIsError = 1
End If


:

As I mentioned, standard Access main form/subform construction
requires a
record in the main form before a subform record can be entered. I
still
don't have enough information to tell if your main form/subform is
standard.

One advantage to using the standard approach is that you can validate
the
main form's record (i.e., the "one" side of a one-to-many
relationship)
before attempting to add a subform record (the "many" side). Of
course,
this assumes that your data is well-normalized and organized in a pair
of
tables related one-to-many. Is this a fair assumption?

(still) More information, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

There is data in both the form and subform which must be validated
before
a
record is considered complete enough to be entered into the
database.

I had this as two separate input forms, but the client didn't like
that
approach.


:

If you are using a standard main form/subform construction, there
is
NO
activity in the form that is your subform UNTIL it gets the focus.
Why
would you be clicking a button in the main form when its data
entered
in
the
subform that needs validation?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

If the validation is taking place when a user clicks a button on
the
parent
form, how would putting the code on the subform be executed?

:

If you are saying that you'd like Access to check on the record
you
are
entering in a subform, have you tried putting your validation
code
in
that
subform (*actually, you'd put it in the form you are using as a
subform*)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have created a form and wish to validate entries within the
form
and a
subform.

I have placed the form validation code as part of the "Close"
button.

Example:
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![OCD]
is Null")) Then
MsgBox "A Value for 'Estimated Completion Date' is
Required.",
vbOKOnly,
"Required Value Missing"
intIsError = 1
End If


I now wish to validate entries on the subform. I tried
calling
the
"[subForm]", but get an error saying basically the method does
not
exist.

How can I validate the subform entries?

Also, I wish to spell check the form and subform. I can spell
check
the
form, but the subform is ignored.

Dim ctlSpell As Control
Dim frm As Form

Set frm = Screen.ActiveForm

DoCmd.SetWarnings False

For Each ctlSpell In frm.Controls

If TypeOf ctlSpell Is TextBox Then

If Len(ctlSpell) > 0 Then

With ctlSpell

'only spell check the text boxes with a
value
of
true
in
their tag property.
If .Tag = "True" Then

.SetFocus
.SelStart = 0
.SelLength = Len(ctlSpell)

DoCmd.RunCommand acCmdSpelling

End If

End With

End If

End If

Next


MsgBox "Spell check complete.", vbInformation + vbOKOnly,
"Finished."
DoCmd.SetWarnings True
DoCmd.Close
End If

 

[dsc2bjn]

Thanks!

That helps, but still have some issues.

If the validation check is on the subform, I need an event or something to
trigger the check.

I placed the code in the AfterUpdate() event, but that validates each record
as they are entered. Normal this would be ok. The problem is that the spell
check runs for each child record as it is entered. And the spell check runs
again on the parent form for the other data at the time the form is closed.

It can be a little a little repetative for the user.

I would really like the spell check to only run once for the entire form
(form and subform).

Any ideas?

The reason why it works when you use the subform itself, but doesn't when
you use the subform embedded in the main form is probably because it is
embedded in the main form!

The path Access needs to follow to find the control is through the main form
to the subform control and into the subform's controls. The Expression
(check Access HELP) might look something like:

Forms!YourMainForm!YourSubformControl.Form!YourSubform'sControl'sName

Regards

Jeff Boyce
Microsoft Office/Access MVP

If I open the subform by itself and use the following code, it works fine.
Private Sub Form_AfterUpdate()
If (Eval("[Forms]![Other Corrective Action Plan subform1]![OCD] Is Null"))
Then
MsgBox "A Value for 'Corrective Action Estimated Completion Date' is
Required.", vbOKOnly, "Required Value Missing"
End If
End Sub

The same code executed within the main form returns an error stating
Access
can't find the form Other Corrective Action Plan subform1.

You mention trying to do the validation from the parent form. It seems
you've already decided "how".

Take a look at "expressions" in Access HELP for a way to refer to
controls
on a subform.

(and I still believe you'd have more luck if you'd set up the validation
of
the subform data IN the subform itself).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

The main form contains general information related to each record (the
one).
The subform contains information at a lower level (the many).

The main form must contain items such as routing number, title, and
source
to be considered a record. If any of these are Null, I want the user
to
enter a value before they are allowed to save the data to the database
and
close the form.

The subform must contain items related to the "steps" taken such as
action
plan and estimated completion date. If these are Null, I want the user
to
enter a value before they are allowed to save the data to the database
and
close the subform.

My thought was to have the entire form/subform validated, when the user
clicks a button (Save & Close). Right now only the parent is
validated.


I tried adding validation and referencing the subform within the
parent,
but
I get a messaging saying it can't find the form (subform).

If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![Deficiency Source] = 'SCAMPI' and [Forms]![frmOther Weakness
Corrective Actions (New Entry)]![ProcessAreas] is Null")) Then
MsgBox "A Value for 'Process Area' is Required.", vbOKOnly,
"Required
Value Missing"
intIsError = 1
End If
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![OCD]
is Null")) Then
MsgBox "A Value for the weakness ' Estimated Completion Date' is
Required.", vbOKOnly, "Required Value Missing"
intIsError = 1
End If
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![Status] is Null")) Then
MsgBox "A Value for 'Status' is Required.", vbOKOnly, "Required
Value
Missing"
intIsError = 1
End If
If (Eval("[Forms]![Other Corrective Action Plan
subform1]![CorrectiveActionPlanItem] is Not Null and [Forms]![Other
Corrective Action Plan subform1]![OCD] is Null")) Then
MsgBox "A Value for 'Corrective Action Plan Estimated Completion
Date.",
vbOKOnly, "Required Value Missing"
intIsError = 1
End If


:

As I mentioned, standard Access main form/subform construction
requires a
record in the main form before a subform record can be entered. I
still
don't have enough information to tell if your main form/subform is
standard.

One advantage to using the standard approach is that you can validate
the
main form's record (i.e., the "one" side of a one-to-many
relationship)
before attempting to add a subform record (the "many" side). Of
course,
this assumes that your data is well-normalized and organized in a pair
of
tables related one-to-many. Is this a fair assumption?

(still) More information, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

There is data in both the form and subform which must be validated
before
a
record is considered complete enough to be entered into the
database.

I had this as two separate input forms, but the client didn't like
that
approach.


:

If you are using a standard main form/subform construction, there
is
NO
activity in the form that is your subform UNTIL it gets the focus.
Why
would you be clicking a button in the main form when its data
entered
in
the
subform that needs validation?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

If the validation is taking place when a user clicks a button on
the
parent
form, how would putting the code on the subform be executed?

:

If you are saying that you'd like Access to check on the record
you
are
entering in a subform, have you tried putting your validation
code
in
that
subform (*actually, you'd put it in the form you are using as a
subform*)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have created a form and wish to validate entries within the
form
and a
subform.

I have placed the form validation code as part of the "Close"
button.

Example:
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![OCD]
is Null")) Then
MsgBox "A Value for 'Estimated Completion Date' is
Required.",
vbOKOnly,
"Required Value Missing"
intIsError = 1
End If


I now wish to validate entries on the subform. I tried
calling
the
"[subForm]", but get an error saying basically the method does
not
exist.

How can I validate the subform entries?

Also, I wish to spell check the form and subform. I can spell
check
the
form, but the subform is ignored.

Dim ctlSpell As Control
Dim frm As Form

Set frm = Screen.ActiveForm

DoCmd.SetWarnings False

For Each ctlSpell In frm.Controls

If TypeOf ctlSpell Is TextBox Then

If Len(ctlSpell) > 0 Then

With ctlSpell

'only spell check the text boxes with a
value
of
true
in
their tag property.
If .Tag = "True" Then

.SetFocus
.SelStart = 0
.SelLength = Len(ctlSpell)

DoCmd.RunCommand acCmdSpelling

End If

End With

End If

End If

Next


MsgBox "Spell check complete.", vbInformation + vbOKOnly,
"Finished."
DoCmd.SetWarnings True
DoCmd.Close
End If

 

[Jeff Boyce]

The notion of validation seems to be ... fractured!

If you don't want folks to enter bad data, you create validation routines
that help them do it the right way.

If you want folks to enter whatever they want, then run a routine that finds
all the mistakes, how did you plan to let the user know that the third field
on the second record in the subform is incorrect?

Actually, ideally, if there was a way to prevent the user from making any
mistake in the first place, that seems optimal.

JOPO (just one person's opinions) ... you did ask for my thoughts on this...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks!

That helps, but still have some issues.

If the validation check is on the subform, I need an event or something to
trigger the check.

I placed the code in the AfterUpdate() event, but that validates each
record
as they are entered. Normal this would be ok. The problem is that the
spell
check runs for each child record as it is entered. And the spell check
runs
again on the parent form for the other data at the time the form is
closed.

It can be a little a little repetative for the user.

I would really like the spell check to only run once for the entire form
(form and subform).

Any ideas?

The reason why it works when you use the subform itself, but doesn't when
you use the subform embedded in the main form is probably because it is
embedded in the main form!

The path Access needs to follow to find the control is through the main
form
to the subform control and into the subform's controls. The Expression
(check Access HELP) might look something like:

Forms!YourMainForm!YourSubformControl.Form!YourSubform'sControl'sName

Regards

Jeff Boyce
Microsoft Office/Access MVP

If I open the subform by itself and use the following code, it works
fine.
Private Sub Form_AfterUpdate()
If (Eval("[Forms]![Other Corrective Action Plan subform1]![OCD] Is
Null"))
Then
MsgBox "A Value for 'Corrective Action Estimated Completion Date' is
Required.", vbOKOnly, "Required Value Missing"
End If
End Sub

The same code executed within the main form returns an error stating
Access
can't find the form Other Corrective Action Plan subform1.

:

You mention trying to do the validation from the parent form. It
seems
you've already decided "how".

Take a look at "expressions" in Access HELP for a way to refer to
controls
on a subform.

(and I still believe you'd have more luck if you'd set up the
validation
of
the subform data IN the subform itself).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

The main form contains general information related to each record
(the
one).
The subform contains information at a lower level (the many).

The main form must contain items such as routing number, title, and
source
to be considered a record. If any of these are Null, I want the
user
to
enter a value before they are allowed to save the data to the
database
and
close the form.

The subform must contain items related to the "steps" taken such as
action
plan and estimated completion date. If these are Null, I want the
user
to
enter a value before they are allowed to save the data to the
database
and
close the subform.

My thought was to have the entire form/subform validated, when the
user
clicks a button (Save & Close). Right now only the parent is
validated.


I tried adding validation and referencing the subform within the
parent,
but
I get a messaging saying it can't find the form (subform).

If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![Deficiency Source] = 'SCAMPI' and [Forms]![frmOther
Weakness
Corrective Actions (New Entry)]![ProcessAreas] is Null")) Then
MsgBox "A Value for 'Process Area' is Required.", vbOKOnly,
"Required
Value Missing"
intIsError = 1
End If
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![OCD]
is Null")) Then
MsgBox "A Value for the weakness ' Estimated Completion Date' is
Required.", vbOKOnly, "Required Value Missing"
intIsError = 1
End If
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![Status] is Null")) Then
MsgBox "A Value for 'Status' is Required.", vbOKOnly, "Required
Value
Missing"
intIsError = 1
End If
If (Eval("[Forms]![Other Corrective Action Plan
subform1]![CorrectiveActionPlanItem] is Not Null and [Forms]![Other
Corrective Action Plan subform1]![OCD] is Null")) Then
MsgBox "A Value for 'Corrective Action Plan Estimated Completion
Date.",
vbOKOnly, "Required Value Missing"
intIsError = 1
End If


:

As I mentioned, standard Access main form/subform construction
requires a
record in the main form before a subform record can be entered. I
still
don't have enough information to tell if your main form/subform is
standard.

One advantage to using the standard approach is that you can
validate
the
main form's record (i.e., the "one" side of a one-to-many
relationship)
before attempting to add a subform record (the "many" side). Of
course,
this assumes that your data is well-normalized and organized in a
pair
of
tables related one-to-many. Is this a fair assumption?

(still) More information, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

There is data in both the form and subform which must be
validated
before
a
record is considered complete enough to be entered into the
database.

I had this as two separate input forms, but the client didn't
like
that
approach.


:

If you are using a standard main form/subform construction,
there
is
NO
activity in the form that is your subform UNTIL it gets the
focus.
Why
would you be clicking a button in the main form when its data
entered
in
the
subform that needs validation?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

If the validation is taking place when a user clicks a button
on
the
parent
form, how would putting the code on the subform be executed?

:

If you are saying that you'd like Access to check on the
record
you
are
entering in a subform, have you tried putting your validation
code
in
that
subform (*actually, you'd put it in the form you are using as
a
subform*)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

message
I have created a form and wish to validate entries within
the
form
and a
subform.

I have placed the form validation code as part of the
"Close"
button.

Example:
If (Eval("[Forms]![frmOther Weakness Corrective Actions
(New
Entry)]![OCD]
is Null")) Then
MsgBox "A Value for 'Estimated Completion Date' is
Required.",
vbOKOnly,
"Required Value Missing"
intIsError = 1
End If


I now wish to validate entries on the subform. I tried
calling
the
"[subForm]", but get an error saying basically the method
does
not
exist.

How can I validate the subform entries?

Also, I wish to spell check the form and subform. I can
spell
check
the
form, but the subform is ignored.

Dim ctlSpell As Control
Dim frm As Form

Set frm = Screen.ActiveForm

DoCmd.SetWarnings False

For Each ctlSpell In frm.Controls

If TypeOf ctlSpell Is TextBox Then

If Len(ctlSpell) > 0 Then

With ctlSpell

'only spell check the text boxes with a
value
of
true
in
their tag property.
If .Tag = "True" Then

.SetFocus
.SelStart = 0
.SelLength = Len(ctlSpell)

DoCmd.RunCommand acCmdSpelling

End If

End With

End If

End If

Next


MsgBox "Spell check complete.", vbInformation +
vbOKOnly,
"Finished."
DoCmd.SetWarnings True
DoCmd.Close
End If

 

[dsc2bjn]

Myself...I tend to agree with you; however, I do work for a client. Thier
expectations, what Access is capable of, and my thoughts don't always match.

The client is use to the Outlook performs spell check. Some items are
automatically corrected while others happen when you close the email (if you
tell it to run spell check before sending).

They were looking for that type of validation...Validate everything before
you close Vs validate as you move from one field to another.

I am going to play with it some more. It would be great if I could click
the "close" button and validate everything at one time, so if you know how I
could do that...it would make my year.

Thanks

The notion of validation seems to be ... fractured!

If you don't want folks to enter bad data, you create validation routines
that help them do it the right way.

If you want folks to enter whatever they want, then run a routine that finds
all the mistakes, how did you plan to let the user know that the third field
on the second record in the subform is incorrect?

Actually, ideally, if there was a way to prevent the user from making any
mistake in the first place, that seems optimal.

JOPO (just one person's opinions) ... you did ask for my thoughts on this...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks!

That helps, but still have some issues.

If the validation check is on the subform, I need an event or something to
trigger the check.

I placed the code in the AfterUpdate() event, but that validates each
record
as they are entered. Normal this would be ok. The problem is that the
spell
check runs for each child record as it is entered. And the spell check
runs
again on the parent form for the other data at the time the form is
closed.

It can be a little a little repetative for the user.

I would really like the spell check to only run once for the entire form
(form and subform).

Any ideas?

The reason why it works when you use the subform itself, but doesn't when
you use the subform embedded in the main form is probably because it is
embedded in the main form!

The path Access needs to follow to find the control is through the main
form
to the subform control and into the subform's controls. The Expression
(check Access HELP) might look something like:

Forms!YourMainForm!YourSubformControl.Form!YourSubform'sControl'sName

Regards

Jeff Boyce
Microsoft Office/Access MVP


If I open the subform by itself and use the following code, it works
fine.
Private Sub Form_AfterUpdate()
If (Eval("[Forms]![Other Corrective Action Plan subform1]![OCD] Is
Null"))
Then
MsgBox "A Value for 'Corrective Action Estimated Completion Date' is
Required.", vbOKOnly, "Required Value Missing"
End If
End Sub

The same code executed within the main form returns an error stating
Access
can't find the form Other Corrective Action Plan subform1.

:

You mention trying to do the validation from the parent form. It
seems
you've already decided "how".

Take a look at "expressions" in Access HELP for a way to refer to
controls
on a subform.

(and I still believe you'd have more luck if you'd set up the
validation
of
the subform data IN the subform itself).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

The main form contains general information related to each record
(the
one).
The subform contains information at a lower level (the many).

The main form must contain items such as routing number, title, and
source
to be considered a record. If any of these are Null, I want the
user
to
enter a value before they are allowed to save the data to the
database
and
close the form.

The subform must contain items related to the "steps" taken such as
action
plan and estimated completion date. If these are Null, I want the
user
to
enter a value before they are allowed to save the data to the
database
and
close the subform.

My thought was to have the entire form/subform validated, when the
user
clicks a button (Save & Close). Right now only the parent is
validated.


I tried adding validation and referencing the subform within the
parent,
but
I get a messaging saying it can't find the form (subform).

If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![Deficiency Source] = 'SCAMPI' and [Forms]![frmOther
Weakness
Corrective Actions (New Entry)]![ProcessAreas] is Null")) Then
MsgBox "A Value for 'Process Area' is Required.", vbOKOnly,
"Required
Value Missing"
intIsError = 1
End If
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![OCD]
is Null")) Then
MsgBox "A Value for the weakness ' Estimated Completion Date' is
Required.", vbOKOnly, "Required Value Missing"
intIsError = 1
End If
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![Status] is Null")) Then
MsgBox "A Value for 'Status' is Required.", vbOKOnly, "Required
Value
Missing"
intIsError = 1
End If
If (Eval("[Forms]![Other Corrective Action Plan
subform1]![CorrectiveActionPlanItem] is Not Null and [Forms]![Other
Corrective Action Plan subform1]![OCD] is Null")) Then
MsgBox "A Value for 'Corrective Action Plan Estimated Completion
Date.",
vbOKOnly, "Required Value Missing"
intIsError = 1
End If


:

As I mentioned, standard Access main form/subform construction
requires a
record in the main form before a subform record can be entered. I
still
don't have enough information to tell if your main form/subform is
standard.

One advantage to using the standard approach is that you can
validate
the
main form's record (i.e., the "one" side of a one-to-many
relationship)
before attempting to add a subform record (the "many" side). Of
course,
this assumes that your data is well-normalized and organized in a
pair
of
tables related one-to-many. Is this a fair assumption?

(still) More information, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

There is data in both the form and subform which must be
validated
before
a
record is considered complete enough to be entered into the
database.

I had this as two separate input forms, but the client didn't
like
that
approach.


:

If you are using a standard main form/subform construction,
there
is
NO
activity in the form that is your subform UNTIL it gets the
focus.
Why
would you be clicking a button in the main form when its data
entered
in
the
subform that needs validation?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

If the validation is taking place when a user clicks a button
on
the
parent
form, how would putting the code on the subform be executed?

:

If you are saying that you'd like Access to check on the
record
you
are
entering in a subform, have you tried putting your validation
code
in
that
subform (*actually, you'd put it in the form you are using as
a
subform*)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

message
I have created a form and wish to validate entries within
the
form
and a
subform.

I have placed the form validation code as part of the
"Close"
button.

Example:
If (Eval("[Forms]![frmOther Weakness Corrective Actions
(New
Entry)]![OCD]
is Null")) Then
MsgBox "A Value for 'Estimated Completion Date' is
Required.",
vbOKOnly,
"Required Value Missing"
intIsError = 1
End If


I now wish to validate entries on the subform. I tried
calling
the
"[subForm]", but get an error saying basically the method
does
not
exist.

 

[Jeff Boyce]

Hopefully one of the other newsgroup readers has done something like this
....

My validation routines inspect the field and/or record before "leaving", so
the "bulk" validation it sounds like you are trying to do is outside my
experience...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Myself...I tend to agree with you; however, I do work for a client. Thier
expectations, what Access is capable of, and my thoughts don't always
match.

The client is use to the Outlook performs spell check. Some items are
automatically corrected while others happen when you close the email (if
you
tell it to run spell check before sending).

They were looking for that type of validation...Validate everything before
you close Vs validate as you move from one field to another.

I am going to play with it some more. It would be great if I could click
the "close" button and validate everything at one time, so if you know how
I
could do that...it would make my year.

Thanks

The notion of validation seems to be ... fractured!

If you don't want folks to enter bad data, you create validation routines
that help them do it the right way.

If you want folks to enter whatever they want, then run a routine that
finds
all the mistakes, how did you plan to let the user know that the third
field
on the second record in the subform is incorrect?

Actually, ideally, if there was a way to prevent the user from making any
mistake in the first place, that seems optimal.

JOPO (just one person's opinions) ... you did ask for my thoughts on
this...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks!

That helps, but still have some issues.

If the validation check is on the subform, I need an event or something
to
trigger the check.

I placed the code in the AfterUpdate() event, but that validates each
record
as they are entered. Normal this would be ok. The problem is that the
spell
check runs for each child record as it is entered. And the spell check
runs
again on the parent form for the other data at the time the form is
closed.

It can be a little a little repetative for the user.

I would really like the spell check to only run once for the entire
form
(form and subform).

Any ideas?


:

The reason why it works when you use the subform itself, but doesn't
when
you use the subform embedded in the main form is probably because it
is
embedded in the main form!

The path Access needs to follow to find the control is through the
main
form
to the subform control and into the subform's controls. The
Expression
(check Access HELP) might look something like:


Forms!YourMainForm!YourSubformControl.Form!YourSubform'sControl'sName

Regards

Jeff Boyce
Microsoft Office/Access MVP


If I open the subform by itself and use the following code, it works
fine.
Private Sub Form_AfterUpdate()
If (Eval("[Forms]![Other Corrective Action Plan subform1]![OCD] Is
Null"))
Then
MsgBox "A Value for 'Corrective Action Estimated Completion Date'
is
Required.", vbOKOnly, "Required Value Missing"
End If
End Sub

The same code executed within the main form returns an error stating
Access
can't find the form Other Corrective Action Plan subform1.

:

You mention trying to do the validation from the parent form. It
seems
you've already decided "how".

Take a look at "expressions" in Access HELP for a way to refer to
controls
on a subform.

(and I still believe you'd have more luck if you'd set up the
validation
of
the subform data IN the subform itself).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

The main form contains general information related to each record
(the
one).
The subform contains information at a lower level (the many).

The main form must contain items such as routing number, title,
and
source
to be considered a record. If any of these are Null, I want the
user
to
enter a value before they are allowed to save the data to the
database
and
close the form.

The subform must contain items related to the "steps" taken such
as
action
plan and estimated completion date. If these are Null, I want
the
user
to
enter a value before they are allowed to save the data to the
database
and
close the subform.

My thought was to have the entire form/subform validated, when
the
user
clicks a button (Save & Close). Right now only the parent is
validated.


I tried adding validation and referencing the subform within the
parent,
but
I get a messaging saying it can't find the form (subform).

If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![Deficiency Source] = 'SCAMPI' and [Forms]![frmOther
Weakness
Corrective Actions (New Entry)]![ProcessAreas] is Null")) Then
MsgBox "A Value for 'Process Area' is Required.", vbOKOnly,
"Required
Value Missing"
intIsError = 1
End If
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![OCD]
is Null")) Then
MsgBox "A Value for the weakness ' Estimated Completion Date'
is
Required.", vbOKOnly, "Required Value Missing"
intIsError = 1
End If
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![Status] is Null")) Then
MsgBox "A Value for 'Status' is Required.", vbOKOnly,
"Required
Value
Missing"
intIsError = 1
End If
If (Eval("[Forms]![Other Corrective Action Plan
subform1]![CorrectiveActionPlanItem] is Not Null and
[Forms]![Other
Corrective Action Plan subform1]![OCD] is Null")) Then
MsgBox "A Value for 'Corrective Action Plan Estimated
Completion
Date.",
vbOKOnly, "Required Value Missing"
intIsError = 1
End If


:

As I mentioned, standard Access main form/subform construction
requires a
record in the main form before a subform record can be entered.
I
still
don't have enough information to tell if your main form/subform
is
standard.

One advantage to using the standard approach is that you can
validate
the
main form's record (i.e., the "one" side of a one-to-many
relationship)
before attempting to add a subform record (the "many" side). Of
course,
this assumes that your data is well-normalized and organized in
a
pair
of
tables related one-to-many. Is this a fair assumption?

(still) More information, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

There is data in both the form and subform which must be
validated
before
a
record is considered complete enough to be entered into the
database.

I had this as two separate input forms, but the client didn't
like
that
approach.


:

If you are using a standard main form/subform construction,
there
is
NO
activity in the form that is your subform UNTIL it gets the
focus.
Why
would you be clicking a button in the main form when its data
entered
in
the
subform that needs validation?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

message
If the validation is taking place when a user clicks a
button
on
the
parent
form, how would putting the code on the subform be
executed?

:

If you are saying that you'd like Access to check on the
record
you
are
entering in a subform, have you tried putting your
validation
code
in
that
subform (*actually, you'd put it in the form you are using
as
a
subform*)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

message
I have created a form and wish to validate entries within
the
form
and a
subform.

I have placed the form validation code as part of the
"Close"
button.

Example:
If (Eval("[Forms]![frmOther Weakness Corrective Actions
(New
Entry)]![OCD]
is Null")) Then
MsgBox "A Value for 'Estimated Completion Date' is
Required.",
vbOKOnly,
"Required Value Missing"
intIsError = 1
End If


I now wish to validate entries on the subform. I tried
calling
the
"[subForm]", but get an error saying basically the
method
does
not
exist.

 

[dsc2bjn]

Jeff,
First thanks for your patience and assistance.

The code errors out at this point stating, "there is a syntax error
(missing operator) in query expression '(Deficiency Source IN ("CCE"))'."

DoCmd.OpenReport "rptOther Weakness Correct Act Plan - Specific Office
(Preview)", acViewPreview, , _
"Deficiency Source IN (" & strChoice & ")"

The notion of validation seems to be ... fractured!

If you don't want folks to enter bad data, you create validation routines
that help them do it the right way.

If you want folks to enter whatever they want, then run a routine that finds
all the mistakes, how did you plan to let the user know that the third field
on the second record in the subform is incorrect?

Actually, ideally, if there was a way to prevent the user from making any
mistake in the first place, that seems optimal.

JOPO (just one person's opinions) ... you did ask for my thoughts on this...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks!

That helps, but still have some issues.

If the validation check is on the subform, I need an event or something to
trigger the check.

I placed the code in the AfterUpdate() event, but that validates each
record
as they are entered. Normal this would be ok. The problem is that the
spell
check runs for each child record as it is entered. And the spell check
runs
again on the parent form for the other data at the time the form is
closed.

It can be a little a little repetative for the user.

I would really like the spell check to only run once for the entire form
(form and subform).

Any ideas?

The reason why it works when you use the subform itself, but doesn't when
you use the subform embedded in the main form is probably because it is
embedded in the main form!

The path Access needs to follow to find the control is through the main
form
to the subform control and into the subform's controls. The Expression
(check Access HELP) might look something like:

Forms!YourMainForm!YourSubformControl.Form!YourSubform'sControl'sName

Regards

Jeff Boyce
Microsoft Office/Access MVP


If I open the subform by itself and use the following code, it works
fine.
Private Sub Form_AfterUpdate()
If (Eval("[Forms]![Other Corrective Action Plan subform1]![OCD] Is
Null"))
Then
MsgBox "A Value for 'Corrective Action Estimated Completion Date' is
Required.", vbOKOnly, "Required Value Missing"
End If
End Sub

The same code executed within the main form returns an error stating
Access
can't find the form Other Corrective Action Plan subform1.

:

You mention trying to do the validation from the parent form. It
seems
you've already decided "how".

Take a look at "expressions" in Access HELP for a way to refer to
controls
on a subform.

(and I still believe you'd have more luck if you'd set up the
validation
of
the subform data IN the subform itself).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

The main form contains general information related to each record
(the
one).
The subform contains information at a lower level (the many).

The main form must contain items such as routing number, title, and
source
to be considered a record. If any of these are Null, I want the
user
to
enter a value before they are allowed to save the data to the
database
and
close the form.

The subform must contain items related to the "steps" taken such as
action
plan and estimated completion date. If these are Null, I want the
user
to
enter a value before they are allowed to save the data to the
database
and
close the subform.

My thought was to have the entire form/subform validated, when the
user
clicks a button (Save & Close). Right now only the parent is
validated.


I tried adding validation and referencing the subform within the
parent,
but
I get a messaging saying it can't find the form (subform).

If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![Deficiency Source] = 'SCAMPI' and [Forms]![frmOther
Weakness
Corrective Actions (New Entry)]![ProcessAreas] is Null")) Then
MsgBox "A Value for 'Process Area' is Required.", vbOKOnly,
"Required
Value Missing"
intIsError = 1
End If
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![OCD]
is Null")) Then
MsgBox "A Value for the weakness ' Estimated Completion Date' is
Required.", vbOKOnly, "Required Value Missing"
intIsError = 1
End If
If (Eval("[Forms]![frmOther Weakness Corrective Actions (New
Entry)]![Status] is Null")) Then
MsgBox "A Value for 'Status' is Required.", vbOKOnly, "Required
Value
Missing"
intIsError = 1
End If
If (Eval("[Forms]![Other Corrective Action Plan
subform1]![CorrectiveActionPlanItem] is Not Null and [Forms]![Other
Corrective Action Plan subform1]![OCD] is Null")) Then
MsgBox "A Value for 'Corrective Action Plan Estimated Completion
Date.",
vbOKOnly, "Required Value Missing"
intIsError = 1
End If


:

As I mentioned, standard Access main form/subform construction
requires a
record in the main form before a subform record can be entered. I
still
don't have enough information to tell if your main form/subform is
standard.

One advantage to using the standard approach is that you can
validate
the
main form's record (i.e., the "one" side of a one-to-many
relationship)
before attempting to add a subform record (the "many" side). Of
course,
this assumes that your data is well-normalized and organized in a
pair
of
tables related one-to-many. Is this a fair assumption?

(still) More information, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

There is data in both the form and subform which must be
validated
before
a
record is considered complete enough to be entered into the
database.

I had this as two separate input forms, but the client didn't
like
that
approach.


:

If you are using a standard main form/subform construction,
there
is
NO
activity in the form that is your subform UNTIL it gets the
focus.
Why
would you be clicking a button in the main form when its data
entered
in
the
subform that needs validation?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

If the validation is taking place when a user clicks a button
on
the
parent
form, how would putting the code on the subform be executed?

:

If you are saying that you'd like Access to check on the
record
you
are
entering in a subform, have you tried putting your validation
code
in
that
subform (*actually, you'd put it in the form you are using as
a
subform*)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

message
I have created a form and wish to validate entries within
the
form
and a
subform.

I have placed the form validation code as part of the
"Close"
button.

Example:
If (Eval("[Forms]![frmOther Weakness Corrective Actions
(New
Entry)]![OCD]
is Null")) Then
MsgBox "A Value for 'Estimated Completion Date' is
Required.",
vbOKOnly,
"Required Value Missing"
intIsError = 1
End If


I now wish to validate entries on the subform. I tried
calling
the
"[subForm]", but get an error saying basically the method
does
not
exist.