continuous subform validation

[deb]

On Access 2003
I have a main form called f040ProjectMain(PK ProjectID)
I have some fields on the main form that are needed to be populated so I
have the below code..

If IsNull(Me!Scope) Or IsNull(Me!ProjStatusID) Or IsNull(Me!ProjCategory) Or
IsNull(Me!PMID) Then
MsgBox "You must complete all required(*) fields before selecting other
fields."
Cancel = True
Me!ProjectName.SetFocus
End If

I also have a CONTINUOUS subform called fMilestones. I need to make sure
the user enters a milestone for KeyMilestoneSubID 12 and 20. How can I add
this requirement to the aboce code to make sure user enters a milestone for
BOTH 12 and 20?

 

[Jeanette Cunningham]

Hi deb,
not in the main form's code, but in the Before Update event on the
continuous subform, put code something like this untested air code.

If Me.[KeyMilestoneSubID] = 12 or KeyMilestoneSubID = 20 Then
If IsNull(Me.NameOfMilestoneField) Then
Cancel = True
Msgbox "Milestone required"
End If
End If



Replace NameOfMilestoneField with the appropriate control/field name.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[S.Clark]

Either In the Before or After Update event of the subform, add the validation
code.
I prefer to do it in the AfterUpdate.

 

[Jeanette Cunningham]

Not sure how that would work if you use the after update event - there is no
cancel parameter with the after update event.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[deb]

I know I need the validation code but I am not sure how to write it to
include the fields in the continuous subform.
How do I write the additional validation code? and
Should it go in Before or after update?

 

[Jeanette Cunningham]

See if this helps.

not in the main form's code, but in the Before Update event on the
continuous subform, put code something like this untested air code.

If Me.[KeyMilestoneSubID] = 12 or KeyMilestoneSubID = 20 Then
If IsNull(Me.NameOfMilestoneField) Then
Cancel = True
Msgbox "Milestone required"
End If
End If


Replace NameOfMilestoneField with the appropriate control/field name.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[deb]

When I put the validation in the subform before or after update, there is
nothing to make the form dirty so the user can exit without touching the
subform. How can I make the form dirty so it will trigger the validation?

I tried putting it in the main form after update but it put me in a loop
that would not let me click on the subform without the msg popping up so I
could not enter data into the subform.

Wow this should not be so difficult. It is making me crazy.
--
deb

Hi deb,
not in the main form's code, but in the Before Update event on the
continuous subform, put code something like this untested air code.

If Me.[KeyMilestoneSubID] = 12 or KeyMilestoneSubID = 20 Then
If IsNull(Me.NameOfMilestoneField) Then
Cancel = True
Msgbox "Milestone required"
End If
End If



Replace NameOfMilestoneField with the appropriate control/field name.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

On Access 2003
I have a main form called f040ProjectMain(PK ProjectID)
I have some fields on the main form that are needed to be populated so I
have the below code..

If IsNull(Me!Scope) Or IsNull(Me!ProjStatusID) Or IsNull(Me!ProjCategory)
Or
IsNull(Me!PMID) Then
MsgBox "You must complete all required(*) fields before selecting other
fields."
Cancel = True
Me!ProjectName.SetFocus
End If

I also have a CONTINUOUS subform called fMilestones. I need to make sure
the user enters a milestone for KeyMilestoneSubID 12 and 20. How can I
add
this requirement to the aboce code to make sure user enters a milestone
for
BOTH 12 and 20?

.

 

[Jeanette Cunningham]

Not sure why you are using a subform where no data entry is required. Would
you explain a bit more about what you are trying to do.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

When I put the validation in the subform before or after update, there is
nothing to make the form dirty so the user can exit without touching the
subform. How can I make the form dirty so it will trigger the validation?

I tried putting it in the main form after update but it put me in a loop
that would not let me click on the subform without the msg popping up so I
could not enter data into the subform.

Wow this should not be so difficult. It is making me crazy.
--
deb

Hi deb,
not in the main form's code, but in the Before Update event on the
continuous subform, put code something like this untested air code.

If Me.[KeyMilestoneSubID] = 12 or KeyMilestoneSubID = 20 Then
If IsNull(Me.NameOfMilestoneField) Then
Cancel = True
Msgbox "Milestone required"
End If
End If



Replace NameOfMilestoneField with the appropriate control/field name.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

On Access 2003
I have a main form called f040ProjectMain(PK ProjectID)
I have some fields on the main form that are needed to be populated so
I
have the below code..

If IsNull(Me!Scope) Or IsNull(Me!ProjStatusID) Or
IsNull(Me!ProjCategory)
Or
IsNull(Me!PMID) Then
MsgBox "You must complete all required(*) fields before selecting
other
fields."
Cancel = True
Me!ProjectName.SetFocus
End If

I also have a CONTINUOUS subform called fMilestones. I need to make
sure
the user enters a milestone for KeyMilestoneSubID 12 and 20. How can I
add
this requirement to the aboce code to make sure user enters a milestone
for
BOTH 12 and 20?

.

 

[dave]

Deb.. did you get a solution for this? I'm going nuts with the same problem. I saw answers to your post but they didn't help me.

Thx, Dave



deb wrote:

continuous subform validation
10-Nov-09

