Eval a SubForm?

A

Art M

I have code to Eval fieds in a Form. This works great.
I tried here to add an eval for a Subform... and i get an error message
Access can't find it.
Is it becasue of the bloody space in the Form name? or is there something
different I have to do to eval a subform?

Here is the code: (The first two evals work great)
Private Sub Close_Click()
On Error GoTo Err_Close_Click

If
(Eval("[Forms]![Data_Entry_Form_Update_Incidents_All]![Regional_Approval_Date] Is Null")) Then
MsgBox "The General Manager must approve before this Incident can be
Closed", vbOKOnly, "More Data Needed"

Else
End If
If (Eval("[Forms]![Data_Entry_Form_Update_Incidents_All]![System_Approval]
Is Null")) Then
MsgBox "The System Control General Manager must approve before this
Incident can be Closed", vbOKOnly, "More Data Needed"

Else
End If

If ((Eval("[Forms]![ActionItem1 Subform]![Due_Date_1] Is NotNull")) And
(Eval("[Forms]![ActionItem1 Subform]![Completed_Date] Is Null"))) Then
MsgBox "Action Items must be Complete before this Incident can be
CLOSED", vbOKOnly, "Action Items Open"

Else
End If
stDocName = "Status_Update_Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit



Exit_Close_Click:
Exit Sub

Err_Close_Click:
MsgBox Err.Description
Resume Exit_Close_Click

End Sub
 
A

Allen Browne

You have a space missing in the 3rd Eval().

But subforms are not open in their own right, i.e. they are not in the Forms
collection.

Try something like:
[Forms]![MyMain]![MySub].Form![MyControl]

Explanation:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

Actually, I don't see the need for Eval here.
You could just use the IsNull() function e.g.:
If
IsNull(Forms![Data_Entry_Form_Update_Incidents_All]![System_Approval]) Then
 
A

Art M

OK, i fixed up the first two, and they work great, Thank you.
I read your notes, and the article, and now I get a message box Object
Required.
Here is the new code, can you see anything amis?

Private Sub Close_Click()
On Error GoTo Err_Close_Click

If
(Eval("[Forms]![Data_Entry_Form_Update_Incidents_All]![Regional_Approval_Date] Is Null")) Then
MsgBox "The General Manager must approve before this Incident can be
Closed", vbOKOnly, "More Data Needed"

Else
End If
If (Eval("[Forms]![Data_Entry_Form_Update_Incidents_All]![System_Approval]
Is Null")) Then
MsgBox "The System Control General Manager must approve before this
Incident can be Closed", vbOKOnly, "More Data Needed"

Else
End If

If (((Forms![Data_Entry_Form_Update_Incidents_All]![ActionItem1
Subform].Form![Due_Date_1]) Is Not Null) And
((Forms![Data_Entry_Form_Update_Incidents_All]![ActionItem1
Subform].Form![Completed_Date]) Is Null)) Then
MsgBox "Action Items must be Complete before this Incident can be CLOSED",
vbOKOnly, "Action Items Open"

Else
End If
stDocName = "Status_Update_Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit



Exit_Close_Click:
Exit Sub

Err_Close_Click:
MsgBox Err.Description
Resume Exit_Close_Click

End Sub
Art


Allen Browne said:
You have a space missing in the 3rd Eval().

But subforms are not open in their own right, i.e. they are not in the Forms
collection.

Try something like:
[Forms]![MyMain]![MySub].Form![MyControl]

Explanation:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

Actually, I don't see the need for Eval here.
You could just use the IsNull() function e.g.:
If
IsNull(Forms![Data_Entry_Form_Update_Incidents_All]![System_Approval]) Then

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

Reply to group, rather than allenbrowne at mvps dot org.

Art M said:
I have code to Eval fieds in a Form. This works great.
I tried here to add an eval for a Subform... and i get an error message
Access can't find it.
Is it becasue of the bloody space in the Form name? or is there something
different I have to do to eval a subform?

Here is the code: (The first two evals work great)
Private Sub Close_Click()
On Error GoTo Err_Close_Click

If
(Eval("[Forms]![Data_Entry_Form_Update_Incidents_All]![Regional_Approval_Date]
Is Null")) Then
MsgBox "The General Manager must approve before this Incident can
be
Closed", vbOKOnly, "More Data Needed"

Else
End If
If (Eval("[Forms]![Data_Entry_Form_Update_Incidents_All]![System_Approval]
Is Null")) Then
MsgBox "The System Control General Manager must approve before this
Incident can be Closed", vbOKOnly, "More Data Needed"

Else
End If

If ((Eval("[Forms]![ActionItem1 Subform]![Due_Date_1] Is NotNull")) And
(Eval("[Forms]![ActionItem1 Subform]![Completed_Date] Is Null"))) Then
MsgBox "Action Items must be Complete before this Incident can be
CLOSED", vbOKOnly, "Action Items Open"

Else
End If
stDocName = "Status_Update_Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit



Exit_Close_Click:
Exit Sub

Err_Close_Click:
MsgBox Err.Description
Resume Exit_Close_Click

End Sub
Click to expand...
Click to expand...
 
A

Art M

Sorry, HERE is the new code...
On Error GoTo Err_Close_Click

If IsNull(Forms![Data_Entry_Form_Update_Incidents_All]![System_Approval])
Then
MsgBox "The General Manager must approve before this Incident can be
Closed", vbOKOnly, "More Data Needed"

Else
End If

If IsNull(Forms![Data_Entry_Form_Update_Incidents_All]![System_Approval]) Then
MsgBox "The System Control General Manager must approve before this Incident
can be Closed", vbOKOnly, "More Data Needed"

Else
End If

If (((Forms![Data_Entry_Form_Update_Incidents_All]![ActionItem1
Subform].Form![Due_Date_1]) Is Not Null) And
((Forms![Data_Entry_Form_Update_Incidents_All]![ActionItem1
Subform].Form![Completed_Date]) Is Null)) Then
MsgBox "Action Items must be Complete before this Incident can be CLOSED",
vbOKOnly, "Action Items Open"

Else
End If

Allen Browne said:
You have a space missing in the 3rd Eval().

But subforms are not open in their own right, i.e. they are not in the Forms
collection.

Try something like:
[Forms]![MyMain]![MySub].Form![MyControl]

Explanation:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

Actually, I don't see the need for Eval here.
You could just use the IsNull() function e.g.:
If
IsNull(Forms![Data_Entry_Form_Update_Incidents_All]![System_Approval]) Then

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

Reply to group, rather than allenbrowne at mvps dot org.

Art M said:
I have code to Eval fieds in a Form. This works great.
I tried here to add an eval for a Subform... and i get an error message
Access can't find it.
Is it becasue of the bloody space in the Form name? or is there something
different I have to do to eval a subform?

Here is the code: (The first two evals work great)
Private Sub Close_Click()
On Error GoTo Err_Close_Click

If
(Eval("[Forms]![Data_Entry_Form_Update_Incidents_All]![Regional_Approval_Date]
Is Null")) Then
MsgBox "The General Manager must approve before this Incident can
be
Closed", vbOKOnly, "More Data Needed"

Else
End If
If (Eval("[Forms]![Data_Entry_Form_Update_Incidents_All]![System_Approval]
Is Null")) Then
MsgBox "The System Control General Manager must approve before this
Incident can be Closed", vbOKOnly, "More Data Needed"

Else
End If

If ((Eval("[Forms]![ActionItem1 Subform]![Due_Date_1] Is NotNull")) And
(Eval("[Forms]![ActionItem1 Subform]![Completed_Date] Is Null"))) Then
MsgBox "Action Items must be Complete before this Incident can be
CLOSED", vbOKOnly, "Action Items Open"

Else
End If
stDocName = "Status_Update_Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit



Exit_Close_Click:
Exit Sub

Err_Close_Click:
MsgBox Err.Description
Resume Exit_Close_Click

End Sub
Click to expand...
Click to expand...
 
A

Art M

Any other ideas?
I need to check to see if one field ofa subform is not null, and the other
field of a subform is null, then I need a message. that is the jist of things.

Art M said:
Sorry, HERE is the new code...
On Error GoTo Err_Close_Click

If IsNull(Forms![Data_Entry_Form_Update_Incidents_All]![System_Approval])
Then
MsgBox "The General Manager must approve before this Incident can be
Closed", vbOKOnly, "More Data Needed"

Else
End If

If IsNull(Forms![Data_Entry_Form_Update_Incidents_All]![System_Approval]) Then
MsgBox "The System Control General Manager must approve before this Incident
can be Closed", vbOKOnly, "More Data Needed"

Else
End If

If (((Forms![Data_Entry_Form_Update_Incidents_All]![ActionItem1
Subform].Form![Due_Date_1]) Is Not Null) And
((Forms![Data_Entry_Form_Update_Incidents_All]![ActionItem1
Subform].Form![Completed_Date]) Is Null)) Then
MsgBox "Action Items must be Complete before this Incident can be CLOSED",
vbOKOnly, "Action Items Open"

Else
End If

Allen Browne said:
You have a space missing in the 3rd Eval().

But subforms are not open in their own right, i.e. they are not in the Forms
collection.

Try something like:
[Forms]![MyMain]![MySub].Form![MyControl]

Explanation:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

Actually, I don't see the need for Eval here.
You could just use the IsNull() function e.g.:
If
IsNull(Forms![Data_Entry_Form_Update_Incidents_All]![System_Approval]) Then

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

Reply to group, rather than allenbrowne at mvps dot org.

Art M said:
I have code to Eval fieds in a Form. This works great.
I tried here to add an eval for a Subform... and i get an error message
Access can't find it.
Is it becasue of the bloody space in the Form name? or is there something
different I have to do to eval a subform?

Here is the code: (The first two evals work great)
Private Sub Close_Click()
On Error GoTo Err_Close_Click

If
(Eval("[Forms]![Data_Entry_Form_Update_Incidents_All]![Regional_Approval_Date]
Is Null")) Then
MsgBox "The General Manager must approve before this Incident can
be
Closed", vbOKOnly, "More Data Needed"

Else
End If
If (Eval("[Forms]![Data_Entry_Form_Update_Incidents_All]![System_Approval]
Is Null")) Then
MsgBox "The System Control General Manager must approve before this
Incident can be Closed", vbOKOnly, "More Data Needed"

Else
End If

If ((Eval("[Forms]![ActionItem1 Subform]![Due_Date_1] Is NotNull")) And
(Eval("[Forms]![ActionItem1 Subform]![Completed_Date] Is Null"))) Then
MsgBox "Action Items must be Complete before this Incident can be
CLOSED", vbOKOnly, "Action Items Open"

Else
End If
stDocName = "Status_Update_Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit



Exit_Close_Click:
Exit Sub

Err_Close_Click:
MsgBox Err.Description
Resume Exit_Close_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Art, you cannot use Is Null and Is Not Null in VBA code.
Use IsNull() and Not IsNull() instead.
The Is operator in VBA relates to identifying objects; hence the error.

Break it down a bit:
Dim frm As Form
Set frm = Forms![Data_Entry_Form_Update_Incidents_All]![ActionItem1
Subform].Form
If IsNull(frm![Completed_Date]) And Not IsNull(frm![Due_Date_1]) Then

If that still fails, you may find that the Name of the subform control is
not the same as the name of the form loaded into it (i.e. its Name is
different from its SourceObject.)

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

Reply to group, rather than allenbrowne at mvps dot org.

Art M said:
Any other ideas?
I need to check to see if one field ofa subform is not null, and the other
field of a subform is null, then I need a message. that is the jist of
things.

Art M said:
Sorry, HERE is the new code...
On Error GoTo Err_Close_Click

If
IsNull(Forms![Data_Entry_Form_Update_Incidents_All]![System_Approval])
Then
MsgBox "The General Manager must approve before this Incident can be
Closed", vbOKOnly, "More Data Needed"

Else
End If

If IsNull(Forms![Data_Entry_Form_Update_Incidents_All]![System_Approval])
Then
MsgBox "The System Control General Manager must approve before this
Incident
can be Closed", vbOKOnly, "More Data Needed"

Else
End If

If (((Forms![Data_Entry_Form_Update_Incidents_All]![ActionItem1
Subform].Form![Due_Date_1]) Is Not Null) And
((Forms![Data_Entry_Form_Update_Incidents_All]![ActionItem1
Subform].Form![Completed_Date]) Is Null)) Then
MsgBox "Action Items must be Complete before this Incident can be
CLOSED",
vbOKOnly, "Action Items Open"

Else
End If

Allen Browne said:
You have a space missing in the 3rd Eval().

But subforms are not open in their own right, i.e. they are not in the
Forms
collection.

Try something like:
[Forms]![MyMain]![MySub].Form![MyControl]

Explanation:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

Actually, I don't see the need for Eval here.
You could just use the IsNull() function e.g.:
If
IsNull(Forms![Data_Entry_Form_Update_Incidents_All]![System_Approval])
Then

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

Reply to group, rather than allenbrowne at mvps dot org.

I have code to Eval fieds in a Form. This works great.
I tried here to add an eval for a Subform... and i get an error
message
Access can't find it.
Is it becasue of the bloody space in the Form name? or is there
something
different I have to do to eval a subform?

Here is the code: (The first two evals work great)
Private Sub Close_Click()
On Error GoTo Err_Close_Click

If
(Eval("[Forms]![Data_Entry_Form_Update_Incidents_All]![Regional_Approval_Date]
Is Null")) Then
MsgBox "The General Manager must approve before this Incident
can
be
Closed", vbOKOnly, "More Data Needed"

Else
End If
If
(Eval("[Forms]![Data_Entry_Form_Update_Incidents_All]![System_Approval]
Is Null")) Then
MsgBox "The System Control General Manager must approve before
this
Incident can be Closed", vbOKOnly, "More Data Needed"

Else
End If

If ((Eval("[Forms]![ActionItem1 Subform]![Due_Date_1] Is NotNull"))
And
(Eval("[Forms]![ActionItem1 Subform]![Completed_Date] Is Null")))
Then
MsgBox "Action Items must be Complete before this Incident can
be
CLOSED", vbOKOnly, "Action Items Open"

Else
End If
stDocName = "Status_Update_Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit



Exit_Close_Click:
Exit Sub

Err_Close_Click:
MsgBox Err.Description
Resume Exit_Close_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
A

Art M

THANK YOU !!!!!
this works perfectly.
FYI, here is the new code that works:

'this is for the CLOSE button to close the Incident

Private Sub Close_Click()
On Error GoTo Err_Close_Click

If IsNull(Forms![Data_Entry_Form_Update_Incidents]![Regional_Approval_Date])
Then
MsgBox "The General Manager must approve before this Incident can be
Closed", vbOKOnly, "More Data Needed"

Else
End If

If IsNull(Forms![Data_Entry_Form_Update_Incidents]![System_Approval]) Then
MsgBox "The System Control General Manager must approve before this Incident
can be Closed", vbOKOnly, "More Data Needed"

Else


Dim frm As Form
Set frm = Forms![Data_Entry_Form_Update_Incidents]![ActionItem1 Subform].Form
If IsNull(frm![Completed_Date_1]) And Not IsNull(frm![Due_Date_1]) Then
Else

Dim stDocName As String

stDocName = "Status_Update_Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit


stDocName = "Status_Update_Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit
End If
Exit_Close_Click:
Exit Sub

Err_Close_Click:
MsgBox Err.Description
Resume Exit_Close_Click
End If
End Sub


Allen Browne said:
Art, you cannot use Is Null and Is Not Null in VBA code.
Use IsNull() and Not IsNull() instead.
The Is operator in VBA relates to identifying objects; hence the error.

Break it down a bit:
Dim frm As Form
Set frm = Forms![Data_Entry_Form_Update_Incidents_All]![ActionItem1
Subform].Form
If IsNull(frm![Completed_Date]) And Not IsNull(frm![Due_Date_1]) Then

If that still fails, you may find that the Name of the subform control is
not the same as the name of the form loaded into it (i.e. its Name is
different from its SourceObject.)

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

Reply to group, rather than allenbrowne at mvps dot org.

Art M said:
Any other ideas?
I need to check to see if one field ofa subform is not null, and the other
field of a subform is null, then I need a message. that is the jist of
things.

Art M said:
Sorry, HERE is the new code...
On Error GoTo Err_Close_Click

If
IsNull(Forms![Data_Entry_Form_Update_Incidents_All]![System_Approval])
Then
MsgBox "The General Manager must approve before this Incident can be
Closed", vbOKOnly, "More Data Needed"

Else
End If

If IsNull(Forms![Data_Entry_Form_Update_Incidents_All]![System_Approval])
Then
MsgBox "The System Control General Manager must approve before this
Incident
can be Closed", vbOKOnly, "More Data Needed"

Else
End If

If (((Forms![Data_Entry_Form_Update_Incidents_All]![ActionItem1
Subform].Form![Due_Date_1]) Is Not Null) And
((Forms![Data_Entry_Form_Update_Incidents_All]![ActionItem1
Subform].Form![Completed_Date]) Is Null)) Then
MsgBox "Action Items must be Complete before this Incident can be
CLOSED",
vbOKOnly, "Action Items Open"

Else
End If

:

You have a space missing in the 3rd Eval().

But subforms are not open in their own right, i.e. they are not in the
Forms
collection.

Try something like:
[Forms]![MyMain]![MySub].Form![MyControl]

Explanation:
Referring to Controls on a Subform
at:
http://allenbrowne.com/casu-04.html

Actually, I don't see the need for Eval here.
You could just use the IsNull() function e.g.:
If
IsNull(Forms![Data_Entry_Form_Update_Incidents_All]![System_Approval])
Then

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

Reply to group, rather than allenbrowne at mvps dot org.

I have code to Eval fieds in a Form. This works great.
I tried here to add an eval for a Subform... and i get an error
message
Access can't find it.
Is it becasue of the bloody space in the Form name? or is there
something
different I have to do to eval a subform?

Here is the code: (The first two evals work great)
Private Sub Close_Click()
On Error GoTo Err_Close_Click

If
(Eval("[Forms]![Data_Entry_Form_Update_Incidents_All]![Regional_Approval_Date]
Is Null")) Then
MsgBox "The General Manager must approve before this Incident
can
be
Closed", vbOKOnly, "More Data Needed"

Else
End If
If
(Eval("[Forms]![Data_Entry_Form_Update_Incidents_All]![System_Approval]
Is Null")) Then
MsgBox "The System Control General Manager must approve before
this
Incident can be Closed", vbOKOnly, "More Data Needed"

Else
End If

If ((Eval("[Forms]![ActionItem1 Subform]![Due_Date_1] Is NotNull"))
And
(Eval("[Forms]![ActionItem1 Subform]![Completed_Date] Is Null")))
Then
MsgBox "Action Items must be Complete before this Incident can
be
CLOSED", vbOKOnly, "Action Items Open"

Else
End If
stDocName = "Status_Update_Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit



Exit_Close_Click:
Exit Sub

Err_Close_Click:
MsgBox Err.Description
Resume Exit_Close_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA works in orignal but fails in copy of database 1
Spell check form and subform 0
Do not allow form to close on validation error 6
Validation of multiple check boxes 5
Checking for null on close 3
Validation of subform 16
Open form based on combo box value w/error message for blank combo box 0
Spell check of form and subform 1

Top