On Access 2003
I have a main form called f040ProjectMain(PK ProjectID)
I have some fields on the main form that are needed to be populated so I
have the below code..

If IsNull(Me!Scope) Or IsNull(Me!ProjStatusID) Or IsNull(Me!ProjCategory) Or
IsNull(Me!PMID) Then
MsgBox "You must complete all required(*) fields before selecting other
fields."
Cancel = True
Me!ProjectName.SetFocus
End If

I also have a CONTINUOUS subform called fMilestones. I need to make sure
the user enters a milestone for KeyMilestoneSubID 12 and 20. How can I add
this requirement to the aboce code to make sure user enters a milestone for
BOTH 12 and 20?
--
deb

Previous Posts In This Thread:

continuous subform validation
On Access 2003
I have a main form called f040ProjectMain(PK ProjectID)
I have some fields on the main form that are needed to be populated so I
have the below code..

If IsNull(Me!Scope) Or IsNull(Me!ProjStatusID) Or IsNull(Me!ProjCategory) Or
IsNull(Me!PMID) Then
MsgBox "You must complete all required(*) fields before selecting other
fields."
Cancel = True
Me!ProjectName.SetFocus
End If

I also have a CONTINUOUS subform called fMilestones. I need to make sure
the user enters a milestone for KeyMilestoneSubID 12 and 20. How can I add
this requirement to the aboce code to make sure user enters a milestone for
BOTH 12 and 20?
--
deb

Hi deb,not in the main form's code, but in the Before Update event on
Hi deb,
not in the main form's code, but in the Before Update event on the
continuous subform, put code something like this untested air code.

If Me.[KeyMilestoneSubID] = 12 or KeyMilestoneSubID = 20 Then
If IsNull(Me.NameOfMilestoneField) Then
Cancel = True
Msgbox "Milestone required"
End If
End If



Replace NameOfMilestoneField with the appropriate control/field name.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Either In the Before or After Update event of the subform, add the
Either In the Before or After Update event of the subform, add the validation
code.
I prefer to do it in the AfterUpdate.

:

Not sure how that would work if you use the after update event - there is
Not sure how that would work if you use the after update event - there is no
cancel parameter with the after update event.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I know I need the validation code but I am not sure how to write it toinclude
I know I need the validation code but I am not sure how to write it to
include the fields in the continuous subform.
How do I write the additional validation code? and
Should it go in Before or after update?
--
deb


:

See if this helps.
See if this helps.

not in the main form's code, but in the Before Update event on the
continuous subform, put code something like this untested air code.

If Me.[KeyMilestoneSubID] = 12 or KeyMilestoneSubID = 20 Then
If IsNull(Me.NameOfMilestoneField) Then
Cancel = True
Msgbox "Milestone required"
End If
End If


Replace NameOfMilestoneField with the appropriate control/field name.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

When I put the validation in the subform before or after update, there
When I put the validation in the subform before or after update, there is
nothing to make the form dirty so the user can exit without touching the
subform. How can I make the form dirty so it will trigger the validation?

I tried putting it in the main form after update but it put me in a loop
that would not let me click on the subform without the msg popping up so I
could not enter data into the subform.

Wow this should not be so difficult. It is making me crazy.
--
deb


:

Not sure why you are using a subform where no data entry is required.
Not sure why you are using a subform where no data entry is required. Would
you explain a bit more about what you are trying to do.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

EggHeadCafe - Software Developer Portal of Choice
Kill Running Process in ASP.NET [C# - Beta 2]
http://www.eggheadcafe.com/tutorial...7-4c140e45d48c/kill-running-process-in-a.aspx

 

[John W. Vinson]

Deb.. did you get a solution for this? I'm going nuts with the same problem. I saw answers to your post but they didn't help me.

Thx, Dave



deb wrote:

continuous subform validation
10-Nov-09

On Access 2003
I have a main form called f040ProjectMain(PK ProjectID)
I have some fields on the main form that are needed to be populated so I
have the below code..

If IsNull(Me!Scope) Or IsNull(Me!ProjStatusID) Or IsNull(Me!ProjCategory) Or
IsNull(Me!PMID) Then
MsgBox "You must complete all required(*) fields before selecting other
fields."
Cancel = True
Me!ProjectName.SetFocus
End If

I also have a CONTINUOUS subform called fMilestones. I need to make sure
the user enters a milestone for KeyMilestoneSubID 12 and 20. How can I add
this requirement to the aboce code to make sure user enters a milestone for
BOTH 12 and 20?

This is a "chicken or egg" problem.

If you have a record in the mainform, there is of necessity a time when there
is a new record on the mainform, with NOTHING entered in the subform - neither
12 nor 20. At some later moment, the user will have entered a record with 12,
but not yet entered a 20. Only then will they have an opportunity to enter
both child records.

Access saves the mainform record the moment you set focus to the subform, so
you *cannot* require that both subform records be entered before you save the
mainform record. It's too soon!

About all you would be able to do is to use two tables: a "scratchpad" table
as the recordsource for a mainform and another for the related form. You would
need a command button which runs two append queries which would check to see
if the required records are present, and only then append the record to the
"real" Project and Milestones tables. Otherwise the user would get a feedback
message that they have more work to do.